Web Scraping using VBA and MSXML2.XMLHTTP library

Question

I'm trying to scrap data from a website using MSXML2.XMLHTTP object on VBA environment (Excel) and I cannot figure out how to solve this problem! The website is the following:

http://www.detran.ms.gov.br/consulta-de-debitos/

You guys can use the following test data to fill the form:

Placa: oon5868
Renavam: 1021783231

I want to retrieve data like "chassi", with the data above that would be " 9BD374121F5068077".

I do not have problems parsing the html document, the difficult is actually getting the information as response! Code below:

Sub SearchVehicle()

   Dim strPlaca As String
   Dim strRenavam As String

   strPlaca = "oon5868"
   strRenavam = "01021783231"

   Dim oXmlPage As MSXML2.XMLHTTP60
   Dim strUrl As String
   Dim strPostData As String

   Set oXmlPage = New MSXML2.XMLHTTP60
   strUrl = "http://www2.detran.ms.gov.br/detranet/nsite/veiculo/veiculos/retornooooveiculos.asp"
   strPostData = "placa=" & strPlaca & "&renavam=" & strRenavam

   oXmlPage.Open "POST", strUrl, False
   oXmlPage.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
   oXmlPage.send strPostData

   Debug.Print oXmlPage.responseText

End Sub

The strURL used in the POST method ".../retornooooveiculos.asp" is the one google developer tools and fiddler showed me that was the correct address the website was posting the payload.

When manually accessed, the website retrieve the correct information, but running my code I always get the following response on the .responseText:

<html>Acesse: <b><a href='http://www.detran.ms.gov.br target='_parent'>www.detran.ms.gov.br</a></b></html>

HELP PLEASE, I'm getting crazy trying to solve this puzzle! Why do I get redirected like this?

I need the "CHASSI" information and can't find the correct http Request to do this!

I have the same response information which your code receives when accessing website manually. It basically tells me to visit "www.detran.ms.gov.br", but when I click on that link, it takes some time to inform me "This site can’t be reached www.detran.ms.gov.br took too long to respond." — Ryszard Jędraszyk
– Ryszard Jędraszyk, Commented May 13, 2018 at 23:24

SIM · Accepted Answer · 2018-05-14 08:08:31Z

1

Try the below approach. It should fetch you the content you are after. The thing is you need to supply the Cookie copied from your Request Headers fields in order for your script to work which you can find using devtools.

Sub SearchVehicle()
    Const URL As String = "http://www2.detran.ms.gov.br/detranet/nsite/veiculo/veiculos/retornooooveiculos.asp"
    Dim HTTP As New ServerXMLHTTP60, HTML As New HTMLDocument
    Dim elem As Object, splaca$, srenavam$, qsp$

   splaca = "oon5868"
   srenavam = "01021783231"

   qsp = "placa=" & splaca & "&renavam=" & srenavam

   With HTTP
    .Open "POST", URL, False
    .setRequestHeader "User-Agent", "Mozilla/5.0"
    .setRequestHeader "Cookie", "ISAWPLB{07D08995-E67C-4F44-91A1-F6A16337ECD6}={286E0BB1-C5F9-4439-A2CE-A7BE8C3955E0}; ASPSESSIONIDSCSDSCTB=AGDPOBEAAPJLLMKKIGPLBGMJ; 69137927=967930978"
    .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    .send qsp
    HTML.body.innerHTML = .responseText
   End With

    For Each elem In HTML.getElementsByTagName("b")
        If InStr(elem.innerText, "Chassi:") > 0 Then MsgBox elem.ParentNode.NextSibling.innerText: Exit For
    Next elem
End Sub

Once again: fill in the Cookie field by collecting it using your devtools (from Request Headers section), if for some reason my provided Cookie doesn't work for you. Thanks.

Output I'm getting:

9BD374121F5068077

answered May 14, 2018 at 8:08

SIM

22.5k6 gold badges45 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adriano Ianase Over a year ago

Thanks for the response! I was trying to get the cookies programmatically, but I couldn't figure out why it wasn't working out. Your solution already helps me a lot!

SIM Over a year ago

Hey there, the thing is you could have got cookies programatically if there are options like strCookie = .getRequestHeader("Cookie") but unfortunately I could not find any options like so. However, the one you may use is strCookie = .getResponseHeader("Set-Cookie") but it will not serve your purpose. That is why i did that manually. Thanks.

Collectives™ on Stack Overflow

Web Scraping using VBA and MSXML2.XMLHTTP library

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related