使用VBA定位网页元素并抓取全赛季赛道统计表格导入Excel的问题

核心调整思路 #

• 移除冗余的XMLHTTP请求逻辑，改用IE加载完整页面，等待JS渲染完成后再解析DOM，确保动态生成的全赛季赛道统计表格可以被正常捕获
• 新增精准定位逻辑：先定位类名为tabs-wrapper rns-scroll的容器元素，再在该容器的后代元素中查找目标表格，避免和页面其他同类名的表格混淆

修改后完整代码 #

Sub Horse2()
    Dim IE As InternetExplorer
    Dim ws As Worksheet
    Dim html As HTMLDocument
    Dim tabsWrapper As HTMLHtmlElement
    Dim node As HTMLHtmlElement
    Dim nodeTr As HTMLHtmlElement
    Dim r As Long, c As Long, i As Long
    
    Application.ScreenUpdating = False
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    r = 1 ' 可根据实际需求调整数据写入的起始行
    
    ' 初始化IE并加载目标页面
    Set IE = New InternetExplorer
    IE.Visible = True
    IE.navigate "https://www.racingandsports.com/thoroughbred/jockey/jake-bayliss/27461"
    
    ' 等待页面完全加载、动态内容渲染完成
    Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy
        DoEvents
    Loop
    Set html = IE.document
    
    ' 先定位目标表格所属的上层tabs容器
    Set tabsWrapper = html.getElementsByClassName("tabs-wrapper rns-scroll")(0)
    If Not tabsWrapper Is Nothing Then
        ' 仅在目标容器内匹配表格类名，不会误抓其他位置的同类名表格
        For Each node In tabsWrapper.getElementsByClassName("table rns-table")
            c = 4
            For Each nodeTr In node.getElementsByTagName("tr")
                With nodeTr.getElementsByTagName("td")
                    If .Length > 0 Then
                        r = r + 1
                        ' 循环写入所有列数据，无需重复编写赋值代码
                        For i = 0 To .Length - 1
                            On Error Resume Next
                            ws.Cells(r, c + 3 + i) = .Item(i).innerText
                            On Error GoTo 0
                        Next i
                    End If
                End With
            Next nodeTr
        Next node
    End If
    
    ' 清理资源
    IE.Quit
    Set IE = Nothing
    Set html = Nothing
    Set tabsWrapper = Nothing
    Application.StatusBar = ""
    Application.ScreenUpdating = True

    MsgBox "数据导入完成"
End Sub

关键修改说明 #

• 新增IE页面加载等待逻辑，确保页面所有动态生成的元素全部渲染完成后再执行提取操作
• 调整元素定位路径，先锁定目标表格所在的上层容器，再匹配表格类名，彻底解决同类名表格的混淆问题
• 优化单元格写入逻辑，用循环替代重复的逐列赋值代码，后续如果表格列数有变动也不需要逐行修改代码

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