从Web(VBA)插入数据时选择下拉列表 [英] Selecting a dropdown list when inserting data from web (VBA)

问题描述

我想从网页上下载一些数据(

---

代码输出(示例):

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

解决方案

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):

这篇关于从Web(VBA)插入数据时选择下拉列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！