Excel宏导入PDF至Excel：替换固定路径为变量报错解决

我录制宏实现了将PDF数据导入Excel的功能，代码可正常运行，但尝试把代码中的固定PDF路径替换为变量pathFile时，出现错误提示：“提供的文件路径必须是有效的绝对路径”。不清楚如何处理字符串中的双引号，尝试多种修改方式后，要么出现语法错误，要么仍报路径错误，希望修改代码以使用pathFile变量作为PDF路径。

原代码：

Sub test()

Dim file As String
pathFile = "C:\Users\Documents\Sample.pdf"

ActiveWorkbook.Queries.Add Name:="Page001 (2)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Pdf.Tables(File.Contents(""**C:\Documents\Sample.pdf**""), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & "    Page1 = Source{[Id=""Page001""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Page1, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Heade"" & _
        "rs"",{{""Column1"", Int64.Type}, {""DocuSign Envelope ID: 3A4A0FCB-6371-49E0-B015-D8F6EFD7CC8F"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type text}, {""Column11"", type text}, {""Column12"", type te" & _
        "xt}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type date}})" & 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=""Page001 (2)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Page001 (2)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Page001__2"
        .Refresh BackgroundQuery:=False
    End With
End Sub

解决方案 #

核心问题是VBA字符串的双引号转义规则：在VBA字符串里要表示一个实际的双引号，需要用两个连续的双引号。原代码中固定路径用""C:\Documents\Sample.pdf""的形式（外层是VBA字符串的双引号，内层两个双引号转义成一个），替换为变量时，需将pathFile插入到字符串拼接中，格式为""" & pathFile & """，这样最终生成的M公式里会包含正确的带双引号的绝对路径。

修改后的完整代码：

Sub test()
    Dim pathFile As String ' 显式声明变量，避免隐式类型问题
    pathFile = "C:\Users\Documents\Sample.pdf"

    ActiveWorkbook.Queries.Add Name:="Page001 (2)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Pdf.Tables(File.Contents(""" & pathFile & """), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & _
        "    Page1 = Source{[Id=""Page001""]}[Data]," & Chr(13) & "" & Chr(10) & _
        "    #""Promoted Headers"" = Table.PromoteHeaders(Page1, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & _
        "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Column1"", Int64.Type}, {""DocuSign Envelope ID: 3A4A0FCB-6371-49E0-B015-D8F6EFD7CC8F"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type text}, {""Column11"", type text}, {""Column12"", type text}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type date}})" & 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=""Page001 (2)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Page001 (2)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Page001__2"
        .Refresh BackgroundQuery:=False
    End With
End Sub

额外优化：将原代码中拆分的字符串（如#""Promoted Heade"" & _"rs"""）合并为完整的#""Promoted Headers""，提升代码可读性；同时显式声明pathFile变量，避免隐式类型带来的潜在问题。

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