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


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.




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)


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

End With

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

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


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

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


End Sub


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
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

Answer the Question

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