Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Log in to a website using MSXML 6.0 using uid & password

0

The following tip uses Internet Explorer to log in with uid & pwd:

Activate or Navigate to a Worksheet using Macros VBA in Excel 

However, MSXML runs about 25 times faster than the IE "navigate", therefore I would very much like to change over to it.

I need a reference to specific documentation OR, possibly better, a good example.

Many Thanks.

Answer
Discuss

Answers

0
Selected Answer

Using the IE.Navigate method seems to be, by far, the easiest way to connect to a website and do something. Yes, it is not fast, but, as far as I'm aware, it is the simplest and most intuitive way for someone to do it.

The method to which you are referring requires quite a few different steps to get anywhere.

MSXML Documentation - If you're not a programmer, you might not like to read this, but here it is.

Here is a good way to illustrate the differences and give you an example:

(courtesy of this post on stackoverflow)

IE.Navigate

Sub testIE()
Dim texto As String

Set ie = CreateObject("InternetExplorer.Application")
my_url = "https://www.nfp.fazenda.sp.gov.br/login.aspx"
With ie
    .Visible = False
    .Navigate my_url

Do Until Not ie.Busy And ie.readyState = 4
    DoEvents
Loop

End With

ie.Document.getelementbyid("userName").Value = "MYUSERNAME"
ie.Document.getelementbyid("Password").Value = "MYPASSWORD"
ie.Document.getelementbyid("Login").Click

Do Until Not ie.Busy And ie.readyState = 4
    DoEvents
Loop

ie.Document.getelementbyid("btnConsultarNFSemestre").Click

Do Until Not ie.Busy And ie.readyState = 4
    DoEvents
Loop

texto = ie.Document.getelementbyid("dadosDoUsuario").innerText
MsgBox texto

ie.Quit

End Sub
 

MSXML

Sub testXMLHTTP()
Dim xml As Object
Dim html As HTMLDocument
Dim dados As Object
Dim text As Object
Dim html2 As HTMLDocument
Dim xml2 As Object

Set xml = CreateObject("Msxml2.ServerXMLHTTP.6.0")
Set html = CreateObject("htmlFile")


With xml
  .Open "GET", "https://www.nfp.fazenda.sp.gov.br/Login.aspx", False
  .send
End With

strCookie = xml.getResponseHeader("Set-Cookie")

html.body.innerhtml = xml.responseText

Set objvstate = html.GetElementById("__VIEWSTATE")
Set objvstategen = html.GetElementById("__VIEWSTATEGENERATOR")
Set objeventval = html.GetElementById("__EVENTVALIDATION")

vstate = objvstate.Value
vstategen = objvstategen.Value
eventval = objeventval.Value

'URL Encode ViewState
    Dim ScriptEngine As ScriptControl
    Set ScriptEngine = New ScriptControl
    ScriptEngine.Language = "JScript"
    ScriptEngine.AddCode "function encode(vstate) {return encodeURIComponent(vstate);}"
    Dim encoded As String
    encoded = ScriptEngine.Run("encode", vstate)
    vstate = encoded
'URL Encode Event Validation
    ScriptEngine.AddCode "function encode(eventval) {return encodeURIComponent(eventval);}"
    encoded = ScriptEngine.Run("encode", eventval)
    eventval = encoded
'URL Encode ViewState Generator
    ScriptEngine.AddCode "function encode(vstategen) {return encodeURIComponent(vstategen);}"
    encoded = ScriptEngine.Run("encode", vstategen)
    vstategen = encoded

Postdata = "__EVENTTARGET=" & "&__EVENTARGUMENT=" & "&__VIEWSTATE=" & vstate & "&__VIEWSTATEGENERATOR=" & vstategen & "&__EVENTVALIDATION=" & eventval & "&ctl00$ddlTipoUsuario=#rdBtnNaoContribuinte" & "&ctl00$UserNameAcessivel=Digite+o+Usuário" & "&ctl00$PasswordAcessivel=x" & "&ctl00$ConteudoPagina$Login1$rblTipo=rdBtnNaoContribuinte" & "&ctl00$ConteudoPagina$Login1$UserName=MYUSERNAME" & "&ctl00$ConteudoPagina$Login1$Password=MYPASSWORD" & "&ctl00$ConteudoPagina$Login1$Login=Acessar" & "&ctl00$ConteudoPagina$Login1$txtCpfCnpj=Digite+o+Usuário"

Set xml2 = CreateObject("Msxml2.ServerXMLHTTP.6.0")
Set html2 = CreateObject("htmlFile")

With xml2
  .Open "POST", "https://www.nfp.fazenda.sp.gov.br/Login.aspx", False
  .setRequestHeader "Cookie", strCookie
  .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
  .setRequestHeader "Content-Lenght", Len(Postdata)
  .send (Postdata)
End With

html2.body.innerhtml = xml2.responseText

Set objResult = html2.GetElementById("dadosDoUsuario")
GetElementById = objResult.innertext

MsgBox GetElementById


End Sub
Discuss

Discussion

Appparently, there are a couple of Functions involved which are not shown. Can you give me any clues as to what they are. Thanks so much for the help so far.
wornhall (rep: 4) May 2, '18 at 11:37 pm
Old post haha. Can you be more descriptive about what you mean with the functions? What is not working as expected? Also, this might be a good candidate for asking a new question in a new post in the forum so you can post the code that is not working like you expect and give a detailed description of the issue.
don (rep: 1989) May 22, '18 at 8:27 am
"encoded" seems to be an undefined function.

But going back to my original question, "activate" is not defined or used in the absolutely great discussion of IE versus MSXML procedures. Which, by the way, give at least a ten to one time advantage to MSXML. I have several applications which run 20 times faster under MSXML.

I remain in need of a discussion of "open" and "close" regarding workbook(s), and "worksheet(s)".
wornhall (rep: 4) Apr 24, '19 at 5:30 pm
Are you trying to connect to a website or move to another worksheet? It seems like you are talking about two completely different things, or I'm just confused about your question lol. And if MSXML is what you want, then go ahead and use that. I don't cover that much in the tutorials because that is far beyond what regular VBA/Macro users need and serves mostly to confuse the non-programming oriented user.
don (rep: 1989) Apr 26, '19 at 4:07 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login