如何用Excel VBA抓取网站CMB 5年期收益率？解决Web查询无数据错误

问题原因 #

你使用的QueryTables属于旧版Web查询工具，仅能抓取网页静态HTML内容，但目标网站的收益率数据是通过JavaScript动态渲染生成的，静态源码中不存在这些数据，因此会返回空数据提示。

解决方案：改用XMLHTTP+HTML解析 #

以下代码通过模拟浏览器请求获取完整网页内容，再解析HTML定位目标数据，规避动态加载导致的抓取失败：

Sub ImportCMB5YearYield()
    Dim xmlHttp As Object
    Dim htmlDoc As Object
    Dim ws As Worksheet
    Dim webURL As String
    Dim trElements As Object, tr As Object, td As Object
    
    webURL = "https://www.peakhillcapital.com/dailybondyields/"
    
    ' 初始化对象
    Set xmlHttp = CreateObject("MSXML2.XMLHTTP.6.0")
    Set htmlDoc = CreateObject("HTMLFile")
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' 清空工作表原有数据
    ws.UsedRange.Clear
    
    ' 发送HTTP请求（模拟浏览器UA避免被拦截）
    With xmlHttp
        .Open "GET", webURL, False
        .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36"
        .send
        ' 将返回内容加载到HTML文档解析
        htmlDoc.body.innerHTML = .responseText
    End With
    
    ' 遍历表格行，定位CMB 5-Year对应的收益率
    Set trElements = htmlDoc.getElementsByTagName("tr")
    For Each tr In trElements
        Set td = tr.getElementsByTagName("td")
        If td.Length > 1 Then
            ' 匹配目标行
            If InStr(Trim(td(0).innerText), "CMB 5-Year") > 0 Then
                ws.Range("A1").Value = "CMB 5年期收益率"
                ws.Range("B1").Value = Trim(td(1).innerText)
                MsgBox "数据提取成功！收益率：" & Trim(td(1).innerText)
                Exit For
            End If
        End If
    Next tr
    
    ' 释放对象
    Set trElements = Nothing
    Set htmlDoc = Nothing
    Set xmlHttp = Nothing
    Set ws = Nothing
End Sub

注意事项 #

• 如果网站结构发生变更（如表格布局调整），需修改代码中匹配行的逻辑，可通过浏览器F12查看网页源码确认目标元素位置
• 部分网站会设置反爬机制，若请求失败可尝试更换User-Agent头内容，或添加请求延迟

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