VBA结合PowerQuery循环导入.out文件到Excel无数据问题求助

代码存在的3个核心错误 #

• 错误1：Power Query公式内未正确拼接路径变量，你将变量chemin直接写在了引号内部，被识别为固定字符串而非动态路径，同时未补全.out文件后缀，导致找不到目标文件
• 错误2：OLEDB连接字符串中的查询名称参数硬编码，Location=querie中的querie被识别为固定字符串，无法匹配你循环生成的quera1/quera2等动态查询名
• 错误3：查询加载命令逻辑存在冲突，你同时设置了.BackgroundQuery = True和.Refresh BackgroundQuery:=False，参数冲突会导致刷新异常

修正后的完整代码 #

Sub BONJOUR()
    Dim chemin As String
    Dim querie As String
    Dim i As Integer
    ' 循环可根据实际文件数量修改上限
    For i = 1 To 3
        ' 补全.out文件后缀，若你的文件无后缀可删除& ".out"部分
        chemin = "C:\Users\matth\Desktop\OUTPUT FILES\Compr" & i & ".out"
        querie = "quera" & i
        ' 拼接动态路径到Power Query公式
        ActiveWorkbook.Queries.Add Name:=querie, Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""" & chemin & """),[Delimiter="","", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Kept Range of Rows"" = Table.Range(#""Changed Type"",2109" & _
            ",20)," & Chr(13) & "" & Chr(10) & "    #""Split Column by Delimiter"" = Table.SplitColumn(#""Kept Range of Rows"", ""Column1"", Splitter.SplitTextByDelimiter("" * "", QuoteStyle.Csv), {""Column1.1"", ""Column1.2"", ""Column1.3"", ""Column1.4"", ""Column1.5"", ""Column1.6""})," & Chr(13) & "" & Chr(10) & "    #""Changed Type1"" = Table.TransformColumnTypes(#""Split Column by Delimiter"",{{""Column1.1"", type text}, {""Col" & _
            "umn1.2"", type text}, {""Column1.3"", type text}, {""Column1.4"", type text}, {""Column1.5"", type text}, {""Column1.6"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Removed Columns"" = Table.RemoveColumns(#""Changed Type1"",{""Column1.1""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Removed Columns"""
        ActiveWorkbook.Worksheets.Add
        ' 拼接动态查询名到连接字符串
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & querie & ";Extended Properties=""""" _
            , Destination:=Range("$A$1")).QueryTable
            .CommandType = xlCmdSql
            .CommandText = Array("SELECT * FROM [" & querie & "]")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = querie
            .Refresh BackgroundQuery:=False
        End With
        Range("C27").Select
        ' 若不需要保留生成的Power Query查询，可取消注释下方代码
        ' ActiveWorkbook.Queries(querie).Delete
    Next
End Sub

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