1.如何合并多个excel文件

如何合并多个excel文件！有一个文件夹下有很多个excel文件，每个excel里面只有一个表。怎么将这些表合并到一个EXCEL文件中常见问题,执行下面的宏Subhuizhong()DimfdAsFileDialogDimWbookAsWorkbookDimrowindexAsIntegerSetfd=Application.FileDialog(msoFileDialogFilePicker)DimvrtSelectedItemAsVariantWithfd.Filters.AddEXCEL文件,*.xls,1'过滤If.Show=-1Thenrowindex=1ForEachvrtSelectedItemIn.SelectedItemsSetWbook=Workbooks.Open(vrtSelectedItem)subrowindex=1'每个表从第一行开始DoWhileWbook.Worksheets(Sheet1).Cells(subrowindex,1)'假设被汇总的excel文件名为综合表,表名为sheet1.Workbooks(综合表.xls).Worksheets(Sheet1).Cells(rowindex,1)=Wbook.Worksheets(Sheet1).Cells(subrowindex,1).Value'以下是对每一行进行赋值Workbooks(综合表.xls).Worksheets(Sheet1).Cells(rowindex,2)=Wbook.Worksheets(Sheet1).Cells(subrowindex,2).Valuesubrowindex=subrowindex+1rowindex=rowindex+1LoopWbook.CloseNextvrtSelectedItemElseEndIfEndWithSetfd=NothingEndSub运行后再选择要合并的文档，Subaa()DimfdAsFileDialogDimWbookAsWorkbookDimrowindexAsIntegerSetfd=Application.FileDialog(msoFileDialogFilePicker)DimvrtSelectedItemAsVariantWithfd.Filters.AddEXCEL文件,*.xls,1'过滤If.Show=-1Thenrowindex=1ForEachvrtSelectedItemIn.SelectedItemsSetWbook=Workbooks.Open(vrtSelectedItem)subrowindex=1'每个表从第一行开始DoWhileWbook.Worksheets(Sheet1).Cells(subrowindex,1)'假设被汇总的excel文件名为新建MicrosoftExcel工作表,表名为sheet1.Workbooks(新建MicrosoftExcel工作表.xls).Worksheets(Sheet1).Cells(rowindex,1)=Wbook.Worksheets(Sheet1).Cells(subrowindex,1).Value'以下是对每一行进行赋值Workbooks(新建MicrosoftExcel工作表.xls).Worksheets(Sheet1).Cells(rowindex,2)=Wbook.Worksheets(Sheet1).Cells(subrowindex,2).ValueWorkbooks(新建MicrosoftExcel工作表.xls).Worksheets(Sheet1).Cells(rowindex,3)=Wbook.Worksheets(Sheet1).Cells(subrowindex,3).ValueWorkbooks(新建MicrosoftExcel工作表.xls).Worksheets(Sheet1).Cells(rowindex,4)=Wbook.Worksheets(Sheet1).Cells(subrowindex,4).ValueWorkbooks(新建MicrosoftExcel工作表.xls).Worksheets(Sheet1).Cells(rowindex,5)=Wbook.Worksheets(Sheet1).Cells(subrowindex,5).ValueWorkbooks(新建MicrosoftExcel工作表.xls).Worksheets(Sheet1).Cells(rowindex,6)=Wbook.Worksheets(Sheet1).Cells(subrowindex,6).ValueWorkbooks(新建MicrosoftExcel工作表.xls).Worksheets(Sheet1).Cells(rowindex,7)=Wbook.Worksheets(Sheet1).Cells(subrowindex,7).ValueWorkbooks(新建MicrosoftExcel工作表.xls).Worksheets(Sheet1).Cells(rowindex,8)=Wbook.Worksheets(Sheet1).Cells(subrowindex,8).ValueWorkbooks(新建MicrosoftExcel工作表.xls).Worksheets(Sheet1).Cells(rowindex,9)=Wbook.Worksheets(Sheet1).Cells(subrowindex,9).Valuesubrowindex=subrowindex+1rowindex=rowindex+1LoopWbook.CloseNextvrtSelectedItemElseEndIfEndWithSetfd=NothingEndSub通过VBA宏合并Excel工作表今天火车票到手，最重要的事情搞定啦，庆祝一下~昨天跟盼盼说要写一篇她看得懂的，小路从来都是言而有信的人~想了半天，对于Excel，我只会玩宏，所以有了这一篇日志~咳咳，切入正题。工作中经常会用到的把几个Excel文件合并到一个，或者是把一个Excel文件里的所有Sheet合并到一个Sheet来进行统计。下面分别提供用vba宏来解决这两个问题的方法~1.合并Excel文件打开一个空Excel文件，Alt+F11，插入一个模块，开始写代码吧：查看源码打印关于SubMergeWorkbooks()DimFileSetDimiAsIntegerOnErrorGoTo0Application.ScreenUpdating=FalseFileSet=Application.GetOpenFilename(FileFilter:=Excel2003(*.xls),*.xls,Excel2007(*.xlsx),*.xlsx,_MultiSelect:=True,Title:=选择要合并的文件)IfTypeName(FileSet)=BooleanThenGoToExitSubEndIfForEachFilenameInFileSetWorkbooks.OpenFilenameSheets().MoveAfter:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)NextExitSub:Application.ScreenUpdating=TrueEndSub这段代码在干嘛？它首先打开一个文件选择框，你可以选择一个或多个文件，然后把这些文件里的所有Sheet合并到当前这个工作簿里来，有重名的Sheet会自动在后面加数字。嗯，接下来可以进行第二歩鸟~2.合并工作表同上，再添加一个模块吧，代码如下查看源码打印关于FunctionLastRow(shAsWorksheet)OnErrorResumeNextLastRow=sh.Cells.Find(what:=*,_After:=sh.Range(A1),_Lookat:=xlPart,_LookIn:=xlFormulas,_SearchOrder:=xlByRows,_SearchDirection:=xlPrevious,_MatchCase:=False).RowOnErrorGoTo0EndFunctionSubMergeSheets()DimshAsWorksheetDimDestShAsWorksheetDimLastAsLongDimshLastAsLongDimCopyRngAsRangeDimStartRowAsLongApplication.ScreenUpdating=FalseApplication.EnableEvents=False'新建一个“汇总”工作表Application.DisplayAlerts=FalseOnErrorResumeNextActiveWorkbook.Worksheets(汇总).DeleteOnErrorGoTo0Application.DisplayAlerts=TrueSetDestSh=ActiveWorkbook.Worksheets.AddDestSh.Name=汇总'开始复制的行号，忽略表头，无表头请设置成1StartRow=2ForEachshInActiveWorkbook.WorksheetsIfsh.NameDestSh.NameThenLast=LastRow(DestSh)shLast=LastRow(sh)IfshLast0AndshLast=StartRowThenSetCopyRng=sh.Range(sh.Rows(StartRow),sh.Rows(shLast))IfLast+CopyRng.Rows.CountDestSh.Rows.CountThenMsgBox内容太多放不下啦！GoToExitSubEndIfCopyRng.CopyWithDestSh.Cells(Last+1,A).PasteSpecialxlPasteValues.PasteSpecialxlPasteFormatsApplication.CutCopyMode=FalseEndWithEndIfEndIfNextExitSub:Application.GoToDestSh.Cells(1)DestSh.Columns.AutoFitApplication.ScreenUpdating=TrueApplication.EnableEvents=TrueEndSub这一大坨又在干嘛？它会新建一个叫做“汇总”的工作表，然后把当前工作簿里的所有Sheet里有数据的内容都复制到“汇总”表里。提示：如果数据表里的内容没有表头的话需要把StartRow=2改成StartRow=1哦。Alt+F11插入一个模块，先运行MergeWorkbooks,再运行MergeSheets.可解决每个Excel表中sheet1的命名是不一样时的情况。SubMergeWorkbooks()DimFileSetDimiAsIntegerOnErrorGoTo0Application.ScreenUpdating=FalseFileSet=Application.GetOpenFilename(FileFilter:=Excel2003(*.xls),*.xls,Excel2007(*.xlsx),*.xlsx,_MultiSelect:=True,Title:=选择要合并的文件)IfTypeName(FileSet)=BooleanThenGoToExitSubEndIfForEachFilenameInFileSetWorkbooks.OpenFilenameSheets().MoveAfter:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)NextExitSub:Application