不同PC上VBA代码中QueryTable的WebTable编号为何不一致？

Hey Matt, let's dig into why your .WebTables parameter is behaving differently across your home and work PCs—this is a super common gotcha with web scraping via Excel QueryTables, even when the VBA environment looks identical.

可能的差异原因 #

1. 浏览器渲染引擎版本差异 #

Excel's QueryTables rely on the IE rendering engine (even if you use Chrome/Firefox day-to-day). Different IE versions (or Edge's IE compatibility mode settings) on your two PCs can parse FanGraphs' HTML structure differently, shifting the number assigned to your target table.

• 验证方法: On both PCs, open the FanGraphs leaderboard page, hit F12 to open developer tools, then count the <table> tags in order (Excel counts all tables, including hidden ones). You’ll likely see the target table sits at position 21 on your home PC and 12 on your work PC.

2. 页面动态内容差异 #

FanGraphs may load different page elements based on geolocation, login status, browser cookies/cache. For example, if your work PC is logged into a FanGraphs account while your home PC isn’t, extra account-specific tables (like saved leaderboards) could throw off the table numbering.

• 验证方法: Open the page in incognito/private mode on both PCs. If the .WebTables parameter matches now, login status or cached data is the culprit.

3. Excel security/trust center settings #

Work PCs often have group policy restrictions that block certain web elements (like scripts or ActiveX controls) from loading, which can break full page rendering and alter table counts. Your home PC probably has looser default settings.

• 验证方法: Check File > Options > Trust Center > Trust Center Settings on both PCs, ensuring "External Content" and "Macro Settings" are configured identically.

4. Hidden HTML updates from FanGraphs #

FanGraphs might have quietly tweaked their page structure (added hidden stats tables, adjusted layout) without you noticing. If one PC cached the old page version while the other loaded the latest, that would create a table number mismatch.

解决办法：摆脱对表格编号的依赖 #

Instead of relying on fragile table numbers, use more reliable methods to target your data:

方法1：用表格HTML属性定位 #

Modify your code to target the table by its id or class attribute (FanGraphs leaderboards usually have a unique ID like LeaderBoard1) instead of its position:

' Replace the .WebTables = "21" line with this approach
With Sheet46.QueryTables.Add(Connection:=URL, Destination:=Range("a2"))
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .WebSelectionType = xlEntirePage ' Grab the whole page first
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With

' Now find the target table by its ID
Dim targetTable As ListObject
On Error Resume Next
Set targetTable = Sheet46.ListObjects("LeaderBoard1") ' Replace with actual table ID
On Error GoTo 0

If Not targetTable Is Nothing Then
    targetTable.Range.Copy Destination:=Sheet46.Range("A2")
    ' Clean up empty columns if needed (like column B on your home PC)
    If Sheet46.Range("B1").Value = "" Then Sheet46.Range("B:B").ClearContents
End If

方法2：动态自动识别目标表格 #

Add code to scan the page's HTML and find the table by its header content (e.g., looking for a "Name" header) instead of counting:

' First, fetch the page HTML to scan tables
Dim htmlDoc As Object, tbl As Object
Set htmlDoc = CreateObject("HTMLFile")

With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", URL, False
    .Send
    htmlDoc.body.innerHTML = .responseText
End With

' Find the table with your target header (adjust "Name" to match your leaderboard's first header)
Dim targetTblNum As Integer: targetTblNum = 0
For Each tbl In htmlDoc.getElementsByTagName("table")
    targetTblNum = targetTblNum + 1
    If tbl.getElementsByTagName("th").Length > 0 Then
        If tbl.getElementsByTagName("th")(0).innerText = "Name" Then Exit For
    End If
Next tbl

' Now use the dynamically found table number
With Sheet46.QueryTables.Add(Connection:=URL, Destination:=Range("a2"))
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebTables = CStr(targetTblNum) ' Use the auto-detected number
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With

Either approach will make your code resilient to table number changes across different PCs.

内容的提问来源于stack exchange，提问作者Matt O.