Login into website using MSXML2.XMLHTTP instead of InternetExplorer.Application with VBA

Question

first time posting,

I'm trying to get the ID "dadosDoUsuario" from a website's page I have to be logged in. I got it working using "InternetExplorer.Application" object, but can't get the ID value when using "MSXML2.XMLHTTP" object. It seems it won't go past the login page, since I'm able to get other IDs from this page (example: "tituloPagina"). Could someone give a hint on how I get the data from the page after logged in? Thanks!

InternetExplorer.Application code (this one works):

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

MSXML2.XMLHTTP code (this one doesn't work):

Sub testXMLHTTP()
Dim xml As Object
Dim html As Object
Dim dados As Object
Dim text As Object

Set xml = CreateObject("MSXML2.XMLHTTP")

Set html = CreateObject("htmlFile")

With xml
  .Open "POST", "https://www.nfp.fazenda.sp.gov.br/Login.aspx", False
  .setRequestHeader "Content-Type", "text/xml"
  .send "userName=MYUSERNAME&password=MYPASSWORD"
  .Open "GET", "https://www.nfp.fazenda.sp.gov.br/Inicio.aspx", False
  .setRequestHeader "Content-Type", "text/xml"
  .send
End With

html.body.innerhtml = xml.responseText

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

MsgBox GetElementById

End Sub

EDIT: I followed the steps suggested by @Florent B., and added a scripcontrol to get the encoded values for __VIEWSTATE, __VIEWSTATEGENERATOR and __EVENTVALIDATION. Got it working!

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

score 3 · Accepted Answer · answered Mar 10 '16 at 05:14

3

It's possible but not that easy.

First you need to use CreateObject("Msxml2.ServerXMLHTTP.6.0") and not CreateObject("MSXML2.XMLHTTP").

Then follow these steps:

Open and send a GET to https://www.nfp.fazenda.sp.gov.br/login.aspx
Parse and store the cookie from the response header "Set-Cookie"
Parse and store the __VIEWSTATE, __VIEWSTATEGENERATOR, __EVENTVALIDATION from the HTML response

Build the data for the next query with the values parsed previously and with your user-name/password :

__EVENTTARGET:""
__EVENTARGUMENT:""
__VIEWSTATE:"..."
__VIEWSTATEGENERATOR:"..."
__EVENTVALIDATION:"..."
ctl00$ddlTipoUsuario:"#rdBtnNaoContribuinte"
ctl00$UserNameAcessivel:"Digite+o+Usuário"
ctl00$PasswordAcessivel:"x"
ctl00$ConteudoPagina$Login1$rblTipo:"rdBtnNaoContribuinte"
ctl00$ConteudoPagina$Login1$UserName:"..."
ctl00$ConteudoPagina$Login1$Password:"..."
ctl00$ConteudoPagina$Login1$Login:"Acessar"
ctl00$ConteudoPagina$Login1$txtCpfCnpj:"Digite+o+Usuário"

Open a POST to https://www.nfp.fazenda.sp.gov.br/login.aspx
Set the header "Cookie" with the cookie parsed at step 2
Set the header Content-Type: "application/x-www-form-urlencoded"
Set the header Content-Length with the length of the data
Send the POST with the data from step 4

answered Mar 10 '16 at 05:14

Florent B.

41,537
7
86
101

3

That is a good action plan. I want to note that usage of `ServerXMLHTTP` is not a must in a common case. `XMLHTTP` has an advantage that you don't have to bother with cookies - it manages them natively: receives, stores and sends a necessary cookie for you, since it based on IE. Although it lacks an ability of redirection and ignoring cert errors, so for that cases `ServerXMLHTTP` is the only way. Also `ServerXMLHTTP` is useful when you need to send several simultaneously requests with different cookies. – omegastripes Mar 10 '16 at 10:10
Helo @Florent B., thanks for the answer, and sorry for taking long to get back, I decided to study a bit to understand better what you proposed. I've edited the original post with a new code, following the steps you suggested. I'm still not getting the response from the page after login. What might be wrong? – Vitor Prado Apr 09 '16 at 22:24
1

You should inspect and compare the packets to identify what is incorrect with your request (https://www.wireshark.org). – Florent B. Apr 11 '16 at 10:09
Got it working! Checked the Postdata lenght and it wasn't matching the actual response from html code. Found out I was getting the deconded values for viewstate, viewstategenerator and eventvalidation. Found this [post](http://www.mrexcel.com/forum/excel-questions/749860-msxmlhttp-post-paramaters.html), showing how to get the encoded values using scriptcontrol. Got it working afterall. I'm going to edit with the working code. Thanks a lot! – Vitor Prado Apr 11 '16 at 12:52

Login into website using MSXML2.XMLHTTP instead of InternetExplorer.Application with VBA

1 Answers1

Linked