Macro to access site with login

Question

I run a daily routine of accessing the Serasa site and make the CNPJ query. I need to develop a macro to access this Serasa site, log in and then query, and then play the information in Excel. Detail: I was able to develop the macro to access the site, but at the time of CNPJ query, Script error and do not copy the information. Can anyone tell me if it is possible for the macro to perform all these activities?

Sub FazerLoginSite()
    Dim IE As Object Set IE = CreateObject("InternetExplorer.application")
    With IE 
        .Visible = True
        .Navigate ("serasaexperian.com.br/")
        While .Busy Or .ReadyState <> 4:
            DoEvents:
        Wend
        .Document.getElementById("inputLogin").Focus
        .Document.getElementById("inputLogin").Value = "Meu Login"
        .Document.getElementById("senha").Focus
        .Document.getElementById("senha").Value = "Minha Senha"
        .Document.All("btnOk").Click
        While .Busy Or .ReadyState <> 4:
            DoEvents:
        Wend 
        Debug.Print .LocationURL
    End With
End Sub

Answer 1

As I’ve commented before, can do using libraries Microsoft HTML Object and Microsoft XML, V6.0. They need to be referenced within the VBA (in the code window, access the menu Ferramentas -> Referências and mark these libraries:

Then, you need to parse the HTML/Javascript code of the page with the form of interest to find out:

1. What are the fields and values needed to send the request
2. What is the request method (POST or GET, for example)
3. What is the address of action form (if empty, is the page address with the form)

So, build a code something like this:

Sub GenMegaSenaNumbers()
On Error Resume Next

    Dim oHttp As MSXML2.XMLHTTP60
    Dim oDoc As MSHTML.HTMLDocument
    Dim oText As HTMLParaElement
    Dim sURL As String
    Dim sParameters As String

    ' Defina aqui a URL da página de requisição do formulário
    sURL = "https://www.random.org/quick-pick/index.php"
    ' Defina aqui os parâmetros para a consulta
    ' (usando EXATAMENTE os mesmos nomes dos campos no formulário)
    sParameters = "tickets=1&lottery=6x60.0x0"

    ' Faz a consulta dos dados
    Cells(1, 1) = "Aguarde. Fazendo a consulta pelos números do jogo..."

    Set oHttp = New MSXML2.XMLHTTP60
    oHttp.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    oHttp.setRequestHeader "Content-type", "application/x-www-form-urlencoded"

    oHttp.Open "GET", sURL & "?" & sParameters, False
    oHttp.send

    ' Se o método de acesso for POST ao invés de GET, use assim:
    'oHttp.Open "POST", sURL, False
    'oHttp.send sParameters

    ' Checa se o carregamento foi efetuado corretamente
    If oHttp.Status <> 200 Then
        Cells(1, 1) = "Oops! Não foi possível obter a resposta! Erro: (" & Str(oHttp.Status) & " )" & oHttp.statusText
        Exit Sub
    End If

    ' Obtem a resposta
    Set oDoc = New MSHTML.HTMLDocument
    oDoc.body.innerHTML = oHttp.responseText

    ' E lê o dado do elemento desejado
    Set oText = oDoc.getElementsByClassName("data").Item(0)
    Cells(1, 1) = "Sugestão de jogo para a Mega-Sena: " & oText.innerText

End Sub

Sub Botão1_Click()

    GenMegaSenaNumbers

End Sub

This example has a macro (the sub Botão1_Click) attached to a button that executes the sub GenMegaSenaNumbers, who is in fact responsible for making the request and getting the answer. In this example I refer to random number generation for a Mega-Sena game, free service available through Random.org. The form on this page has several fields (including a hidden one that is dynamically mounted by Javascript), but I used the Google Chrome developer tool, on the flap Network, to identify which parameters he sent in the request (much easier! and I could see that not all fields were needed).

Note that there is difference in the way to pass the parameters in VBA if the method is POST or GET (in the case of example, it was GET). Also note the parameter False in the call for oHttp.Open (third parameter, which does not exist in the original SOEN response that I mentioned in the comments). It indicates that the call is not asynchronous, and so it is not necessary to tie the DoEvents to wait for the result (Excel itself manages this for you, making the call synchronous).

Here is the screenshot of the result:

IMPORTANT REMARK: Naturally this method doesn’t work with forms that require validation by CAPTCHA. After all, it is precisely this the intention of a (good) CAPTCHA: prevent automatisms run on the page. To understand (if you need to), read more about the Wikipedia and right here at SOPT(suggestion of reading 1 and reading suggestion 2). Independently, the ideal (and probably legal) solution is to use a Web Service whenever available. The consumption of Web Services in newer versions of Excel is fairly simple.