VBA在多Excel工作薄数据汇总的应用

徐志伟，孙航

（1.陕西铁路工程职业技术学院陕西渭南714000；2.开封大学河南开封475000）

Microsoft Office软件是微软公司开发的办公自动化应用软件，Microsoft Excel是其中的一个重要的组成部分，由于它包含大量的公式函数对各种大量数据有很强的处理、统计分析能力，被广泛地应用于管理、统计、金融等领域。伴随计算机的普及已经成为日常办公不可或缺的工具。

VBA（Visual Basic for Application），是基于Visual Basic f or Windows发展而来的[1]。在执行特定功能或重复性高的操作时，使用VBA有助于使工作自动化，提高工作效率。另外，由于VBA可以直接应用Office套装软件的各项强大功能，所以对于程序设计人员的程序设计和开发更加方便快捷。

1 需求分析

在工作中经常会做数据的收集汇总，制定特定表结构的工作表模板供其他人员填写，然后回收汇总合并成一个完整表，一般完成这个任务主要采用手工打开工作表进行复制、粘贴这样简单而重复性较高的操作，容易使人疲惫导致操作错误，难以察觉，工作效率低。

我们知道Excel宏可使任务自动化，用户可创建并执行一个宏，以替代人工进行一系列费时而重复的Excel操作。事实上，它是一个自定义命令，用来完成所需任务。如：加速日常编辑和格式设置、组合多个命令、使对话框中的选项更易于访问、使一系列复杂的任务自动执行等[2]。宏所使用的语言是VBA语言，那么通过VBA编程可以更灵活、高效、快速地编制出应用程序，高效完成工作任务，而且在以后的工作中需要稍加修改或不用修改进行重复使用，这样一天或几天的工作任务就可以在几分钟轻松完成。

2 应用案例

某高校每年都进行职业技能考试，各种考试采用统一制定好格式的报名表，分发到各班级填写报名表，如图1、图2所示，之后由负责人员将这些原始报名数据汇总成一个总表，如图3所示。汇总的过程如果采用不断地打开原始工作表，复制报考学生信息数据，然后粘贴到总表中，如此重复操作，耗费大量的时间和精力。类似的信息统计工作在其他企事业单位也常常遇到，如对所有子公司的销售数据汇总统计，所有下属部门工作人员的信息表收集等。本文即是利用VBA编程来快速解决这样一类问题。

2.1 应用条件分析

本案例中各班级上交的报名登记表表名称为班级名称，工作表模板由考试管理中心制定下发，表结构格式是固定的、统一的，数据均填写在工作薄的第一张工作表中。各班报名人数不同，即表中的数据记录行数不同，但每个人的报名信息要求相同。在最后得到的汇总表增加了序号、班级标题字段，当某一班级的报名表数据被录入汇总表时，对应班级字段填写报名表文件名，用来标注数据来源，方便后续数据统计处理。通过对此类大量工作表进行数据汇总的应用案例分析，发现一些规律，所有的表格具有相同的表结构形式，汇总人要做的就是打开这些表复制其中的有效数据，粘贴到汇总表中。

图1 源数据1Fig.1Source data 1

图2 源数据2Fig.2Source data 2

图3 汇总工作表Fig.3Consolidated worksheet

2.2 VBA编程具体实现数据汇总

2.2.1 设计思路

在使用VBA编程实现数据汇总的过程中，加入一些用和程序间的交互设计，使得程序的适用性更强，具体设计思路如下：

1）准备工作：将所有源工作薄存放于一个文件夹中，汇总工作薄放在其他文件夹下，将汇总结果录入到工作薄汇总表.xls中第二个工作表（汇总表）中，对该工作表设计标题行，在A列、B列分别添加序号、班级列标题，其他列标题和报名登记表列标题相同，并将单元格格式与源工作表格式相同。

