使用VBA提取网页表格数据遇无效内容，需提取输入框及选中选项值

Hey there! It sounds like your current VBA code is missing the mark when pulling values from input boxes and dropdowns in that web table—let's get that sorted out. The most common issues here are targeting the wrong element properties or not properly identifying the input/dropdown elements within table cells.

Key Issues to Fix #

• For text inputs/hidden fields: Don't use InnerText—their values are stored in the Value attribute.
• For dropdowns (<select> elements): You need to grab the SelectedItem's text or value, not the entire dropdown's text (which shows all options).
• Always ensure the page is fully loaded before trying to access elements.

Modified VBA Code Example #

This code targets input boxes and dropdowns specifically, and outputs the extracted data to an Excel worksheet:

Sub ExtractWebTableValues()
    Dim IE As Object
    Dim targetTable As Object
    Dim tableRow As Object
    Dim tableCell As Object
    Dim inputField As Object
    Dim dropdownMenu As Object
    Dim outputSheet As Worksheet
    Dim rowCounter As Integer, colCounter As Integer
    
    ' Initialize Internet Explorer
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True ' Keep visible for debugging; set to False for silent runs
    IE.Navigate "Your_Target_Page_URL" ' Replace with your actual webpage URL
    
    ' Wait for the page to fully load
    Do While IE.Busy Or IE.ReadyState <> 4
        DoEvents
    Loop
    
    ' Set the worksheet where you want to output data
    Set outputSheet = ThisWorkbook.Sheets("ExtractedData") ' Rename to your sheet
    rowCounter = 1 ' Start writing from row 1
    
    ' Locate your target table (adjust selector based on your HTML)
    ' Option 1: Use table ID (most reliable if available)
    Set targetTable = IE.Document.getElementById("your-table-id")
    ' Option 2: Use first table on page if no ID exists
    ' Set targetTable = IE.Document.getElementsByTagName("table")(0)
    
    ' Loop through each row in the table
    For Each tableRow In targetTable.Rows
        colCounter = 1 ' Reset column counter for each row
        
        For Each tableCell In tableRow.Cells
            ' Check if cell contains an input field
            Set inputField = tableCell.getElementsByTagName("input")(0)
            If Not inputField Is Nothing Then
                ' Extract input value (works for text boxes, hidden inputs, etc.)
                outputSheet.Cells(rowCounter, colCounter).Value = inputField.Value
                colCounter = colCounter + 1
                GoTo NextCell ' Skip to next cell since we handled the input
            End If
            
            ' Check if cell contains a dropdown menu
            Set dropdownMenu = tableCell.getElementsByTagName("select")(0)
            If Not dropdownMenu Is Nothing Then
                ' Extract selected option's display text (or use .Value for option value)
                outputSheet.Cells(rowCounter, colCounter).Value = dropdownMenu.SelectedItem.Text
                ' Alternative: outputSheet.Cells(rowCounter, colCounter).Value = dropdownMenu.Value
                colCounter = colCounter + 1
                GoTo NextCell
            End If
            
            ' If no input/dropdown, extract regular cell text
            outputSheet.Cells(rowCounter, colCounter).Value = tableCell.innerText
            colCounter = colCounter + 1
            
NextCell:
        Next tableCell
        
        rowCounter = rowCounter + 1
    Next tableRow
    
    ' Cleanup
    IE.Quit
    Set IE = Nothing
    Set targetTable = Nothing
    MsgBox "Data extraction finished successfully!", vbInformation
End Sub

Critical Notes for Your Use Case #

1. Adjust Table Selector: Replace "your-table-id" with the actual ID of your target table (check the HTML source). If there's no ID, use the getElementsByTagName("table")(0) approach (change the index 0 to match your table's position).
2. Dropdown Value vs Text: Use dropdownMenu.SelectedItem.Text to get what's visible to the user, or dropdownMenu.Value to get the underlying value attribute of the selected option (use whichever matches your needs for participant number, etc.).
3. Dynamic Content: If the table loads after the initial page load (e.g., via JavaScript), you'll need to add an extra wait for the table element to exist. You can add a loop like this after the initial page load:

   Do While IE.Document.getElementById("your-table-id") Is Nothing
       DoEvents
   Loop
   

Quick Debugging Tip #

If you're still getting invalid data, open the webpage's developer tools (F12), inspect the input/dropdown elements, and confirm their tag names and attributes. This will help you verify that your VBA is targeting the right elements.

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