16.9　利用数据透视表快速汇总多个工作簿

如果数据源保存在多个工作簿中，并且每个工作簿中又包含多个工作表，手工汇总这些数据时，需要逐个打开工作簿，将所有的原始数据汇总到一个新的工作表中，然后以此工作表为数据源创建数据表。保存原始数据的工作簿中任何数据变更之后，都需要重复上面的繁琐步骤来汇总新数据。

本示例利用数据透视表的外部连接数据源，可以实现方便快捷的汇总和数据更新。

示例16.12　利用数据透视表快速汇总多个工作簿

在示例文件所在的目录中有4个季度明细数据工作簿（Q1.XLSX、Q2.XLSX、Q3.XLSX和Q4.XLSX)，每个工作簿包含该季度3个月份的明细数据工作表，这些工作表中的数据表结构完全相同，如图16-28所示。

图16-28　数据源保存在4个工作簿中

打开示例文件“利用数据透视表快速汇总多个工作簿.xlsm”，运行其中的MultiWKPvt过程，在“数据透视表”工作表中将创建如图16-29所示的数据透视表。任何工作簿中的数据变更之后，只需要刷新数据透视表就能获得最新的汇总结果。

图16-29　汇总多个工作簿生成的数据透视表

代码解析：

第7行代码禁止屏幕更新，提高代码运行效率。

第8行代码到第10行代码清除“数据透视表”工作表的数据透视表。

第11行代码获取示例文件所在的目录名称。

第12行代码获取示例文件目录名称和文件名。

第13行代码到第19行代码指定ODBC为数据透视表缓存的外部数据源。此部分代码涉及SQL查询和ODBC数据源等相关知识，限于篇幅无法进行详细讲解。读者如果希望了解这些语句的具体含义，请参考相关书籍。

第15行代码设置ODBC连接属性。

第16行代码设置OLE DB连接属性。

注意

本示例中既可以使用ODBC连接外部数据源，也可以使用OLEDB连接外部数据源，但是两者的连接参数并不相同。

第17行代码设置CommandType属性为xlCmdSql，即使用一个SQL查询语句返回的数据集作为创建数据透视表的数据源。

第18行代码创建SQL查询语句。

第20行代码在“数据透视表”工作表中创建名称为“MultiWKobjPvtTbl”的数据透视表。

第22行代码设置数据透视表为手动更新方式。

第23行代码添加数据透视表的行字段、列字段和筛选字段。

第24行代码添加值字段“发生额”，并设定其汇总方式为“求和”，字段标题为“发生总额”。

第25行代码到第27行代码隐藏“月”字段的部分条目。

第28行代码恢复数据透视表的自动更新方式。

第30行代码恢复屏幕更新。

第31行代码和第32行代码释放对象变量。