Excel VBA爬取：如何从表格内name属性为vt的下拉列表选选项

Hey there, let's tackle your two Excel VBA questions related to web dropdowns step by step:

1. Scraping Options from a Web Dropdown with Excel VBA #

If you're looking to extract all options from a dropdown on a webpage using VBA, the most straightforward approach is to use the Internet Explorer object (you can use late binding to avoid needing to set up references manually). Here's a working example:

Sub ScrapeDropdownOptions()
    Dim ie As Object
    Dim htmlDoc As Object
    Dim targetDropdown As Object
    Dim optionItem As Object
    Dim currentRow As Integer
    
    ' Initialize IE object (late binding - no references needed)
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = True ' Set to False for background execution
    
    ' Navigate to your target webpage
    ie.navigate "https://your-target-page-url.com"
    
    ' Wait for the page to fully load
    Do While ie.Busy Or ie.readyState <> 4
        DoEvents
    Loop
    
    ' Grab the HTML document
    Set htmlDoc = ie.document
    
    ' Locate the dropdown - adjust this based on the page's structure
    ' Examples: By tag name (first <select> element), by name, or by ID
    ' Set targetDropdown = htmlDoc.getElementsByName("dropdown-name")(0)
    ' Set targetDropdown = htmlDoc.getElementById("dropdown-id")
    Set targetDropdown = htmlDoc.getElementsByTagName("select")(0)
    
    ' Start writing options to Excel at row 1
    currentRow = 1
    
    ' Loop through each option in the dropdown
    For Each optionItem In targetDropdown.Options
        Cells(currentRow, 1).Value = optionItem.Text ' Write the visible text
        Cells(currentRow, 2).Value = optionItem.Value ' Write the underlying value (if needed)
        currentRow = currentRow + 1
    Next optionItem
    
    ' Clean up
    ie.Quit
    Set ie = Nothing
    Set htmlDoc = Nothing
    Set targetDropdown = Nothing
End Sub

Pro tip: If you know the dropdown's name or id attribute, use getElementsByName or getElementById instead of getElementsByTagName for more precise targeting.

2. Selecting an Option from the Dropdown with Name "vt" #

Once you've located the dropdown with the name attribute set to "vt", you can select an option in a few different ways depending on what you know about the option (index, value, or visible text). Here's how to do it:

Sub SelectVT_DropdownOption()
    Dim ie As Object
    Dim htmlDoc As Object
    Dim vtDropdown As Object
    
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = True
    ie.navigate "https://your-target-page-url.com"
    
    ' Wait for page load
    Do While ie.Busy Or ie.readyState <> 4
        DoEvents
    Loop
    
    Set htmlDoc = ie.document
    
    ' Locate the dropdown with name="vt"
    Set vtDropdown = htmlDoc.getElementsByName("vt")(0)
    
    ' Method 1: Select by index (starts at 0 for the first option)
    vtDropdown.selectedIndex = 1 ' Selects the second option
    
    ' Method 2: Select by the option's value attribute
    ' vtDropdown.Value = "desired-option-value"
    
    ' Method 3: Select by the option's visible text
    ' Dim opt As Object
    ' For Each opt In vtDropdown.Options
    '     If opt.Text = "Option Text You Want" Then
    '         opt.Selected = True
    '         Exit For
    '     End If
    ' Next opt
    
    ' If the dropdown triggers a page action (like a refresh) when changed, fire the onchange event:
    ' vtDropdown.FireEvent "onchange"
    
    ' Uncomment below to close IE when done
    ' ie.Quit
    ' Set ie = Nothing
End Sub

Each method has its use case: go with index if you know the position, value if you have the underlying option value, or text if you only know what's displayed to users.

内容的提问来源于stack exchange，提问作者KingTamo