如何合并多个excel文件！
有一个文件夹下有很多个excel文件，每个excel里面只有一个表。怎么将这些表合并到一个EXCEL文件中
常见问题,执行下面的宏
Sub huizhong()
Dim fd As FileDialog
Dim Wbook As Workbook
Dim rowindex As Integer
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim vrtSelectedItem As Variant
With fd
.Filters.Add "EXCEL 文件", "*.xls", 1 '过滤
If .Show = -1 Then
rowindex = 1
For Each vrtSelectedItem In .SelectedItems
不离不弃什么意思Set Wbook = Workbooks.Open(vrtSelectedItem)
subrowindex = 1 '每个表从第一行开始
Do While Wbook.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).Value
subrowindex = subrowindex + 1
rowindex = rowindex + 1
Loop
Wbook.Clo
Next vrtSelectedItem
El
End If
End With
Set fd = Nothing
End Sub
成果的英文
运行后再选择要合并的文档，
Sub aa()
Dim fd As FileDialog
Dim Wbook As Workbook
Dim rowindex As Integer
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim vrtSelectedItem As Variant
With fd
.Filters.Add "EXCEL 文件", "*.xls", 1 '过滤
If .Show = -1 Then壁纸男生霸气冷酷
rowindex = 1
For Each vrtSelectedItem In .SelectedItems
Set Wbook = Workbooks.Open(vrtSelectedItem)
subrowindex = 1 '每个表从第一行开始
Do While Wbook.Worksheets("Sheet1").Cells(subrowindex, 1) <> ""
'假设被汇总的excel文件名为 "新建 Microsoft Excel 工作表",表名为sheet1.
Workbooks("新建 Microsoft Excel 工作表.xls").Worksheets("Sheet1").Cells(rowindex, 1) = Wbook.Worksheets("Sheet1").Cells(subrowindex, 1).Value '以下是对每一行进行赋值
Workbooks("新建 Microsoft Excel 工作表.xls").Worksheets("Sheet1").Cells(rowindex, 2) = Wbook.Worksheets("Sheet1").Cells(subrowindex, 2).Value
Workbooks("新建 Microsoft Excel 工作表.xls").Worksheets("Sheet1").Cells(rowindex, 3) = Wbook.Worksheets("Sheet1").Cells(subrowindex, 3).Value
Workbooks("新建 Microsoft Excel 工作表.xls").Worksheets("Sheet1").Cells(rowindex, 4) = Wbook.Worksheets("Sheet1").Cells(subrowindex, 4).Value
Workbooks("新建 Microsoft Excel 工作表.xls").Worksheets("Sheet1").Cells(rowindex, 5) = Wbook.Worksheets("Sheet1").Cells(subrowindex, 5).Value
北师大文学院
良加偏旁组成新字Workbooks("新建 Microsoft Excel 工作表.xls").Worksheets("Sheet1").Cells(rowindex, 6) = Wbook.Worksheets("Sheet1").Cells(subrowindex, 6).Value
Workbooks("新建 Microsoft Excel 工作表.xls").Worksheets("Sheet1").Cells(rowindex, 7) = Wbook.Worksheets("Sheet1").Cells(subrowindex, 7).Value
Workbooks("新建 Microsoft Excel 工作表.xls").Worksheets("Sheet1").Cells(rowindex, 8) = Wbook.Worksheets("Sheet1").Cells(subrowindex, 8).Value
Workbooks("新建 Microsoft Excel 工作表.xls").Worksheets("Sheet1").Cells(rowindex, 9) = Wbook.Worksheets("Sheet1").Cells(subrowindex, 9).Value
subrowindex = subrowindex + 1
rowindex = rowindex + 1
Loop
Wbook.Clo
举行升旗仪式>景观平面图
Next vrtSelectedItem
El
End If
End With
Set fd = Nothing
End Sub
通过VBA宏合并Excel工作表
今天火车票到手，最重要的事情搞定啦，庆祝一下~
昨天跟盼盼说要写一篇她看得懂的，小路从来都是言而有信的人~想了半天，对于Excel，我只会玩宏，所以有了这一篇日志~
咳咳，切入正题。工作中经常会用到的把几个Excel文件合并到一个，或者是把一个Excel文件里的所有Sheet合并到一个Sheet来进行统计。下面分别提供用vba宏来解决这两个问题的方法~
1. 合并Excel文件
打开一个空Excel文件，Alt+F11，插入一个模块，开始写代码吧：
查看源码
打印关于
2会 Sub MergeWorkbooks()
Dim FileSet
Dim i As Integer
On Error GoTo 0
Application.ScreenUpdating = Fal
FileSet = Application.GetOpenFilename(FileFilter:="Excel 2003(*.xls),*.xls,Excel 2007(*.xlsx),*.xlsx", _
MultiSelect:=True, Title:="选择要合并的文件")
If TypeName(FileSet) = "Boolean" Then
GoTo ExitSub
End If
For Each Filename In FileSet
Workbooks.Open Filename
Sheets().Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next
ExitSub:
Application.ScreenUpdating = True
End Sub
这段代码在干嘛？它首先打开一个文件选择框，你可以选择一个或多个文件，然后把这些文件里的所有Sheet合并到当前这个工作簿里来，有重名的Sheet会自动在后面加数字。嗯，接下来可以进行第二歩鸟~
2. 合并工作表
同上，再添加一个模块吧，代码如下
查看源码
打印关于
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(what:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
