将多个Excel文件合并为一个(实测有效) | 您所在的位置:网站首页 › inverted怎么读 › 将多个Excel文件合并为一个(实测有效) |
将需要合并的Excel文件放在一个文件夹中,新建一个空白的Excel,然后按F11+Alt,打开 Visual Basic Editor。右击左侧栏中的 ThisWorkbook 并选择插入>模块。 将下列代码插入到Code window中,然后点击运行,就可以将多个Excel合并到一个Excel中的多个sheet中。 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 "Processed " & 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在此基础之上,创建一个新的excel,点击功能区中的“数据”>获取数据>自文件>从工作簿,选中上一步整合得到的Excel,选中文件夹而不是下面的任意一个sheet,点击编辑。 如果文章对你有用的话就给我点个赞吧~ |
CopyRight 2018-2019 实验室设备网 版权所有 |