无论在知乎还是在其他一些网站,都有提问或描述如何才能合并多个工作簿。回答中不乏有推荐power query 之类。power query是新版Excel的功能,看过其简介,的确能感觉到它的强大。不过像我一样用着老版本的Excel的人,并没有福分去分享它的强大能量,毕竟新版office对电脑的配置、系统版本,还是破解等方面,都是懒人所惧的。不过,听说它有针对Office 2010以上版本的插件,有兴趣的朋友可以去尝试一下。
好吧,那仍逗留在老版本Office下的朋友们咋办呢?
幸好在Excel中存在VBA 这样一个万能利器。只要谁有闲又有能,应该可以用VBA来实现新版Excel所具备的所有新奇功能!那我今天就来粗糙地实现下工作簿的合并。
假设一个工作情景:
在某个文件目录中有若干个Excel文件,我们需要打开一个文件选择对话框将他们选中,然后将他们中的每一个工作表拷贝到我当前这个工作簿中。
这样一个功能的描述,应该是清楚的,它所实现的代码其实也并不长,我先将它整体抛出
Sub
对于有VBA基础的朋友来讲,来理解这样一页代码并不费力。VB 这样一个编程语言,写法很自由,不用区分大小写,甚至不用预先变量声明,所以,我的这一页代码仍可以缩短(但就显得不大规范直观亲和了)。
代码的分解
这样一组代码,我开始就说了,它是粗糙的,尤其比起power query,会引起很多人的不屑。以我的理解,数据需求是多变的,但都有一个实现的基础步骤,而我在这里就是展现的这样一个基础步骤。后期,有VBA基础的,或有想具备VBA编程能力的人,都可以在这样一个基础上建自己想要的“高楼”。如果一开始就将”高楼“展现出来,给谁都会是------
╮(๑•́ ₃•̀๑)╭
以上代码量不多,如果真想掌握VBA这一技能,是应该花一段时间仔细研究一下的(我刚开始自学VBA时,也是啃了不少别人的代码)。下面我要做的,就是用函数调用的形式来实现同样的功能。
函数调用的编程方法,是编程实现过程中的常用方法。尤其是复杂的项目中。它主要可以把一个比较复杂的逻辑,分成若干个小的逻辑步骤,这样分步分点地实现,更容易对自己程序逻辑进行确认检查,出现Bug也更容易排查些。
那我们当前的这样一个功能,我是这样将其逻辑进行拆分的
- 打开对话框选文件
- 所选文件依次打开
- 每个文件打开后复制它的所有工作表到当前工作簿
按照上面的逻辑步骤,我将一开始的那段程序拆分成如下代码实现
' ************ 第一步 ***************
Sub Step1_打开文件选择对话框()
Call PreStep_删除历史拷贝表
'----------------------------------------------------------
Dim F As FileDialog
Set F = Application.FileDialog(msoFileDialogFilePicker)
F.Filters.Clear
F.Filters.Add "Select Excel", "*.xls;*.xlsx"
F.Show '启动一个Excel文件选择对话框
'----------------------------------------------------------
For Each filePath In F.SelectedItems
Call Step2_打开工作簿(filePath)
'Step2_打开工作簿 FilePath '调用过程的另一种写法
Next 'For Each 语句获取选择的每一个文件的路径
'----------------------------------------------------------
End Sub
' ************ 第二步 ***************
Sub Step2_打开工作簿(Path As Variant)
Dim excel As Workbook
Set excel = Workbooks.Open(Path) '打开当前所选路径
Dim ExcelName As String
ExcelName = 获取Excel的文件名(excel.Name)
'- - - - - - - - - - - - - - - - - - - - - - - - - - -
Dim sheet As Worksheet
For Each sheet In excel.Sheets
Call Step3_获取工作表并新建拷贝(ExcelName, sheet)
Next 'for each 获取目标工作簿内每一个sheet表
'- - - - - - - - - - - - - - - - - - - - - - - - - - -
Application.CutCopyMode = False
excel.Close False
End Sub
' ************ 第三步 ***************
Sub Step3_获取工作表并新建拷贝(ExcelName As String, sheet As Worksheet)
sheet.Cells.Copy
Dim NewSheet As Worksheet
Set NewSheet = ThisWorkbook.Sheets.Add '新增拷贝表
NewSheet.Cells.PasteSpecial
NewSheet.Name = "Copy_" & ExcelName & "_" & sheet.Name '新增表命名 以Copy开头
End Sub
' 附 **** 需要提前准备的 ****
Sub PreStep_删除历史拷贝表()
Dim sheet As Worksheet
Application.DisplayAlerts = False '不显示提示框
For Each sheet In ThisWorkbook.Sheets
If Left(sheet.Name, 5) = "Copy_" Then
sheet.Delete
End If
Next
Application.DisplayAlerts = True '恢复显示提示框
End Sub
' 附 **** 需要考虑的一个问题点 ****
Function 获取Excel的文件名(ExcelName As Variant) As String
Dim Name As Variant
Name = Split(ExcelName, ".")
If IsArray(Name) Then
获取Excel的文件名 = Name(0)
End If
End Function
细节或相关点解释
函数调用
这里需要说明的是,在VBA中以”Sub“ 标记的称为过程,以”Function“标记的叫函数。
过程的调用方法有两种,一种就是像我上面一样用"CALL"语句
Call Step2_打开工作簿(filePath)
另一种方法,就是我注释掉的那有一种用法
Step2_打开工作簿 FilePath '调用过程的另一种写法
在使用效果上,它们没有任何区别,但需要注意它们之间的语法区别:带call的方法,需要用括号传递参数;不带call的方法,传递参数则不能加括号。(按自己的喜好选一个适合自己的方法)
function 函数,是带返回值的过程,通常有意义的做法是
变量=函数(参数)
但也有人把它当过程一样调用,其实,在公共模块里的function 函数还有一个十分强大的使用场景,它可以扩充Excel 函数库,就像在单元格内输入任何本地函数一样。
比如自己实现一个 “加法” 函数
然后我们就可以在单元格里进行如下操作了
在我们的程序中,也有一个函数,如下:
Function 获取Excel的文件名(ExcelName As Variant) As String
Dim Name As Variant
Name = Split(ExcelName, ".")
If IsArray(Name) Then
获取Excel的文件名 = Name(0)
End If
End Function
每个文件都是以 "文件名.后缀”的形式组织的,而我上面的函数的功能就是:传入带后缀的文件名,获取其“文件名”部分。
function函数的函数名(获取Excel的文件名)在函数体中也是作为变量的,只有在函数体中对其赋值了,函数才会有一个想要的返回值。具体细节建议百度,或者参看帮助(Excel2010以后的版本似乎仅支持在线帮助,离开了网络就抓瞎了【这也是我不想升级软件的一大原因】)。
具体实现上的考量
虽然我们将合并工作薄分三个大的逻辑步骤实现了,大的逻辑实现上并没有什么不妥。但在反复的测试中,我们发现仍存在很多基础性的问题,它们往往能引发很多意外。
1:多次运行后,工作表无限制增加。
所以我们需要在合并工作簿函数运行之前,先删除历史表,仅保留固有表。所以我引入了一个过程“Sub PreStep_删除历史拷贝表()”,在执行第一步的一开始,先删除历史表。
2:我们怎样判断哪些Sheet表是历史性的,哪些是本工作簿固有的表。
所以,我们需要总结并归纳出自己的一套方式逻辑,必须确保本工作簿内的所有工作表的命名不同(这是硬性的基本要求),又能识别出其表的出处,而且一眼看出它是由程序执行拷贝新建的。在本实例中,我把所有新建的工作表均以“Copy_工作簿名_工作表名”的形式来加以区分。(各自在自己的代码实现过程中,可以用自己的方法区分。)
现在我们需要删除历史表时,只要看表面是否带有“Copy_”就可以了
If Left(sheet.Name, 5) = "Copy_" Then
sheet.Delete
End If
后记
以上就是对“合并工作簿”的一个粗糙实现,代码大体严谨且简洁。有兴趣的朋友可以自己再剖析下细节,其中很多代码段均是可以复用的,对于没有基础,或基础较薄弱的,初期能看懂,会修改最合适。
后期,可以在该基础上进行业务逻辑上的修改,用以满足具体业务。
QQ交流群 625157714
-微博 Sina Visitor System
(后期QQ群有变动时,会在微博中告知)