使用VBA将多个Excel工作簿从特定文件夹导入到单个工作表中

Sub ImportWorkbooks()
    Dim FolderPath As String
    Dim FileName As String
    Dim SourceWorkbook As Workbook
    Dim TargetWorkbook As Workbook
    Dim SourceWorksheet As Worksheet
    Dim TargetWorksheet As Worksheet
    Dim RowCount As Long
    
    ' 设置目标工作簿
    Set TargetWorkbook = ThisWorkbook
    Set TargetWorksheet = TargetWorkbook.Worksheets(1)
    
    ' 设置源文件夹路径
    FolderPath = "C:\YourFolderPath\" ' 替换为你的文件夹路径
    
    ' 获取源文件夹中的所有文件名
    FileName = Dir(FolderPath & "*.xls*")
    
    ' 循环处理每个源工作簿
    Do While FileName <> ""
        ' 打开源工作簿
        Set SourceWorkbook = Workbooks.Open(FolderPath & FileName)
        
        ' 复制源工作簿的第一个工作表到目标工作表中的下一行
        Set SourceWorksheet = SourceWorkbook.Worksheets(1)
        RowCount = TargetWorksheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
        SourceWorksheet.UsedRange.Copy TargetWorksheet.Cells(RowCount, 1)
        
        ' 关闭源工作簿
        SourceWorkbook.Close SaveChanges:=False
        
        ' 获取下一个文件名
        FileName = Dir
    Loop
    
    ' 清理对象变量
    Set TargetWorksheet = Nothing
    Set TargetWorkbook = Nothing
    Set SourceWorksheet = Nothing
    Set SourceWorkbook = Nothing
End Sub