如何把几个Excel文件合并在一起

如何把几个Excel文件合并在一起####

几个Excel文件内的数据格式都差不多，现在我们需要把它们合并在一个Excel表格当中。

1. 新建一个Excel文件
2. 右键单击Sheet1，选择“查看代码”
3. 在对话框中输入以下代码
   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
4. 点击“运行”→点击“运行子过程／用户窗体”
5. 等待即可