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

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

利用Excel VBA解决多个结构相同的文件的合并。

新建一个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文件。