Click on the link with vba

Asked

Viewed 339 times

0

I need to click on the object that has the hyperlink to continue browsing the page.

Error occurs on line: ie.document.getElementsByTagName("td")(0).CLICK.

Sub busca_desc()

Range("B3:g3").ClearContents 'limpo o range

Set ie = CreateObject("internetexplorer.application") 'crio o objeto IE

ie.navigate "https://www.petronect.com.br/irj/go/km/docs/pccshrcontent/Site%20Content%20(Legacy)/Portal2018/pt/lista_licitacoes_publicadas_ft.html"  'Url do site
ie.Visible = True

Do While ie.busy And ie.readyState <> "READYSTATE_COMPLETE" ' aguardar a página carregar
    DoEvents
Loop

ie.document.getElementsByTagName("input")(3).Value = 7003383741# ' insiro o valor no campo para pesquisa
ie.document.getElementsByTagName ("button") 'aciono o botão pesquisar
Application.Wait (Now + #12:00:02 AM#)

 
Do While ie.busy And ie.readyState <> "READYSTATE_COMPLETE" ' aguardar a página carregar
    DoEvents
Loop

'ie.document.getElementsByTagName("td")(0).CLICK ' Meu problema está aqui!! No resutado exibido preciso clicar no Objeto que possui o hyperlink.    

'ie.Quit

'Range("A3:g3").WrapText = False

End Sub
excel vba url internet-explorer click

  • Dear Icarus, I suggest you format the code as a code block (just add four spaces before each line). Also, explain what the mistake is.

1 answer

1

Dear Icarus, your problem actually starts well before.

The line IE.document.getElementsByTagName("button") 'aciono o botão pesquisar does absolutely nothing. It neither calls any method, nor instantiates the button on a variable, nor triggers the search button -- including because IE.document.getElementsByTagName("button") returns a collection of buttons, not a single button. "Peesquisar" button is the 4th of the collection (index 3).

Therefore, to click the search button, you would need to replace the above statement by IE.document.getElementsByTagName("button")(3).Click 'aciono o botão pesquisar

Also, by going to the part you have already highlighted at the end of the code, you are currently clicking on the HTML table cell that contains the link (TD). This really does not produce any result. You want, yes, click on the link (A) that is inside the cell.

EDITION: I entered the request part, which you asked for in the comments.

So your final code would look like this:

Sub busca_desc()
    
    Dim ie As InternetExplorer
    Dim tbody As HTMLTableSection
    Dim req As New XMLHTTP60
    Dim respostaJson As String, numOportunidade As String
    Dim i As Variant
    
    
    Range("B3:G3").ClearContents 'limpo o range
    numOportunidade = "7003383741" ' Na verdade, toda essa função estará dentro de uma iteração das células com os números de oportunidade que você quer consultar. Aqui, coloquei um valor fixo, mas você provavelmente apontara para o calor contido na célula atual da iteração
    
    Set ie = New InternetExplorer 'crio o objeto IE
    ie.navigate "https://www.petronect.com.br/irj/go/km/docs/pccshrcontent/Site%20Content%20(Legacy)/Portal2018/pt/lista_licitacoes_publicadas_ft.html"  'Url do site
    ie.Visible = True
    
    Do While ie.Busy And ie.readyState <> "READYSTATE_COMPLETE" ' aguardar a página carregar
        DoEvents
    Loop
    
    ie.document.getElementsByTagName("input")(3).Value = numOportunidade ' insiro o valor no campo para pesquisa
    ie.document.getElementsByTagName("button")(3).Click 'aciono o botão pesquisar
    Application.Wait (Now + #12:00:02 AM#)
     
    Do While ie.Busy And ie.readyState <> "READYSTATE_COMPLETE" ' aguardar a página carregar
        DoEvents
    Loop
    
    Set tbody = ie.document.getElementById("result")
    ' Aqui provavelmente você vai querer colocar uma estrutura de loop, algo como:
    For Each i In tbody.getElementsByTagName("a")
        req.Open "GET", "https://www.petronect.com.br/sap/opu/odata/SAP/YPCON_GET_HEADER_INFO_SRV/headerInfoSet('" & numOportunidade & "')?$format=json"
        req.send corpoReq
        respostaJson = req.responseText
        respostaJson = Replace(respostaJson, "\""", """") ' As aspas externas são, no VB, o que delimita uma string. As aspas duplicadas dentro da string são a forma de o VB entender que não se trata da aspa-fim-da-string, e sim de uma aspa dentro do texto da string. Portanto, o que estamos fazendo aí é substituir '\"' por '"'.
        ' Nesse momento, respostaJson conterá um texto bem longo, e a parte dele que importa para você é algo como:
        '"START_DATE":"2020-11-30","START_TIME":"10:00:00","QUOT_DEAD":"2021-02-19","QUOT_DEAD_TIME":"14:00:00","CREATED_AT_DATE":"2020-11-27","CREATED_AT_TIME":"17:19:50","TZONE":"Brasil - Distrito Federal","CURRENCY":"BRL","YPCON_MODALITY":"101","YPCON_MODALITY_NAME":"Licitação, Lei 13.303, Art. 28, CAPUT","PUBL_DOU_DATE":"2020-11-30","DISPUTE_MODE":"02","YPCON_OBJ_CONT_DESC":"Serviço de conceituação, planejamento, criação, manutenção de ambientes digitais que compõem a presença digital da PETROBRAS."
        ' Prontinho! Agora você vai parsear esse texto, buscando as variáveis que te interessarem.
    Next i

    'ie.Quit
    
    'Range("A3:g3").WrapText = False
    
End Sub

P.S.: To enable the classes InternetExplorer, HTMLTableSection and others, you need to go to Tools -> References and select the Microsoft HTML Object Library option.

EDITION: To enable the XMLHTTP60 class, you will need to make another reference, this time to "Microsoft XML, v. #.0" (mine is 6.0).

  • Cesar, I’m new to programming. Could you help even more? Thank you See my logic: 1º open the URL; 2º grab the ID that will be in my spreadsheet (Cell "A2" for example); 3º will feed ie.document.getelementsbytagname("input")(3); 4º will trigger the search button (ie.document.getelementsbytagname("button")(3).Click); 5º will click on tbody.getelementsbytagname("a"); 6º when loading the "new" page will feed the worksheet with the value of id="object_descr_ctr"; 7º after feeding the worksheet of the respective "A2" cell ID, will do the whole process again of the next "A3" cell ID".

  • Ok, the logic seems correct (remembering that an ID may happen to not return any value or return more than one value). Are you having trouble implementing any of these steps?

  • It is unlikely that the ID does not return value, but, would it be enough an error treatment and continue the "Loop"? My difficulty is now in capturing the values of the "new" page after "click" and feeding the worksheet referring to the ID (Cell "A2" for example); Taking these values and feeding the worksheet, implement the "Loop" until the last value of the "A" column".

  • On that page that opens, what data do you want to take?

  • If you try to click on the opportunity link that you have filtered, it will open a new page in the same window -- which is a problem, because then, when we return to the page that contains the results, the objects that are being iterated will no longer be in memory.

  • The simplest solution would be to open in another tab, but it doesn’t work because the opportunity links don’t point to a URL. They make an asynchronous request and return a JSON.

  • The second simplest solution I believe is to make an HTML request. I managed to do without problems and get back the JSON file with the information. The problem here is that the company may have a limitation on the amount of requisitions, or something like that, but it’s worth testing. Tell us what information you want, and we’ll talk about it.

  • Need for: Name, Description of the Object of Employment, Start Date and End Date:

  • I made an edit on the answer, see if it works for you.

  • What variable are you storing the values in? I fed a plan cell with "answerJson", but the values I need don’t come. Thanks again.

  • You can post in this comment a response like this?

  • Hi César, I don’t understand!

  • You can run a test and put here the returned text in a reply?

  • I applied the following command: Cells(3, 5) = answerJson The result was: {"d":{"__Metadata":{"id":"WWW.PETRONECT.COM.BR:443/sap/opu/odata/SAP/..." },"Date":"","Objectid":""}}

  • Caesar, did you manage to identify? Thanks from now on.

Show 10 more comments

Browser other questions tagged excel vba url internet-explorer click

You are not signed in. Login or sign up in order to post.