Excel VBA调用PowerQuery加载PDF表格：分步正常连续运行无数据

通过Excel VBA调用PowerQuery，将多页PDF中的表格数据转换并加载到Excel工作表。流程为：先调用DisplayListTable过程加载PDF，获取各页面的名称及类型（页面/表格），再通过其他过程提取每页表格数据并处理。

故障现象：分步执行代码（F8）或在PowerQuery执行后设置断点时，数据加载正常；但连续运行（F5）时，无数据加载，仅显示"ExternalData_1: Getting Data..."。已尝试添加等待、刷新、DoEvents，禁用/启用事件及屏幕更新，均无效。

原DisplayListTable过程代码 #

Sub DisplayListTable(v_TableName as string v_Filename as string)

    'delete preexisting queries
    on error resume next
    thisworkbook.queries(v_TableName).Delete
    on error goto 0
    
    DoEvents

    thisworkbook.activate
    thisworkbook.queries.add name:=v_TableName; formula:= _
    "let" & chr(13) & "" & chr(10) & "    Source = Pdf.Tables(File.Contents("""" & v_FileName & """"), [Implementation=""1.3""])" & chr(13) & "" & chr(10) & "in" & chr(13) & "" & chr(10) & "    Source"
    thisworkbook.worksheets("LIST_OF_TABLE").select
    With activesheet.ListObjects.add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location="""" & v_TableName & """";Extended Properties="""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & v_TableName & "]")
        .RowNumbers = false
        .FillAdjacentFormulas = false
        .PreserveFormatting = true
        .RefreshOnFileOpen = false
        '.BackgroundQuery = true
        .BackgroundQuery = false
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = true
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = true
        .Listobject.Name = v_TableName
        .Refresh
    End With
    
End Sub

1. 修正代码语法错误 #

原代码存在多处语法问题，是导致连续运行失败的核心原因：

• 过程参数定义缺少逗号：v_TableName as string v_Filename as string → v_TableName As String, v_Filename As String
• Queries.Add方法的参数分隔符错误：分号;改为逗号,
• PowerQuery公式中变量名写错：v_FileName → v_Filename
• OLEDB连接字符串引号冗余：Location="""" & v_TableName & """" → Location=""" & v_TableName & """

2. 强制等待刷新完成 #

即使设置BackgroundQuery = False，VBA仍可能提前执行后续逻辑，需添加循环等待刷新结束：
在.Refresh语句后添加：

Do While .Refreshing
    DoEvents
Loop

3. 移除不必要的激活/选择操作 #

激活工作表或单元格容易引发时序冲突，改为直接引用对象：
用Set ws = ThisWorkbook.Worksheets("LIST_OF_TABLE")替代Select操作，后续通过ws引用工作表。

修正后的完整代码 #

Sub DisplayListTable(v_TableName As String, v_Filename As String)
    ' 删除已存在的查询
    On Error Resume Next
    ThisWorkbook.Queries(v_TableName).Delete
    On Error GoTo 0
    
    ' 创建PowerQuery查询
    Dim pqFormula As String
    pqFormula = "let" & vbCrLf & _
                "    Source = Pdf.Tables(File.Contents(""""" & v_Filename & """""), [Implementation=""1.3""])" & vbCrLf & _
                "in" & vbCrLf & _
                "    Source"
    ThisWorkbook.Queries.Add Name:=v_TableName, Formula:=pqFormula
    
    ' 直接引用目标工作表，避免Select/Activate
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("LIST_OF_TABLE")
    
    ' 添加ListObject并刷新数据
    With ws.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""" & v_TableName & """;Extended Properties=""""", _
        Destination:=ws.Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & v_TableName & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.Name = v_TableName
        .Refresh
        ' 等待刷新完成
        Do While .Refreshing
            DoEvents
        Loop
    End With
End Sub

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