Selecting a dropdown list when inserting data from web (VBA)

Question

I want to download some data from a webpage (http://www.debentures.com.br/exploreosnd/consultaadados/sndemumclique/) into an Excel spreadsheet.

After loading this page I have to manually choose an option from the "Código do Ativo" dropdown list, and then choose "Agenda".

Is there a way I can do it automatically via VBA?

For example: selecting "RDVT11" from the "Código do Ativo" dropdown list, selecting "Agenda" and then downloading the data from the table that will appear in the bottom part of the page?

My macro so far:

Private Sub Agenda()
Sheets("Dados").Select

Dim ProductionAddress As String
ProductionAddress = "http://www.debentures.com.br/exploreosnd/consultaadados/sndemumclique/x_pu_historico_r.aspx?"

Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
With ie
    .Silent = True
    .Visible = True
    .Navigate ProductionAddress
End With

While ie.ReadyState <> 4 Or ie.Busy: DoEvents: Wend

ie.document.getElementByid("ctl00_ddlAti").Value = "RDVT11|11001110111100001" 

 While ie.ReadyState <> 4 Or ie.Busy: DoEvents: Wend


Set objButton = ie.document.getElementByid("ctl00_x_agenda_r")
    objButton.Focus
    objButton.Click
 While ie.ReadyState <> 4 Or ie.Busy: DoEvents: Wend

ie.Quit
 Set ie = Nothing
End Sub

score 0 · Answer 1 · answered Jan 21 '17 at 21:51

0

You need to capture the request that a browser sends when the dropdown is activated. Open up Chrome dev tools and watch the network tab. You will see a POST request to sndemumclique/. This will have some headers and form data. Your code will need to basically replicate this request. Likely, not all of the header and form fields are required but there is not way to know without trying.

answered Jan 21 '17 at 21:51

Grisha Levit

8,194
2
38
53

I added some code I made to the question... If you can take a look on it I would really appreciate... – Felippe Mendonca Jan 22 '17 at 14:42
I got it to load the "Código do Ativo" and then click on "Agenda". The only thing that I am missing now is: How can I download the data from the table that appears on the bottom part of the page into the spreadsheet? Can you please help me, Grisha? Thanks a lot! – Felippe Mendonca Jan 22 '17 at 15:55
I posted my code so far. It goes into the page. Inserts the "Código do Ativo" and clicks on "Agenda". Now I'm having trouble downloading the table into the spreadsheet (The table is called "ctl00_ContentPlaceHolder1_C_agenda_r1_grdAgenda". Do you think you can help me? – Felippe Mendonca Jan 22 '17 at 16:46

QHarr · Answer 2 · 2018-06-16T14:27:24.723

Here are all 3 parts. The making two selections and the writing of the table to the sheet.

Notes:

① Making first selection:

To make the RDVT11 selection, I first use the Id of the dropdown to capture the element in a variable with:

Set a = .document.getElementById("ctl00_ddlAti")

Next, I loop the drop down options, using a.getElementsByTagName("Option") to generate the collection which I loop over. When the target selection text is found, I set that option to Selected and exit the loop.

For Each currentOption In a.getElementsByTagName("Option")
    If InStr(currentOption.innerText, optionText) > 0 Then
        currentOption.Selected = True
        Exit For
    End If
Next currentOption

② Making Agenda selection:

I then target the agenda option of Sobre e emissão by its id and click it and wait for a refresh of the page:

.document.getElementById("ctl00_x_agenda_r").Click
While .Busy Or .readyState < 4: DoEvents: Wend

③ Getting the table and writing to sheet:

I then target the table that is loaded by its id. This is done within a loop to ensure the table is present:

Do: On Error Resume Next: Set nTable = .document.getElementById("aGENDA"): On Error GoTo 0: DoEvents: Loop While nTable Is Nothing

I finally, loop the rows and columns in the table, writing out to the Activesheet.

Code:

Option Explicit
Public Sub MakeSelectiongGetData()
    Dim IE As New InternetExplorer
    Const URL = "http://www.debentures.com.br/exploreosnd/consultaadados/sndemumclique/"
    Const optionText As String = "RDVT11"
    Application.ScreenUpdating = False
    With IE
        .Visible = True
        .navigate URL

        While .Busy Or .readyState < 4: DoEvents: Wend

        Dim a As Object
        Set a = .document.getElementById("ctl00_ddlAti")

        Dim currentOption As Object
        For Each currentOption In a.getElementsByTagName("Option")
            If InStr(currentOption.innerText, optionText) > 0 Then
                currentOption.Selected = True
                Exit For
            End If
        Next currentOption
        .document.getElementById("ctl00_x_agenda_r").Click
        While .Busy Or .readyState < 4: DoEvents: Wend

        Dim nTable As HTMLTable

        Do: On Error Resume Next: Set nTable = .document.getElementById("aGENDA"): On Error GoTo 0: DoEvents: Loop While nTable Is Nothing

        Dim nRow As Object, nCell As Object, r As Long, c As Long

        With ActiveSheet
            Dim nBody As Object
            Set nBody = nTable.getElementsByTagName("tbody")(0).getElementsByTagName("tr")
            .Cells(1, 1) = nBody(0).innerText
            For r = 2 To nBody.Length - 1
                Set nRow = nBody(r)
                For Each nCell In nRow.Cells
                    c = c + 1: .Cells(r + 1, c) = nCell.innerText
                Next nCell
                c = 0
          Next r
    End With
    .Quit
End With
Application.ScreenUpdating = True
End Sub

Data on page (sample)

Code output (sample):

Selecting a dropdown list when inserting data from web (VBA)

2 Answers2

Linked