CODEWITHSUNDEEP
vbaweb-scrapingxmlhttprequestmsxml
Adriano Ianase
Adriano Ianase

Reputation: 75

Web Scraping using VBA and MSXML2.XMLHTTP library

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:

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!

Upvotes: 0

Views: 3153

Answers (1)

SIM
SIM

Reputation: 22440

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

Upvotes: 1

Related Questions