Macro to access website with login

Navigation
26

I run a daily routine to access the Serasa site and make the CNPJ query. I need to develop a macro to access this Serasa site, log in and then the query, and then throw the information into Excel. Detail: I have already developed the macro to access the site, but at the time of the CNPJ query, the Script error and does not let the information copied. Can anyone tell me if it is possible for the macro to do 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
    
asked by anonymous 26.12.2014 / 12:09

1 answer

8

As I've mentioned before, you can do this by using the Microsoft HTML Object and Microsoft XML, v6.0 libraries. They need to be referenced within VBA (in the code window, go to the Ferramentas -> Referências menu and check these libraries:

ThenyouneedtoreviewtheHTML/Javascriptcodeofthepagewiththeformofinteresttofindout:

  • Whatfieldsandvaluesarerequiredtosendtherequest
  • Whatistherequestmethod(POSTorGET,forexample)
  • Whatistheaddressoftheactionoftheform(ifitisempty,itistheaddressofthepagewiththeformitself)

    • So,putsomecodelikethis:

    SubGenMegaSenaNumbers()OnErrorResumeNextDimoHttpAsMSXML2.XMLHTTP60DimoDocAsMSHTML.HTMLDocumentDimoTextAsHTMLParaElementDimsURLAsStringDimsParametersAsString'DefinaaquiaURLdapáginaderequisiçãodoformuláriosURL="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 ) appended to a button that executes the sub GenMegaSenaNumbers , which in fact is responsible for making the request and obtaining the response. In this example I refer to the random number generation for a Mega-Sena game, free service available from Random.org . The form on this page has several fields (including a hidden one that is dynamically mounted by Javascript), but I've used the Google Chrome Developer Tool in the Network tab, to identify which parameters it sent in the request (much easier! and you could see that not all fields were needed).

    Note that there is a difference in how to pass parameters in VBA if the method is POST or GET (in the example case, it was GET). Note also the False parameter in the 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, so it is not necessary to loop with DoEvents to wait for the result (Excel itself manages this for you, making the call synchronous).

    Here is the result screenshot:

      

    IMPORTANTNOTE:Naturallythismethoddoesnotwork  withformsthatrequireCAPTCHAvalidation.Afterall,itis  thisistheintentionofa(good)CAPTCHA:topreventautomatisms  runonthepage.Tounderstand(ifneedbe),Isuggestreading  moreaboutitat Wikipedia and right here at SOPT ( suggestion   read 1 and reading suggestion 2 ). Regardless, the   (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 a>.

        
    24.04.2015 / 00:45
    OneToMany entity mapping with Fluent NHibernate Open PDF on iOS mobile using Xamarin - C #