如何使用VBA代码合并多个Excel文件

您可以使用以下VBA代码将多个Excel文件合并为一个文件：

Sub MergeExcelFiles()
    Dim MyPath As String, FilesInPath As String
    Dim MyFiles() As String
    Dim SourceR As Range, DestR As Range
    Dim wbSource As Workbook, wbDest As Workbook
    Dim wsSource As Worksheet, wsDest As Worksheet
    Dim FileCounter As Integer, n As Integer
    Dim LastRow As Long
    
    ' 设置源文件夹路径
    MyPath = "C:\Path\To\Your\Folder\"
    
    ' 获取文件夹中的所有文件
    FilesInPath = Dir(MyPath & "*.xlsx")
    
    ' 确定文件计数器的初始值
    FileCounter = 1
    
    ' 将所有文件存储在数组中
    Do While FilesInPath <> ""
        ReDim Preserve MyFiles(1 To FileCounter)
        MyFiles(FileCounter) = FilesInPath
        FileCounter = FileCounter + 1
        FilesInPath = Dir()
    Loop
    
    ' 创建新工作簿作为目标工作簿
    Set wbDest = Workbooks.Add
    
    ' 循环遍历所有文件
    For n = 1 To FileCounter - 1
        ' 打开源文件
        Set wbSource = Workbooks.Open(MyPath & MyFiles(n))
        
        ' 设置源工作表
        Set wsSource = wbSource.Worksheets(1)
        
        ' 确定源工作表中的最后一行
        LastRow = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row
        
        ' 设置源范围
        Set SourceR = wsSource.Range("A1:Z" & LastRow)
        
        ' 设置目标工作表
        Set wsDest = wbDest.Worksheets(1)
        
        ' 确定目标工作表中的最后一行
        LastRow = wsDest.Cells(wsDest.Rows.Count, 1).End(xlUp).Row + 1
        
        ' 设置目标范围
        Set DestR = wsDest.Range("A" & LastRow)
        
        ' 复制源范围到目标范围
        SourceR.Copy DestR
        
        ' 关闭源工作簿
        wbSource.Close SaveChanges:=False
    Next n
    
    ' 保存目标工作簿
    wbDest.SaveAs MyPath & "MergedFile.xlsx"
    
    ' 关闭目标工作簿
    wbDest.Close SaveChanges:=False
    
    ' 清除对象变量
    Set wsSource = Nothing
    Set wsDest = Nothing
    Set wbSource = Nothing
    Set wbDest = Nothing
    
    MsgBox "合并完成！"
End Sub

请根据您的需求修改代码中的文件夹路径和文件类型（例如，*.xlsx）。

运行这个宏后，它将从给定的文件夹中获取所有Excel文件并将它们合并到一个名为"MergedFile.xlsx"的新工作簿中