合并Excel文件

利用VBA宏合并多个 xml 文件，用来处理 outreg2 生成的文件 整理自 知乎专栏 - VBA 合并工作簿

Sub 合并多个工作簿()
    Application.DisplayAlerts = False   '不显示提示框'
    '----------------------------------------------------------''
    Dim CheckSheet As Worksheet
    For Each CheckSheet In ThisWorkbook.Sheets
        If Left(CheckSheet.Name, 5) = "Copy_" Then
            CheckSheet.Delete
        End If
    Next                            '删除历史拷贝表
    '----------------------------------------------------------'
    Dim F As FileDialog
    Set F = Application.FileDialog(msoFileDialogFilePicker)
    F.Filters.Clear
	'F.Filters.Add "Select Excel", "*.xls;*.xlsx;*.xml' 
	F.Filters.Add "Select Excel", "*.xls;*.xlsx;*.xml" ' 增加 xml 格式'
    F.Show                          '启动一个Excel文件选择对话框
    '----------------------------------------------------------'
    Dim filePath As Variant         '选择的每一个文件的路径'
    Dim excel As Workbook           '自动打开的工作簿'
    Dim sheet As Worksheet          '自动选择的源工作表'
    Dim AddSheet As Worksheet       '新增的拷贝表'
    '----------------------------------------------------------'
    For Each filePath In F.SelectedItems
        Set excel = Workbooks.Open(filePath) '打开当前所选路径'
        Dim ExcelName As Variant
        ExcelName = Split(excel.Name, ".")
        ExcelName = ExcelName(0)
        '----------------------------------------------------------'
        For Each sheet In excel.Sheets
            sheet.Cells.Copy
            Set NewSheet = ThisWorkbook.Sheets.Add '新增拷贝表'
            NewSheet.Cells.PasteSpecial
			'NewSheet.Name = "Copy_" & ExcelName & "_" & sheet.Name ''新增表命名 以Copy开头'
            NewSheet.Name = ExcelName '修改 新增表名 原始文档名字'
        Next           'for each 获取目标工作簿内每一个sheet表'
        '- - - - - - - - - - - - - - - - - - - - - - - - - - -'
        excel.Close False
    Next               'For Each 语句获取选择的每一个文件的路径'
    '----------------------------------------------------------'
    Application.DisplayAlerts = True   '恢复显示提示框'
End Sub