Excel网页查询完成后执行操作报错找不到QueryTables(1)如何解决

错误原因 #

你遇到的报错是因为当前Excel版本中，通过Power Query（也就是你代码里命名为Abfrage - Umrechnung、Abfrage - Übersicht这类的查询）导入的数据，存储在工作表的**表对象（ListObject）**中，而非旧版Excel的QueryTable对象，所以Sheets("All").QueryTables(1)找不到对应对象，直接触发报错。

解决步骤 #

步骤1：确认查询对应的表名 #

打开All工作表，点击数据区域的任意单元格，查看Excel顶部公式栏左侧的名称框，就能看到该表的名称，默认一般为表1、Table1，你也可以手动修改为好记的名称。

步骤2：修正宏代码 #

同时要注意，默认的连接刷新是后台执行的，如果你要等刷新完成再执行格式调整，需要先关闭后台刷新，或者等待刷新状态结束，修正后的参考代码如下：

Sub all_refresh()
    Dim ws As Worksheet
    Dim targetTable As ListObject
    Dim conn As WorkbookConnection
    
    ' 定义目标工作表
    Set ws = ThisWorkbook.Sheets("All")
    ' 替换成你自己的表名，比如Table1、表1
    Set targetTable = ws.ListObjects("你的表名")
    
    ' 先关闭所有查询的后台刷新，避免刷新未完成就执行后续代码
    For Each conn In ThisWorkbook.Connections
        If conn.Type = xlConnectionTypeOLEDB Then
            conn.OLEDBConnection.BackgroundQuery = False
        End If
    Next conn
    
    ' 执行刷新
    ThisWorkbook.Connections("Abfrage - Umrechnung").Refresh
    ThisWorkbook.Connections("Abfrage - Übersicht").Refresh
    
    ' 后续格式调整代码写在这里，此时刷新已经完成
    ' 示例：选中整个表的数据区域
    targetTable.DataBodyRange.Select
    
    ' 如果你需要恢复后台刷新可以取消注释下面的代码
    ' For Each conn In ThisWorkbook.Connections
    '     If conn.Type = xlConnectionTypeOLEDB Then
    '         conn.OLEDBConnection.BackgroundQuery = True
    '     End If
    ' Next conn
End Sub

特殊情况：使用旧版导入向导的场景 #

如果你的查询是通过Excel旧版“自网站”导入向导创建的，确实属于QueryTable对象，可以先运行下面的代码遍历输出当前工作表所有QueryTable的名称，替换到原代码里即可：

Sub 查QueryTable名称()
    Dim qt As QueryTable
    For Each qt In Sheets("All").QueryTables
        MsgBox "QueryTable名称：" & qt.Name
    Next qt
End Sub

• 如果需要每次查询刷新完成后自动执行格式调整，不需要手动运行宏，可以把格式调整逻辑写在Worksheet_AfterRefresh事件中。

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