- 新建一个Excel文档并打开
- 在Sheet1中A1，A2，A3...依次填入要合并的Excel文件地址，比如D:\11.xls，E:\22.xlsx...
- 按下快捷键Alt+F11打开VBA宏编辑器
- 插入->模块，在新建的“模块1”下面输入以下代码
Public Sub addlist()
Dim curpath As String
Dim thiswb_name As String
Dim lj_rows As Long
Dim active_rows As Long
Dim active_cols As Long
thiswb_name = ThisWorkbook.Name
Workbooks(thiswb_name).Sheets(1).Activate
lj_rows = Sheets(1).UsedRange.Count
Dim ljarr()
Dim hzarr()
If lj_rows > 1 Then
ljarr = Range(Cells(1, 1), Cells(lj_rows, 1))
Dim currow As Long
currow = 1
Application.DisplayAlerts = False
For i = 1 To lj_rows
curpath = ljarr(i, 1)
If curpath <> "" And Dir(curpath) <> "" Then
Workbooks.Open (curpath)
active_rows = ActiveSheet.UsedRange.Rows.Count
active_cols = ActiveSheet.UsedRange.Columns.Count
Range(Cells(1, 1), Cells(active_rows, active_cols)).Copy
Workbooks(thiswb_name).Sheets(2).Activate
ActiveSheet.Cells(currow, 1).Select
ActiveSheet.Paste
Workbooks(2).Close False
currow = currow + active_rows
Else
If Dir(curpath) = "" Then
MsgBox curpath & "：此文件不存在！"
End If
End If
Next i
MsgBox "已完成！"
Application.DisplayAlerts = True
Else
MsgBox "请在Sheet1中第一列填入要合并的文件路径"
End If
End Sub
5.点击“运行”合并想要合并的多个Excel文件。
