使用msxml2.xmlhttp提取LBMA网页黄金价格表格数据的问题排查

问题核心原因 #

你的代码出现Object required、Object doesn't support...错误，主要源于两个关键问题：

• 动态页面渲染限制：目标网页的表格数据是通过JavaScript异步加载的，msxml2.xmlhttp仅能获取初始HTML源码，源码中不存在实际的表格行数据，因此所有通过getElementsByTagName/getElementsByClassName定位表格元素的操作都会失效。
• 代码逻辑/语法错误：r变量未初始化（默认值为0，Excel单元格行号从1开始），直接写入Cells(r,c)会触发错误；同时getElementsByTagName("tbody")返回的是元素集合，遍历方式不符合DOM操作规范。

修正方案：直接请求数据API #

绕过动态渲染的问题，直接调用LBMA官方数据API接口，获取结构化的JSON数据后解析提取所需字段，效率更高且稳定性更强。

修正后的代码 #

Sub Get_GoldPrices()
    Dim xhr As Object, jsonObj As Object, items As Object, item As Object
    Dim r As Long
    Dim apiUrl As String
    
    ' API接口：指定黄金、10条数据、仅USD/GBP货币
    apiUrl = "https://www.lbma.org.uk/api/prices/table?currency=GBP,USD&metal=gold&page=1&pageSize=10"
    
    ' 初始化XHR对象
    Set xhr = CreateObject("MSXML2.XMLHTTP.6.0")
    With xhr
        .Open "GET", apiUrl, False
        .setRequestHeader "Accept", "application/json"
        .send
        ' 解析JSON响应（需导入VBA-JSON模块）
        Set jsonObj = JsonConverter.ParseJson(.responseText)
    End With
    
    ' 获取数据列表
    Set items = jsonObj("items")
    
    ' 写入Sheet20，从第1行开始
    r = 1
    With Sheets(20)
        .UsedRange.ClearContents ' 清空原有数据
        ' 遍历前10条数据
        For Each item In items
            .Cells(r, 1) = item("date") ' 日期列
            ' 筛选USD、GBP价格
            For Each price In item("prices")
                Select Case price("currency")
                    Case "USD"
                        .Cells(r, 2) = price("value")
                    Case "GBP"
                        .Cells(r, 3) = price("value")
                End Select
            Next price
            r = r + 1
        Next item
    End With
    
    ' 释放对象
    Set xhr = Nothing
    Set jsonObj = Nothing
    Set items = Nothing
    MsgBox "数据提取完成！"
End Sub

使用注意事项 #

1. 导入JSON解析模块：代码中使用了JsonConverter，需将VBA-JSON的JsonConverter.bas模块导入到你的VBA工程中（可通过VBA编辑器的「文件」→「导入文件」操作）。
2. API参数自定义：可通过修改API参数调整数据范围，比如metal=sliver提取白银数据，pageSize=20提取20条数据。

原代码错误点拆解 #

• 动态页面无法解析：原网页表格由JS动态生成，初始HTML中无实际行数据，所有DOM定位代码均无法找到目标元素。
• 变量未初始化：r变量默认值为0，Excel单元格行号从1开始，写入Cells(0,c)会触发下标越界类错误。
• 集合遍历错误：getElementsByTagName("tbody")返回的是元素集合，直接For Each tr In oTbl会遍历集合中的每个tbody节点，而非tbody内的行节点。

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