使用VBA从Span标签提取MLB赛事日期数据求助

我正尝试从指定MLB球员页面获取赛事数据，但无法从以下HTML元素中提取日期（Jun 19）：

<td class="Px(cell-padding-x) Py(cell-padding-y) Va(m) Ta(start) Fw(500) Whs(nw) Pos(r) Cnt(cq):h::a Pos(a):h::a Bgc(table-hover):h::a Start(0):h::a T(-5000px):h::a H(10000px):h::a W(100%):h::a Z(-1):h::a"><span class=""><span>Jun 19</span></span></td>

我试过多种方法都没成功，以下是我最新的尝试代码：

Sub MBL_player()
    Dim ieObj As InternetExplorer
    Dim htmlEle As IHTMLElement
    Dim i As Integer

    i = 2
    
    Set ieObj = New InternetExplorer
    ieObj.Visible = True
    ieObj.navigate "https://sports.yahoo.com/mlb/players/9329/"
    
    Application.Wait Now + TimeValue("00:00:05")
    
    Dim tables As Object
    Set tables = ieObj.document.getElementsByClassName("table graph-table W(100%) Ta(start) Bdcl(c) Mb(56px) Ov(h)")

        text = span.innerText
    
    ' Check if there are at least two tables on the page
    If tables.Length >= 2 Then
        Dim secondTable As Object
        Set secondTable = tables(1) ' Index 1 represents the second table
        
        For Each htmlEle In secondTable.getElementsByClassName("Bgc(table-hover):h")
            With ActiveSheet
                .Range("A" & i).Value = htmlEle.Children(0).getElementsByTagName("span")(0).innerText
                .Range("B" & i).Value = htmlEle.Children(1).textContent
                .Range("C" & i).Value = htmlEle.Children(2).textContent
                .Range("D" & i).Value = htmlEle.Children(3).textContent
                .Range("E" & i).Value = htmlEle.Children(4).textContent
                .Range("F" & i).Value = htmlEle.Children(5).textContent
                .Range("G" & i).Value = htmlEle.Children(6).textContent
                .Range("H" & i).Value = htmlEle.Children(7).textContent
                .Range("I" & i).Value = htmlEle.Children(8).textContent
                .Range("J" & i).Value = htmlEle.Children(9).textContent
                .Range("K" & i).Value = htmlEle.Children(10).textContent
                .Range("L" & i).Value = htmlEle.Children(11).textContent
                .Range("M" & i).Value = htmlEle.Children(12).textContent
                .Range("N" & i).Value = htmlEle.Children(13).textContent
                .Range("O" & i).Value = htmlEle.Children(14).textContent
            End With
        
            i = i + 1
        Next htmlEle
    Else
        MsgBox "The page does not contain at least two tables."
    End If
    
    ieObj.Quit
End Sub

问题分析与修复方案 #

1. 冗余代码报错：代码中text = span.innerText属于未定义对象的无效语句，直接删除即可。
2. 行元素定位错误：Bgc(table-hover):h是鼠标悬停时才生效的样式类，不能用来定位默认状态的表格行，应该直接遍历表格的<tr>元素，并跳过表头行。
3. 日期层级提取错误：日期嵌套在两层<span>内，原代码只取了第一层，需要定位到第二层<span>才能拿到日期文本。

修复后的代码 #

Sub MLB_player()
    Dim ieObj As InternetExplorer
    Dim targetTable As Object
    Dim tableRow As Object
    Dim tableCell As Object
    Dim rowIndex As Integer
    
    rowIndex = 2
    
    Set ieObj = New InternetExplorer
    ieObj.Visible = True
    ieObj.navigate "https://sports.yahoo.com/mlb/players/9329/"
    
    ' 等待页面完全加载，替代固定等待更可靠
    Do While ieObj.Busy Or ieObj.readyState <> 4
        DoEvents
    Loop
    
    ' 直接获取第二个目标表格
    Set targetTable = ieObj.document.getElementsByClassName("table graph-table W(100%) Ta(start) Bdcl(c) Mb(56px) Ov(h)")(1)
    
    ' 遍历所有表格行，跳过表头
    For Each tableRow In targetTable.getElementsByTagName("tr")
        If tableRow.getElementsByTagName("td").Length > 0 Then
            Dim cellIndex As Integer
            cellIndex = 0
            For Each tableCell In tableRow.getElementsByTagName("td")
                cellIndex = cellIndex + 1
                If cellIndex = 1 Then
                    ' 提取第二层span的文本作为日期
                    ActiveSheet.Cells(rowIndex, cellIndex).Value = tableCell.getElementsByTagName("span")(1).innerText
                    ' 可选：设置日期格式
                    ActiveSheet.Cells(rowIndex, cellIndex).NumberFormat = "mmm dd"
                Else
                    ActiveSheet.Cells(rowIndex, cellIndex).Value = tableCell.textContent
                End If
            Next tableCell
            rowIndex = rowIndex + 1
        End If
    Next tableRow
    
    ieObj.Quit
End Sub

额外优化提示 #

• 用Do While循环等待页面加载，比固定时长等待更适配不同网络速度的加载情况。
• 如果页面存在动态加载的赛事数据，可在获取表格前增加额外的等待逻辑，或者改用XMLHTTP请求直接获取页面源码，效率更高且无需打开浏览器。

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