合并多个excel文件

sub 合并当前目录下所有工作簿的全部工作表()

dim mypath, myname, awbname

dim wb as workbook, wbn as string

dim g as long

dim num as long

dim box as string

application.screenupdating = false

mypath = activeworkbook.path

myname = dir(mypath & "\" & "*.xls")

awbname = activeworkbook.name

num = 0

do while myname <> ""

if myname <> awbname then

set wb = workbooks.open(mypath & "\" & myname)

num = num + 1

with workbooks(1).activesheet

.cells(.range("a65536").end(xlup).row + 2, 1) = left(myname, len(myname) - 4)

for g = 1 to sheets.count

wb.sheets(g).usedrange.copy .cells(.range("a65536").end(xlup).row + 1, 1)

next

wbn = wbn & chr(13) & wb.name

wb.close false

end with

end if

myname = dir

loop

range("a1").select

application.screenupdating = true

msgbox "共合并了" & num & "个工作薄下的全部工作表。如下：" & chr(13) & wbn, vbinformation, "提示"

end sub