快速合并文件(2)
程序员文章站
2024-01-27 16:09:31
...
该文是针对快速合并文件(1)方法不足的补充,应用的是VBA程序来解决的。补充不是完善的,欢迎补充~
合并多个xls、csv工作簿到同一个工作簿中,且保留原来各个工作表名称。以合并Excel文件为例。
1.将需要合并的Excel工作簿文件1、2、3放到一个文件夹中。
2.在该文件夹中新建一个Excel工作簿,取名为“合并结果.xls”
3.打开“合并结果.xls”,选择“开发工具”->“Visual Basic”
4.在打开的面板中,双击其中一个sheet,贴入下面代码即可。
Sub CombineFiles()
Dim path As String
Dim FileName As String
Dim LastCell As Range
Dim Wkb As Workbook
Dim WS As Worksheet
Dim ThisWB As String
Dim MyDir As String
MyDir = ThisWorkbook.path & "\"
'ChDriveLeft(MyDir, 1) 'find all the excel files
'ChDir MyDir
'Match =Dir$("")
ThisWB = ThisWorkbook.Name
Application.EnableEvents = False
Application.ScreenUpdating = False
path = MyDir
FileName = Dir(path & "\*.xls", vbNormal)
Do Until FileName = ""
If FileName <> ThisWB Then
Set Wkb = Workbooks.Open(FileName:=path & "\" & FileName)
For Each WS In Wkb.Worksheets
Set LastCell = WS.Cells.SpecialCells(xlCellTypeLastCell)
If LastCell.Value = "" And LastCell.Address = Range("$A$1").Address Then
Else
WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End If
Next WS
Wkb.Close False
End If
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
Set Wkb = Nothing
Set LastCell = Nothing
End Sub
5.按F5便可运行程序了。结果如下:“过好年”便是工作簿1、2、3工作中的sheet名。返回到工作簿,可看到工作簿1、2、3里面的工作表都整理到“合并结果.xls”工作簿中了。
备注:合并csv格式文件也可用此方法。只要将代码中的xls改为csv。
Dim path As String
Dim FileName As String
Dim LastCell As Range
Dim Wkb As Workbook
Dim WS As Worksheet
Dim ThisWB As String
Dim MyDir As String
MyDir = ThisWorkbook.path & "\"
'ChDriveLeft(MyDir, 1) 'find all the excel files
'ChDir MyDir
'Match =Dir$("")
ThisWB = ThisWorkbook.Name
Application.EnableEvents = False
Application.ScreenUpdating = False
path = MyDir
FileName = Dir(path & "\*.xls", vbNormal)
Do Until FileName = ""
If FileName <> ThisWB Then
Set Wkb = Workbooks.Open(FileName:=path & "\" & FileName)
For Each WS In Wkb.Worksheets
Set LastCell = WS.Cells.SpecialCells(xlCellTypeLastCell)
If LastCell.Value = "" And LastCell.Address = Range("$A$1").Address Then
Else
WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End If
Next WS
Wkb.Close False
End If
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
Set Wkb = Nothing
Set LastCell = Nothing
End Sub
5.按F5便可运行程序了。结果如下:“过好年”便是工作簿1、2、3工作中的sheet名。返回到工作簿,可看到工作簿1、2、3里面的工作表都整理到“合并结果.xls”工作簿中了。
备注:合并csv格式文件也可用此方法。只要将代码中的xls改为csv。
上一篇: 16位色深