如何把几个Excel文件合并在一起####
几个Excel文件内的数据格式都差不多,现在我们需要把它们合并在一个Excel表格当中。
- 新建一个Excel文件
- 右键单击Sheet1,选择“查看代码”
- 在对话框中输入以下代码
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 - 点击“运行”→点击“运行子过程/用户窗体”
- 等待即可