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、5.点击“运行”合并想要合并的多个Excel文件。