一、需求
工作上需要将59个Excel文件合并为一个文件后进行分析,这些文件结构完全一样,文件名有规律,文件内容为简单的带有标题行的数据表,每一行为一条数据。现需要将这些文件合并为一个文件,之后利用Excel的数据分析功能进行综合分析。
二、实现
网上搜了一些文章,都是提供了一些思路,并没有一个完整的范例,最简单的做法是利用VBA模拟人工重复进行“打开子文件–>选择–>复制–>关闭–>粘贴到主文件”操作。
由于对VBA不熟,只能自己摸索了,首先打开Excel的“录制宏”功能,手动执行这个功能,然后参考Excel自身提供的函数,改造出了如下代码:
SubCopy_all()
DimiAsLong’循环变量
DimminAsLong’文件名中变化量的最小数值
DimmaxAsLong’文件名中变化量的最大数值
Diminsert_rowAsLong’合并文件中的粘贴位置
Dimfirst_rowAsLong’待合并文件的最前单元格位置
Dimhave_titleAsBoolean’待合并的文件中是否含有标题,
‘如果含有,除第一个文件外从第二行开始拷贝
DimfilenameAsString’构造文件名
Application.DisplayAlerts =False
‘文件名从page1_of_page59.xls到page59_of_page59.xls
min = 1
max = 59
insert_row = 1’初始化,从第一行开始存放
have_title = True
Fori = minTomax
‘ 构造文件名并打开文件(Excel 的字符串合并还是很简单的)
filename = “H:/Info /page” & i & “_of_page59.xls”
Workbooks.Open filename:=filename
Ifhave_titleThen
‘ 带有标题行,从第1行或第2行一直选择到最后一行
Ifi = minThen
first_row = 1’第一个文件,包含标题行拷贝
Else
first_row = 2’其余文件从第二行开始拷贝
EndIf
Range(“A”&first_row, Cells.SpecialCells(xlCellTypeLastCell)).Select
Else
‘ 不带标题行,全文选择
Range(“A1”, Cells.SpecialCells(xlCellTypeLastCell)).Select
EndIf
‘ 复制所选到剪贴板,并关闭子文件
Selection.Copy
ActiveWindow.Close
‘ 确定需要粘贴的位置,将子文件中的内容粘贴到主文件
Range(“A” & insert_row).Select
ActiveSheet.Paste
‘ 更新主文件中插入的位置
insert_row = Cells.SpecialCells(xlCellTypeLastCell).row + 1
Next
EndSub
说明:
- 合并后的文件成为“主文件”,待合并的文件成为“子文件”;Cells.SpecialCells(xlCellTypeLastCell)的功能为选择最右下角的非空白单元格;本此操作文件的文件名比较规范,可以直接用循环变量进行转化,如果文件名不规律可参考附录,时间关系不在整合到代码中;
三、应用
网上搜搜“Excel文件 合并”,基本都是有类似需求的应用,比如多人整理后的报表合并等,当子文件数量较少时比较容易操作,当数量较大时。。。。还是用这个 VBA 吧 :)附录 – 文件遍历参考代码
Subtest()
DimsFolderAsString
DimwbAsWorkbook
DimiAsLong
WithApplication.FileSearch
.NewSearch
.LookIn = “D:/test”
.SearchSubFolders = True
.Filename = “*.xls”
.FileType = msoFileTypeExcelWorkbooks
If.Execute() > 0Then
Fori = 1 To .FoundFiles.Count
OnError ResumeNext
Setwb = Workbooks.Open(Filename:=.FoundFiles(i))
Nexti
Else
MsgBox “Folder ” & sFolder & ” contains no required files”
EndIf
EndWith
ExitSub
接受自己的失败面,是一种成熟,更是一种睿智;