Excel多个文件合并--结构相同

1. 新建一个Excel文档并打开
2. 在Sheet1中A1，A2，A3...依次填入要合并的Excel文件地址，比如D:\11.xls，E:\22.xlsx...
3. 按下快捷键Alt+F11打开VBA宏编辑器
4. 插入->模块，在新建的“模块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文件。