2）用户能够通过界面中的浏览按钮选择浏览源工作薄所在的文件夹，获取源工作表所在文件夹的路径。利用VBA代码调用浏览文件夹对话框常用有3种方法[3]：使用API方法，使用Shell.Application方法，使用FileDialog方法。本程序浏览按钮编程应用API方法调用文件夹浏览窗口打开及选择制定文件夹。为获取选择的文件夹路径，设计一个文本框来保存文件夹路径。可以用菜单“视图”→“工具栏”→“控件工具箱”添加一个文本框实现。

3）自定义参数，在对源工作表读取数据时，需要用户根据工作表模板设置读取源工作表数据的起始行和总列数，及汇总表的起始行数、列数。开始录入数据，利用上一步添加控件方法添加一些文本框供用户填写相关参数，这样既实现了程序与用户的交互，也提高了程序的适用性。

4）用户发出开始汇总指令，程序即开始依次将制定文件夹中所有的文件中的数据读取并顺序存储到汇总表中。这一步是整个VBA编程主用部分，当用户点击开始按钮，程序读取选择文件夹路径及设置的参数，根据文件夹路径获取所有文件名存入文件名数组（FilesArr）中，然后开始遍历文件名数组中对应的每一个工作薄，读取其中第一个工作表的数据。此时利用VBA引用Excel对象进行编程，不必显示打开每一个工作薄来读取数据，通过Excel对象的一些方法、属性来计算源工作表有效数据的行数，根据指定的起始行和列将有效数据复制到汇总工作表中，并在班级字段对应单元格填充文件名，用来说明数据来源，序号列自动按序列进行填充，直到将所有源数据复制到汇总表中。

5）查看清除汇总结果，为方便用户查看或清除汇总表汇总内容，设置了查看和清空汇总表数据按钮。当点击清除按钮，自动将汇总表中标题行以下的所有数据删除掉。

2.2.2 界面控件设计

在工作薄汇总表.xls中第一个工作表中进行界面设计，如图4所示，添加5个文本框名称属性分别为：txtPath，txtCols，txtSRowBegin，txtTColBegin，txtTRowBegin，4个命令按钮的名称属性分别为：btnPath，btnCommit，btnReslut，btnClear。其他一些说明性的文字直接编辑到工作表的单元格中，另外为了界面美观将区域的填充色设置为浅蓝色。

图4 界面设计Fig.4Interface design

2.2.3主要过程、函数代码功能

1）点击浏览按钮调用过程代码如下，实现添加源文件所在文件夹路径。

…此处省略打开文件夹对话框代码

以下浏览按钮选择源工作薄文件夹路径

Private Sub btnPath_Click（）

Dim FName As String

FName=BrowseFolderA（Caption:=“选择源工作薄所在文件夹”）

If FName＜＞vbNullString Then txtPath.Text=FName

End Sub

2）开始按钮对应过程，实现将选择的文件夹路径中所有工作薄中的第一个工作表中的数据导入到汇总表中，对应程序流程图[4]如图5所示。

图5 流程图Fig.5Flow chart

Private Sub btnCommit_Click（）

…，省略了为使代码更快而在运行前保存并关闭Excel相关状态的代码

Dim i，j，rCount，s_Rows，s_Col，s_BeginRow，t_BeginRow，t_BeginCol As Long

s_BeginRow=txtSRowBegin.Value′获取源表要读取的数据起始行

s_Col=txtCols.Value′获取源表要读取的数据总列数

t_BeginRow=txtTRowBegin.Value

t_BeginCol=txtTColBegin.Value

以上两行表示汇总表起始写入行/列，不包含标题行/新建字段列，并在后面运行过程中自增1下移/右移到新空白行/列供新的数据导入。

Dim MyFolder As Folder

Dim FileArr

Dim MyFile As File

Dim FilePath As String

以下打开登记文件夹下所有的文件名

Set MyFSO=CreateObject（“Scripting.FileSystemObject”）

