如何修改VBA宏，用For循环批量导入Folders工作表指定的Excel文件

以下是修改后的VBA代码，可实现通过循环读取"Folders"工作表A列的文件名，批量导入对应Excel文件的数据：

Dim numFolders As Integer
Dim folderPosition As Integer
Dim folderName As String

numFolders = 10

For folderPosition = 1 To numFolders
    folderName = Sheets("Folders").Range("A" & folderPosition).Value
    
    ' 动态创建Power Query，使用当前循环的文件名
    ActiveWorkbook.Queries.Add Name:=folderName, Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""C:\Location\" & folderName & ".XLS""), null, true)," & Chr(13) & "" & Chr(10) & "    #" & Chr(34) & folderName & Chr(34) & "1 = Source{[Name=""" & folderName & """]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(#" & Chr(34) & folderName & Chr(34) & "1, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Name"", type text}, {""Type""" & _
        ", type text}, {""Description"", type text}, {""Format"", type text}, {""Created"", type date}, {""Records"", type text}, {""List?"", type logical}, {""Created by"", type text}, {""Last run"", type date}, {""Last changed"", 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=" & folderName & ";Extended Properties="""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & folderName & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "_" & folderName
        .Refresh BackgroundQuery:=False
    End With
    Application.CommandBars("Queries and Connections").Visible = False
Next

关键修改说明 #

• 动态查询命名：将原固定的查询名称TEST_FILE替换为变量folderName，避免重复创建查询时触发命名冲突错误
• 文件路径动态化：把硬编码的文件路径"C:\Location\TEST FILE.XLS"修改为"C:\Location\" & folderName & ".XLS"，实现每次循环读取对应文件名的Excel文件
• 工作表引用适配：Power Query中指定的工作表名称TEST_FILE替换为folderName，确保读取目标文件内的对应工作表（假设每个Excel文件内的目标工作表名与文件名一致）
• 查询表关联更新：修改QueryTable的数据源位置、SQL查询语句以及列表对象的显示名称，使其与当前循环的查询名称保持一致，保证数据正确导入

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