如何在ActiveWorkbook.Queries.Add中更新Source ID参数

问题根源 #

你遇到的错误是因为Power Query公式字符串的引号拼接错误，导致实际传递给Source{[Id=...]}的不是正确的表名变量值，引号格式混乱使得Power Query无法找到对应的表。

正确的字符串拼接方式 #

在VBA中，要在字符串里嵌入变量并保留Power Query所需的双引号，必须正确转义：

• 原固定写法：Source{[Id=""Table001""]}
• 替换为变量时，需将Table001换成TableOnly变量，同时保持双引号转义："Source{[Id=""" & TableOnly & """]}[Data]"

逻辑说明：

• VBA中用""表示一个实际的双引号
• 拼接时"""会在最终的Power Query公式中输出一个"，中间插入变量TableOnly，再用"""闭合引号

修改后的完整代码 #

For i = 1 To 3

    TableName = "Table00" & i & " (Page " & i + 2 & ")" 'Example: "Table001 (Page 3)"
    DispName = "Table00" & i & "__Page_" & i + 2 & "" 'Example: "Table001__Page_3"
    TableOnly = "Table00" & i 'Example: Table001

    ActiveWorkbook.Queries.Add Name:=TableName, Formula:= _
        "let" & Chr(13) & "" & Chr(10) & _
        "    Source = Pdf.Tables(File.Contents(""C:\Sample.pdf""), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & _
        "    " & TableOnly & " = Source{[Id=""" & TableOnly & """]}[Data]," & Chr(13) & "" & Chr(10) & _
        "    #""Changed Type"" = Table.TransformColumnTypes(" & TableOnly & ",{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", " & _
        "type text}, {""Column6"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & TableName & ";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & TableName & "]")
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SaveData = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = DispName
        .Refresh BackgroundQuery:=False
    End With

Next

额外检查点 #

• 确认PDF文件C:\Sample.pdf中确实存在Table001、Table002、Table003这三个可导出的表，表名拼写需完全一致（区分大小写）
• 可手动在Power Query编辑器中测试单个表的获取逻辑，验证表名有效性

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