遍历文件夹中FILE格式文件导入Excel的VBA代码报错求助

问题根源 #

1. Power Query公式中使用了通配符\Users\Documents\LoadFiles\ABC *，这种写法会被识别为非法路径字符，直接触发1004错误
2. Dir函数未使用完整绝对路径，且未明确匹配无扩展名的目标文件
3. 循环过程中未将当前遍历到的具体文件名传递给Power Query的数据源

修正后的完整代码 #

Sub LoopAllFilesInAFolder()
    'Loop through all files in a folder
    Dim FileName As Variant
    Dim folderPath As String
    
    '替换为你的实际文件夹绝对路径
    folderPath = "C:\Users\YourUserName\Documents\LoadFiles\"
    
    '匹配文件夹下所有以ABC开头的无扩展名文件
    FileName = Dir(folderPath & "ABC*", vbNormal)

    While FileName <> ""
        '避免删除不存在的查询导致报错
        On Error Resume Next
        ActiveWorkbook.Queries("IMPORT").Delete
        On Error GoTo 0
        
        '拼接当前文件的完整路径
        Dim fullFilePath As String
        fullFilePath = folderPath & FileName
        
        '创建Power Query查询，传入当前文件的具体路径
        ActiveWorkbook.Queries.Add Name:="IMPORT", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "  Source = Csv.Document(File.Contents(""" & fullFilePath & """),[Delimiter=""|"", Columns=30, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & _
        "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", 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 text}, {""Column17"", type text}, {""Column18"", type text}, {""Column19"", type text}, {""Column20"", type text}, {""Column21"", type text}, {""Column22"", type text}, {""Column23"", type text}, {""Column24"", type text}, {""Column25"", Int64.Type}, {""Column26"", type number}, {""Column27"", type date}, {""Column28"", Int64.Type}, {""Column29"", type text}, {""Column30"", 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=IMPORT;Extended Properties="""""" _
        , Destination:=Range("$A$1")).QueryTable
            .CommandType = xlCmdSql
            .CommandText = Array("SELECT * FROM [IMPORT]")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = "IMPORT"
            .Refresh BackgroundQuery:=False
        End With
        
        '打印当前处理的文件名到立即窗口
        Debug.Print FileName

        '获取下一个待处理文件
        FileName = Dir
    Wend
End Sub

关键修改说明 #

• 使用完整绝对路径：定义folderPath变量存储文件夹的完整路径，避免相对路径导致的识别误差
• 精准匹配目标文件：通过Dir(folderPath & "ABC*", vbNormal)匹配所有以ABC开头的无扩展名文件
• 动态传递文件路径：将当前遍历到的文件名拼接成完整路径，代入Power Query的File.Contents参数，彻底替换原有的通配符写法
• 添加错误防护：删除查询前加入错误捕获，避免因查询不存在导致的中断
• 修正语法错误：移除原代码中类型转换语句里的非法参数filename，保证Power Query公式语法正确

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