VBA合并文件夹中的工作簿

Sub MergeWorkbooksInFolder()
    Dim FolderPath As String
    Dim FileName As String
    Dim TargetWorkbook As Workbook
    Dim SourceWorkbook As Workbook
    Dim SourceWorksheet As Worksheet
    
    ' 设置文件夹路径
    FolderPath = "C:\Folder\Path\"
    
    ' 创建目标工作簿
    Set TargetWorkbook = Workbooks.Add
    
    ' 遍历文件夹中的所有文件
    FileName = Dir(FolderPath & "*.xlsx")
    Do While FileName <> ""
        ' 打开源工作簿
        Set SourceWorkbook = Workbooks.Open(FolderPath & FileName)
        
        ' 复制源工作簿中的数据到目标工作簿
        For Each SourceWorksheet In SourceWorkbook.Worksheets
            SourceWorksheet.Copy After:=TargetWorkbook.Sheets(TargetWorkbook.Sheets.Count)
        Next SourceWorksheet
        
        ' 关闭源工作簿
        SourceWorkbook.Close SaveChanges:=False
        
        ' 获取下一个文件名
        FileName = Dir
    Loop
    
    ' 保存目标工作簿
    TargetWorkbook.SaveAs FolderPath & "MergedWorkbook.xlsx"
    
    ' 关闭目标工作簿
    TargetWorkbook.Close SaveChanges:=False
End Sub