使用VBA变量定义Power Query数据源时遇到的问题

问题背景 #

需要从每页包含2个表格的PDF中，提取每页的第2个表格（对应Table002、Table004、Table006…这类命名）。用户输入PDF页数后通过循环批量提取，但将代码中固定的Table002替换为变量Table_No后，首次循环正常，后续无法提取新表格且报错。

原代码 #

Dim Table_Max As Integer
Dim Ack_Pages As Integer
Dim Table_No As String

Ack_Pages = InputBox("Enter the number of pages on the acknowledgement file.")
Table_Max = Ack_Pages * 2

For i = 2 To Table_Max Step 2
    Table_No = "Table00" & i
        ActiveWorkbook.Queries.Add Name:=Table_No, Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    Source = Pdf.Tables(File.Contents(""" & PDFPath & """), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & "    Table002 = Source{[Id=""Table002""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Table002,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""

解决方法 #

问题核心是只替换了查询名称的变量，没替换Power Query公式里硬编码的表格ID，同时需要注意VBA字符串拼接的转义规则，以下是修正步骤：

1. 替换公式中所有硬编码的Table002
   原代码的Formula里有两处Table002：一处是表格变量名，一处是Source{[Id="Table002"]}里的ID值，这两处都要替换为变量Table_No，否则每次循环还是在提取Table002。

2. 正确处理VBA字符串的双引号转义
   在VBA中，要在字符串里输出双引号，需要用两个双引号""来转义，所以公式里的ID部分要写成[Id=""" & Table_No & """]。

3. 可选：避免重复创建查询报错
   循环前可以检查是否已存在同名查询，若存在则先删除，防止重复创建导致报错。

修正后的完整代码 #

Dim Table_Max As Integer
Dim Ack_Pages As Integer
Dim Table_No As String
Dim i As Integer
Dim qry As WorkbookQuery

Ack_Pages = InputBox("请输入确认文件的页数。")
Table_Max = Ack_Pages * 2

For i = 2 To Table_Max Step 2
    Table_No = "Table00" & i
    
    ' 先检查是否已有同名查询，有则删除
    On Error Resume Next
    Set qry = ActiveWorkbook.Queries(Table_No)
    If Err.Number = 0 Then
        qry.Delete
    End If
    On Error GoTo 0
    
    ' 创建新查询，替换所有Table002为变量Table_No
    ActiveWorkbook.Queries.Add Name:=Table_No, Formula:= _
        "let" & Chr(13) & "" & Chr(10) & _
        "    Source = Pdf.Tables(File.Contents(""" & PDFPath & """), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & _
        "    " & Table_No & " = Source{[Id=""" & Table_No & """]}[Data]," & Chr(13) & "" & Chr(10) & _
        "    #""Changed Type"" = Table.TransformColumnTypes(" & Table_No & ",{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}})" & Chr(13) & "" & Chr(10) & _
        "in" & Chr(13) & "" & Chr(10) & _
        "    #""Changed Type"""
Next i

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