使用Excel VBA提取非简单HTML页面中的Logical CPU数值

问题 #

我原本用以下VBA代码提取HTML页面中的简单表格数据：

Sub Export_HTML_Table_To_Excel()
    Dim htm As Object
    Dim Tr As Object
    Dim Td As Object
    Dim Tab1 As Object
    Dim Web_URL As String
    Dim wsgfLPARs As Worksheet
    Dim lrow As Long
    Dim irow As Long
    Dim icol As Integer
    Dim Column_Num_To_Start As Integer
    Dim iTable As Integer
    Dim HTML_Content As Variant
    
    Set wsgfLPARs = Application.ThisWorkbook.Sheets("Sheet3")
    
    Web_URL = "http://10.201.xxx.yy/iotdashboard/htmlibm/top/all_lpars.html"
    
    'Create HTMLFile Object
    Set HTML_Content = CreateObject("htmlfile")

    'Get the WebPage Content to HTMLFile Object
    With CreateObject("msxml2.xmlhttp")
        .Open "GET", Web_URL, False
        .send
        HTML_Content.Body.Innerhtml = .responseText
    End With

    Column_Num_To_Start = 1
    irow = 0
    icol = Column_Num_To_Start
    iTable = 0

    'Loop Through Each Table and Download it to Excel in Proper Format
    For Each Tab1 In HTML_Content.getElementsByTagName("table")
        With HTML_Content.getElementsByTagName("table")(iTable)
            For Each Tr In .Rows
                If Tr.Cells(1).innerText <> "" Then
                    For Each Td In Tr.Cells
                        wsgfLPARs.Cells(irow, icol).Select
                        wsgfLPARs.Cells(irow, icol) = Td.innerText
                        icol = icol + 1
                    Next Td
                    icol = Column_Num_To_Start
                    irow = irow + 1
                End If
            Next Tr
        End With
        iTable = iTable + 1
        icol = Column_Num_To_Start
        irow = irow + 1
    Next Tab1
End Sub    

但现在需要从含复杂内容的其他网页提取特定数值，目标是提取图表下方的Logical CPU数值，对应HTML片段如下：

<tr class="css-47yhhe-LegendRow"><td><span class="css-fblkr"><div class="pointer" style="background: rgb(115, 191, 105); width: 14px; height: 4px; border-radius: 1px; display: inline-block; margin-right: 8px;"></div><div class="css-w166kv-LegendLabel-LegendClickable">Idle </div></span></td><td class="css-1bpvq0r">93.0%</td><td class="css-1bpvq0r">95.9%</td><td class="css-1bpvq0r">97.3%</td><td class="css-1bpvq0r">29.9%</td></tr>

我尝试过getElementsByTagName、getElementsByClassName和getElementsByID方法，但均未成功，且不确定参数传递方式，需要技术指导。

解决方案 #

核心思路 #

针对目标HTML结构，通过以下步骤精准定位并提取数值：

1. 定位class为css-47yhhe-LegendRow的<tr>元素（图表图例的行容器）
2. 在该行内找到包含文本Idle的<div>，确认目标行
3. 提取该行内所有class为css-1bpvq0r的<td>中的数值

修改后的VBA代码 #

Sub Extract_Logical_CPU_Idle()
    Dim HTML_Content As Object
    Dim Web_URL As String
    Dim ws As Worksheet
    Dim legendRows As Object
    Dim row As Object
    Dim labelDiv As Object
    Dim valueCells As Object
    Dim cell As Object
    Dim i As Integer
    Dim outputRow As Long
    
    ' 设置输出工作表和起始行
    Set ws = Application.ThisWorkbook.Sheets("Sheet3")
    outputRow = 1
    
    ' 替换为实际目标网页URL
    Web_URL = "你的目标网页URL"
    
    ' 创建并加载HTML内容
    Set HTML_Content = CreateObject("htmlfile")
    With CreateObject("msxml2.xmlhttp")
        .Open "GET", Web_URL, False
        .send
        HTML_Content.Body.Innerhtml = .responseText
    End With
    
    ' 获取所有图例行
    Set legendRows = HTML_Content.getElementsByClassName("css-47yhhe-LegendRow")
    
    ' 遍历每行寻找Idle对应的数值
    For Each row In legendRows
        ' 用CSS选择器定位标签元素，避免多层嵌套遍历
        On Error Resume Next
        Set labelDiv = row.querySelector(".css-w166kv-LegendLabel-LegendClickable")
        On Error GoTo 0
        
        If Not labelDiv Is Nothing Then
            ' 去除文本首尾空格，精准匹配Idle行
            If Trim(labelDiv.innerText) = "Idle" Then
                ' 获取所有数值单元格
                Set valueCells = row.getElementsByClassName("css-1bpvq0r")
                
                ' 将数值写入Excel
                i = 1
                For Each cell In valueCells
                    ws.Cells(outputRow, i).Value = cell.innerText
                    i = i + 1
                Next cell
                
                ' 输出行下移
                outputRow = outputRow + 1
            End If
        End If
    Next row
    
    MsgBox "提取完成"
End Sub

关键说明 #

• querySelector用法：通过CSS选择器直接定位元素，比传统的标签/类名遍历更高效，适合处理嵌套结构的网页
• 错误处理：添加On Error Resume Next避免因元素不存在导致代码中断
• 精准匹配：用Trim()处理文本首尾空格，防止因HTML中的空格导致匹配失败
• 兼容性：htmlfile对象支持getElementsByClassName和querySelector，适配现代网页结构

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