如何使用Excel VBA提取指定网页特定class属性div下的文本内容

原代码错误原因 #

• 目标节点class属性值匹配错误：代码中填写的class值多了两处多余空格，和页面实际属性值不一致，导致无法定位到目标div节点
• 逻辑结构错误：外层嵌套了不必要的With html.getElementsByClassName("np mainparent")块，内层赋值时错误取了该块下元素的文本，而非目标div的内容
• 冗余循环：不需要嵌套遍历子div节点，目标节点的innerText中直接包含所需的JAKE BAYLISS文本

修正后代码 #

Sub Horse6()
    Dim ws As Worksheet
    Dim r As Integer
    Dim c As Integer
    Dim http As New XMLHTTP60
    Dim html As New HTMLDocument
    Dim targetNodes As IHTMLDOMChildrenCollection

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    r = 2
    c = 12
  
    With http
        .Open "GET", "https://www.racingandsports.com/thoroughbred/jockey/jake-bayliss/27461", False
        .send
        html.body.innerHTML = .responseText
    End With
  
    ' 直接定位目标节点
    Set targetNodes = html.getElementsByClassName("col-xs-12 col-sm-12 col-md-6 col-lg-5 col-md-pull-6 col-lg-pull-7 p-main-title-wrapper")
    ' 判断节点是否存在，避免报错
    If targetNodes.Length > 0 Then
        ws.Cells(r, c) = Trim(targetNodes.Item(0).innerText)
    End If
  
    MsgBox "Data input complete"
End Sub

注意事项 #

运行代码前请确保VBA编辑器中已提前引用两个库：Microsoft XML, v6.0、Microsoft HTML Object Library，否则会报类型未定义错误。

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