Power Query加载CSV数据时查询引用失败问题求助

我尝试使用Power Query将CSV文件数据加载至Excel，查询名称设为当日文件名（已存入字符串变量LatestFileQuery）。问题出现在代码末尾的查询刷新环节，推测是在Location中无法找到之前定义的查询，请问应如何定义并引用该查询名称？

原VBA代码 #

ActiveWorkbook.Queries.Add Name:= _
        LatestFileQuery, Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""" & LatestFile & """),[Delimiter="""";""", Columns=34, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#" & _
        """Promoted Headers""",{{"""PORTFOLIO""", type text}, {"""INSTRUMENT""", type text}, {"""DEPOSIT""", type text}, {"""CONTRACT ID""", type text}, {"""INSTRUMENT NATURE""", type text}, {"""QUANTITY ATLAS""", type number}, {"""QUANTITY AAA""", type number}, {"""DELTA QUANTITY""", type number}, {"""NAV ATLAS""", type number}, {"""NAV AAA""", type number}, {"""PERCENTAGE NAV""", type number}, {" & _
        """DELTA NAV""", type number}, {"""PRICE ATLAS""", type number}, {"""DATE ATLAS""", type date}, {"""PRICE AAA""", type number}, {"""DATE AAA""", type date}, {"""DELTA PRICE""", type number}, {"""ACCRUED ATLAS""", type number}, {"""ACCRUED AAA""", type number}, {"""PERCENTAGE ACCRUED""", type number}, {"""DELTA ACCRUED""", type number}, {"""CURRENCY ATLAS""", type text}, {"""CURRENCY AAA"""" & _
        ", type text}, {"""DELTA CURRENCY""", type number}, {"""PMA ATLAS""", type number}, {"""PMA AAA""", type number}, {"""PERCENTAGE PMA""", type number}, {"""DELTA PMA""", type number}, {""" RM/PM""", type text}, {""" MGT TYPE""", type text}, {""" NAV PTF (ATL)""", type number}, {""" NAV PTF (PMS)""", type number}, {""" NAV TOT""", type number}, {""" %NAVPTF""", type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Ch" & _
        "anged Type"""
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""" & LatestFileQuery & """;" _
        , "Extended Properties="""""""), Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "SELECT * FROM [LatestFileQuery]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = _
        "" & LatestFileQuery & ""
        .Refresh BackgroundQuery:=False
    End With

错误提示 #

解决方案 #

问题根源是字符串拼接时的变量引用错误，以下是具体修正点：

1. 修正Location参数的变量拼接
   原代码中Location=""" & LatestFileQuery & """;的双层引号嵌套错误，应该直接将变量嵌入连接字符串，不需要额外转义：

"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & LatestFileQuery & ";"

2. 修正CommandText中的查询名称
   原代码把变量名LatestFileQuery当成了固定字符串，需要动态拼接查询名称：

"SELECT * FROM [" & LatestFileQuery & "]"

3. 简化ListObject.DisplayName赋值
   直接使用变量即可，无需多余的空字符串拼接：

.ListObject.DisplayName = LatestFileQuery

修正后的完整代码 #

ActiveWorkbook.Queries.Add Name:= _
        LatestFileQuery, Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""" & LatestFile & """),[Delimiter="""";""", Columns=34, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#" & _
        """Promoted Headers""",{{"""PORTFOLIO""", type text}, {"""INSTRUMENT""", type text}, {"""DEPOSIT""", type text}, {"""CONTRACT ID""", type text}, {"""INSTRUMENT NATURE""", type text}, {"""QUANTITY ATLAS""", type number}, {"""QUANTITY AAA""", type number}, {"""DELTA QUANTITY""", type number}, {"""NAV ATLAS""", type number}, {"""NAV AAA""", type number}, {"""PERCENTAGE NAV""", type number}, {" & _
        """DELTA NAV""", type number}, {"""PRICE ATLAS""", type number}, {"""DATE ATLAS""", type date}, {"""PRICE AAA""", type number}, {"""DATE AAA""", type date}, {"""DELTA PRICE""", type number}, {"""ACCRUED ATLAS""", type number}, {"""ACCRUED AAA""", type number}, {"""PERCENTAGE ACCRUED""", type number}, {"""DELTA ACCRUED""", type number}, {"""CURRENCY ATLAS""", type text}, {"""CURRENCY AAA"""" & _
        ", type text}, {"""DELTA CURRENCY""", type number}, {"""PMA ATLAS""", type number}, {"""PMA AAA""", type number}, {"""PERCENTAGE PMA""", type number}, {"""DELTA PMA""", type number}, {""" RM/PM""", type text}, {""" MGT TYPE""", type text}, {""" NAV PTF (ATL)""", type number}, {""" NAV PTF (PMS)""", type number}, {""" NAV TOT""", type number}, {""" %NAVPTF""", type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Ch" & _
        "anged Type"""
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & LatestFileQuery & ";" _
        , "Extended Properties="""""""), Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "SELECT * FROM [" & LatestFileQuery & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = LatestFileQuery
        .Refresh BackgroundQuery:=False
    End With

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