![如何将多个 Excel 文件合并为一个文件](https://linux22.com/image/1565341/%E5%A6%82%E4%BD%95%E5%B0%86%E5%A4%9A%E4%B8%AA%20Excel%20%E6%96%87%E4%BB%B6%E5%90%88%E5%B9%B6%E4%B8%BA%E4%B8%80%E4%B8%AA%E6%96%87%E4%BB%B6.png)
我有 3 个 excel 文件,每个文件都有多个选项卡,我需要将每个选项卡复制到一个 excel 文件中,以便我可以按特定顺序对它们进行排序。每个选项卡都有不同的名称(最终大约有 130 个选项卡)。我尝试了下面的代码,它改变了我的图表格式并在空白行上添加了零。
Sub MergeExcelFiles()
Dim fnameList, fnameCurFile As Variant
Dim countFiles, countSheets As Integer
Dim wksCurSheet As Worksheet
Dim wbkCurBook, wbkSrcBook As Workbook
fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)
If (vbBoolean <> VarType(fnameList)) Then
If (UBound(fnameList) > 0) Then
countFiles = 0
countSheets = 0
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set wbkCurBook = ActiveWorkbook
For Each fnameCurFile In fnameList
countFiles = countFiles + 1
Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)
For Each wksCurSheet In wbkSrcBook.Sheets
countSheets = countSheets + 1
wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
Next
wbkSrcBook.Close SaveChanges:=False
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Procesed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
End If
Else
MsgBox "No files selected", Title:="Merge Excel files"
End If
End Sub
是否有不同的代码或更好的方法来尝试将它们放在一个文件中?
我有一个宏,可以按照我需要的顺序对选项卡进行排序,但是当我将它们复制到一个工作簿中时,我无法改变选项卡的格式。