Set MyFolder=MyFSO.GetFolder（txtPath）

Set FilesArr=MyFolder.Files

FilePath=txtPath.Value&“”

以下代码依次打开每个源工作薄，检测其中数据记录个数，并循环导入到汇总表中。

For Each MyFile In FilesArr

Set xlapp=CreateObject（“Excel.Application”）′创建EXCEL对象

Set xlbook=xlapp.Workbooks.Open（FilePath&MyFile.Name）′打开已经登记的EXCEL工件簿文件

xlapp.Visible=False′设置EXCEL对象不可见

Set xlsheet=xlbook.Worksheets（1）′设置工作表对象

s_Rows=xlsheet.Range（“a65535”）.End（xlUp）.Row′获取源工作表A列已使用的总行数包括标题行

rCount=s_Rows－s_BeginRow+1计算源工作表sheets（1）数据记录的个数

ThisWorkbook.Sheets（3）.Range（“B”&t_BeginRow&“:”&“B”&（t_BeginRow+rCount－1））=MyFile.Name′在汇总表B列t_BeginRow开始的rCount个单元格填充已打开的工作薄的名称，表示本行数据记录来源。

For i=s_BeginRow To s_Rows

For j=1 To s_Col

ThisWorkbook.Sheets（3）.Cells（t_BeginRow，t_BeginCol+j－1）=xlsheet.Cells（i，j）′将源工作表中数据导入到汇总表该工作薄名称单元格后单元格中

Next

ThisWorkbook.Sheets（3）.Cells（t_BeginRow，1）=t_BeginRow－txtTRowBegin.Value+1′在第一列序号列实现自动填充

t_BeginRow=t_BeginRow+1′汇总表开始行下移逐条实现导入

Next

以下代码关闭源工作薄xlbook.Close xlapp.Quit

Set xlapp=Nothing

Next

…′省略程序运行后，恢复Excel原来的状态的代码

End Sub

3）查看按钮对应代码，打开汇总表查看结果。

Private Sub btnResult_Click（）

Worksheets（"汇总表"）.Activate’激活汇总表，查看结果End Sub

4）清空汇总表数据按钮对应代码，清除汇总表中数据。

Private Sub btnClear_Click（）

Dim a，b As Long

a=txtTRowBegin.Value

b=65536

Worksheets（“汇总表”）.Rows（a&":"&b）.ClearContents’汇总表数据记录行清除内容

End Sub

3 结束语

在通过VBA编程实现多个Excel工作薄的数据汇总案例中，应用Excel对象来操作工作薄、工作表及单元格，而VBA开发的主要的难点和问题就在于相应的对象模型的理解及其应用。在用VBA开发中解决问题的方法比较灵活，如在对浏览文件夹对话框调用时，就可以有多种方法。Microsoft Excel办公软件所提供的命令按钮可以满足办公员平时办公需要，然而通过本例可以看到，要想使办公自动化程度更高、效率更高，则要学习利用VBA对Excel进行二次开发，研究制定出灵活、高效的解决方案，可以让办公人员从大量繁琐的操作中解脱出来。所开发VBA程序可以解决一类问题，用户只需要根据自己的不同需求，进行相应的修改，便可以快速解决类似数据汇总问题。

[1]Walkenbach J.Excel 2003高级VBA编程宝典[M].盖江南，王勇译.北京：电子工业出版社，2005.

[2]伍云辉.完全手册：Excel VBA办公应用开发详解[M].北京：电子工业出版社，2008.

[3]Excel Home.Excel应用大全[M].北京：人民邮电出版社，2008.

[4]史宝会.Visual Basic程序设计教程[M].北京：机械工业出版社，2005.

[5]马维峰.Excel VBA应用开发从基础到实践[M].北京：电子工业出版社，2006.

[6]Kimmel P T，Bullen S，Green J，et al.Excel 2003 VBA Programmer’s Reference[M].Birmingham:WROX，2004.