VBA QueryTables指定WebClass提取网页表格失败求助

你遇到的问题根源在于Excel QueryTables的WebClass属性根本不是用来通过CSS类名定位网页元素的，这个属性的设计初衷不支持CSS选择器匹配，所以你指定snapshot-table2-table这类类名后，QueryTables完全无法识别，直接导致数据抓取失败。

方案1：用WebTables指定表格索引（简单快速） #

Finviz报价页里，目标表格（snapshot-table2-table）对应的<table>标签有固定索引，替换掉无效的WebClass设置即可：

修改后的VBA代码：

Dim wb As Workbook
Dim src As Worksheet
Dim tgt As Worksheet

Set wb = ThisWorkbook
Set src = wb.Sheets("CP")
Set tgt = wb.Sheets("00")

Dim url As String
Dim symbol As String

symbol = src.Range("C4").Value
url = "https://finviz.com/quote.ashx?t=" & symbol & "&p=d"

On Error Resume Next

' 先清空目标区域旧数据（可选）
tgt.Range("A2").CurrentRegion.Clear

With tgt.QueryTables.Add(Connection:= _
    "URL;" & url, _
    Destination:=tgt.Range("A2"))
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingNone
    ' Finviz报价页中目标表格的索引通常是2，不对的话自己数页面里的<table>标签
    .WebTables = "2"
    .Refresh BackgroundQuery:=False
End With

If Err.Number <> 0 Then
    MsgBox "获取数据出错了，检查下股票代码或网络吧。"
    Exit Sub
End If

On Error GoTo 0

方案2：用XMLHTTP+DOM解析（精准可靠） #

如果表格索引经常变，推荐用这种方式直接通过CSS类名定位，稳定性更强：

Dim wb As Workbook
Dim src As Worksheet
Dim tgt As Worksheet
Dim xmlHttp As Object
Dim htmlDoc As Object
Dim targetTable As Object
Dim row As Object
Dim cell As Object
Dim i As Integer, j As Integer

Set wb = ThisWorkbook
Set src = wb.Sheets("CP")
Set tgt = wb.Sheets("00")
Set xmlHttp = CreateObject("MSXML2.XMLHTTP.6.0")
Set htmlDoc = CreateObject("HTMLFile")

Dim symbol As String, url As String
symbol = src.Range("C4").Value
url = "https://finviz.com/quote.ashx?t=" & symbol & "&p=d"

' 发送请求，加个User-Agent避免被拦截
xmlHttp.Open "GET", url, False
xmlHttp.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) Chrome/114.0.0.0 Safari/537.36"
xmlHttp.send

' 加载网页内容
htmlDoc.body.innerHTML = xmlHttp.responseText

' 直接通过CSS类名抓目标表格
Set targetTable = htmlDoc.querySelector(".snapshot-table2-table")

If targetTable Is Nothing Then
    MsgBox "没找到目标表格，要么页面变了，要么股票代码错了。"
    Exit Sub
End If

' 清空旧数据
tgt.Range("A2").CurrentRegion.Clear

' 遍历表格内容写入Excel
i = 2
For Each row In targetTable.Rows
    j = 1
    For Each cell In row.Cells
        tgt.Cells(i, j).Value = cell.innerText
        j = j + 1
    Next cell
    i = i + 1
Next row

' 释放对象
Set xmlHttp = Nothing
Set htmlDoc = Nothing
Set targetTable = Nothing
MsgBox "数据抓完了。"

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