html - 从类中提取跨度值，VBA

Question

经过大量搜索，我正在努力使用 VBA 从下面的 HTML 中抓取数据。具体来说，我试图从以下 HTML 代码中的每个 class="_Xnb _QJ"中提取值 'DATA ONE' 和 'DATA THREE':

<div class="results">
  <div class="_s2 _wPc">
    <div class="_fW _QJ">
    <div class="_Xnb _QJ _Z9b">
    <div class="_Xnb _QJ">
    <div class="_Xnb _QJ">
    <div class="_Xnb _QJ">
      <a href="//Extracted URL//">
        <span class="_fbb">
          <img id="uid_3" //Extracted// >
        </span>
        <span class="_PHb">
          <span class="_MHb">DATA ONE</span>
        </span>
        <span class="_B6e">
          <span class="_x2">DATA TWO</span>
          <span class="_Fs"> DATA THREE </span>

我一直在尝试使用 getElementsByClassName 来获取“_Xnb _QJ”类的集合，并且对于这些类中的每一个，都使用 getElementsByTagName 来搜索“_MHb”和“_FS”。我无法按数字顺序挑选子项，因为这会在“_Xnb..”类之间发生变化，但我需要的数据始终附有相同的 (_MHb/FS) 类标签。

我是 VBA/HTML 的新手，所以这段代码主要是通过在 stackoverflow 上其他地方编辑示例来组装的。我想知道我需要的类在“href”内而不是直接在 _Xnb 类下是否是我无法提取正确数据的原因？

下面是我的 VBA 代码的相关部分 - 当我运行它时，代码似乎运行良好但未收集任何数据。

Dim RowNumber As Long
Dim DataOne As String
Dim DataThree As String
Dim QuestionList As IHTMLElementCollection
Dim Question As IHTMLElement
Dim QuestionFields As IHTMLElementCollection
Dim QuestionField As IHTMLElement
RowNumber = 1

Set QuestionList = html.getElementsByClassName("_Xnb _QJ")

For Each Question In QuestionList
Set QuestionFields = Question.getElementsByTagName("SPAN")

For Each QuestionField In QuestionFields
If QuestionField.className = "_MHb" Then
DataOne= QuestionField.innerText
Cells(RowNumber, 1).Value = DataOne
End If

If QuestionField.className = "_Fs" Then
DataThree = QuestionField.innerText
Cells(RowNumber, 2).Value = DataThree
End If

Next QuestionField
RowNumber = RowNumber + 1
Next
Set html = Nothing
MsgBox "Done!"

End Sub

如有任何帮助，我们将不胜感激。

非常感谢

Answer 1

我建议您研究一下 XPath - 用于处理 XML 文档的基于标准的查询语言。您也可以在格式良好的 HTML 文档中使用它。它有点神秘，但非常有用，也可以在 VBA 中使用。

您的示例 HTML 看起来有点复杂，因为您有多个 <div>具有相同类别的标签。由于 //Extracted//，它也不是有效的 XML在<img>标签。此外，示例中没有结束标记。不管怎样，我已经在下面的代码示例中对其进行了整理。

我看过你的问题，然后这样解释:

Extract any text from a <span> tag where it is of class _MHb or Fs; and where it is a descendant of a <div> tag of class _Xnb _QJ

如果是这样，您的 XPath 查询可以分为三个部分:

//div[@class='_Xnb _QJ']

含义 - 获取类为 _Xnb _QJ 的所有 div 标签.

(//div[@class='_Xnb _QJ'])[last()]

意思 - 只从第一组中获取最里面的项目(记住你有多个嵌套的 <div> 标签具有相同的类)。

(//div[@class='_Xnb _QJ'])[last()]//span[@class='_MHb' or @class='_Fs']

含义——过滤最里面的<div>对于 <span>类别为 _Mhb 的标签或 _Fs .

因此，如果包含 MSXML 库(我想您已经这样做了)，就可以在 VBA 中使用 XPath。代码如下所示:

Option Explicit

Sub Test()

    Dim strXml As String
    Dim objXml As New DOMDocument60
    Dim strXPath As String
    Dim objXmlNodeList As IXMLDOMNodeList
    Dim objXmlNode As IXMLDOMNode

    'get the sample XML
    strXml = GetXml

    'load xml to document
    If Not objXml.LoadXML(strXml) Then
        Debug.Print "Not parsed"
        Exit Sub
    End If

    'apply XPath
    'first just let's get the last <div> tag of class _Xnb _QJ
    strXPath = "(//div[@class='_Xnb _QJ'])[last()]"
    'test that query
    Set objXmlNodeList = objXml.SelectNodes(strXPath)
    For Each objXmlNode In objXmlNodeList
        Debug.Print objXmlNode.XML
    Next objXmlNode

    'now lets append a filter to only get the <span> texts
    strXPath = strXPath & "//span[@class='_MHb' or @class='_Fs']"

    'get output nodes by applying query to xml
    Set objXmlNodeList = objXml.SelectNodes(strXPath)
    For Each objXmlNode In objXmlNodeList
        Debug.Print objXmlNode.Text
    Next objXmlNode

End Sub

Function GetXml() As String

    Dim strXml As String

    strXml = ""
    strXml = strXml & "<div class=""results"">"
    strXml = strXml & "  <div class=""_s2 _wPc"">"
    strXml = strXml & "    <div class=""_fW _QJ"">"
    strXml = strXml & "      <div class=""_Xnb _QJ _Z9b"">"
    strXml = strXml & "        <div class=""_Xnb _QJ"">"
    strXml = strXml & "          <div class=""_Xnb _QJ"">"
    strXml = strXml & "            <div class=""_Xnb _QJ"">"
    strXml = strXml & "              <a href=""//Extracted URL//"">"
    strXml = strXml & "                <span class=""_fbb"">"
    strXml = strXml & "                  <img id=""uid_3"" />"
    strXml = strXml & "                </span>"
    strXml = strXml & "                <span class=""_PHb"">"
    strXml = strXml & "                  <span class=""_MHb"">DATA ONE</span>"
    strXml = strXml & "                </span>"
    strXml = strXml & "                <span class=""_B6e"">"
    strXml = strXml & "                  <span class=""_x2"">DATA TWO</span>"
    strXml = strXml & "                  <span class=""_Fs""> DATA THREE </span>"
    strXml = strXml & "                </span>"
    strXml = strXml & "              </a>"
    strXml = strXml & "            </div>"
    strXml = strXml & "          </div>"
    strXml = strXml & "        </div>"
    strXml = strXml & "      </div>"
    strXml = strXml & "    </div>"
    strXml = strXml & "  </div>"
    strXml = strXml & "</div>"

    GetXml = strXml

End Function

调试输出如下所示:

<div class="_Xnb _QJ">
    <a href="//Extracted URL//">
        <span class="_fbb">
            <img id="uid_3"/>
        </span>
        <span class="_PHb">
            <span class="_MHb">DATA ONE</span>
        </span>
        <span class="_B6e">
            <span class="_x2">DATA TWO</span>
            <span class="_Fs"> DATA THREE </span>
        </span>
    </a>
</div>
DATA ONE
DATA THREE

这一切看起来有点复杂 - 但只要你尝试几次就会没问题。