《Excel VBA+SQL数据管理与应用模板开发》_工作效率UP!_5.2 工作表快速合并

【摘要】 本书摘自《Excel VBA+SQL数据管理与应用模板开发》一书中第5章，第2节，韩小良著。

5.2 工作表快速合并

如何把大量的工作表合并到一个工作表中呢?加入合并的工作表不在一个工作 簿中，而是多个独立的工作簿，又该如何合并呢?每个工作表的列数不一样，但需 要把共有的列数据汇总起来，如何处理?诸如此类，就是工作表的合并问题。

如果基础表格是规范的表单，使用Power Query可以快速地合并并建立数据 模型，在此基础上进行各种分析。但是，如果基础表不规范(例如，列数不一样、 列顺序不一样、表格顶部还有大标题之类的文字),此时使用VBA 就是一种较好的 选择。

本节将结合实际案例，介绍工作表合并的VBA+ADO+SQL 方 法 。

快 速 汇 总 当 前 工 作 簿 或 其 他 工 作 簿 中 的 N 个 工 作 表

这种问题在实际工作中经常会遇到。例如，将当前工作簿中的12个月工资表进行汇总、 将另外一个工作簿里的12个月工资表进行汇总(不打开该工作簿),将工作簿里的数百个店 铺数据进行汇总等。

对于这样的汇总，可使用连接查询的方法来完成，也就是对每个工作表的 SELECT 语句 用UNION 进行连接，即可得到这些工作表的汇总数据。这种方法在第3章已经介绍过了， 语句基本结构如下(注意，下面的语句写法要求每个工作表的列结构必须完全相同，也就是 列数相同，列顺序也相同)。

select *from[表1$]

union

select *from[表2S]

union

select *from [表3$]

union

select *from [表N$]

如果仅仅是汇总这些工作表中共有的几列数据，可以用具体的字段列表来代替星号(*)。

select 字段1,字段2,字段3,…,字段n from[表1S]

unjon

select字段1,字段2,字段3,…,字段n from [表2$]

union

select字段1,字段2,字段3,…,字段n from [表3$]

union

select字段1,字段2,字段3,…,字段n from[表N$]

快速汇总N 个工作簿，每个工作簿只有一个工作表

如果是要汇总指定文件中的N 个工作簿，但每个工作簿中仅有一个工作表，并且每个工 作表都有相同的名称，那么可以使用循环访问工作簿的方法来查询每个工作簿数据，然后把 每个工作簿数据保存在当前工作簿中。

案例5-4

下面的例子是汇总当前工作簿所在文件夹下的子文件夹“案例5-4源文件”里的6个工 作簿，并分别保存6个分公司的工资数据

Sub 合并汇总工作簿_保存到一个工作表0

Dim cnn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim ws As Worksheet

Dimi As Integer,n As Integer,fName As String

Dim wb As Variant

指定保存汇总数据的工作表

Set ws =This Workbook.Worksheets("汇总表")

ws.Range("A2:Z100000").Clear

'指定要汇总的工作簿

wb=Array(" 分公司A"," 分公司B"," 分公司C"," 分公司D"," 分公司E"," 分公司F")

循环每个工作簿进行查询汇总

Fori=0 To UBound(wb)

'确定每个工作簿数据开始保存的起始行

n=ws.Range("A100000").End(xIUp).Row+1

'指定每个工作簿的路径和名称

fName =ThisWorkbook.Path&"案例5-4源文件"&wb(i)&".xlsx"

'建立与每个工作簿的连接

Set cnn =New ADODB.Connection

With cnn

.Provider ="microsoft.ace.oledb.12.0"

.ConnectionString="Extended Properties=Excel 12.0;"_

&"Data Source="&fName

.Open

End With

'查询每个工作簿的数据

Sql="select'"&wb(i)&"as 分公司，*from [工资表$]"

Set rs =New ADODB.Recordset

rs.Open Sql,cnn,adOpenKeyset,adLockOptimistic

ws.Range("A"&n).CopyFromRecordset rs

'关闭该工作簿的查询和连接

rs.Close

cnn.Close

Next i

MsgBox"汇总完毕!"vbInformation," 汇总工作簿"

End Sub

运行上面的程序，即可将6个工作簿数据汇总到一个工作表中，如图5-8所示。

如果不是将6个工作簿数据汇总到当前工作簿的一个工作表中，而是分别保存在当前工 作簿的不同工作表中，工作表名称就是工作簿名称，那么可以将程序修改如下。

Sub 合并汇总工作簿_分工作表保存0

Application.DisplayAlerts =False

Dim cnn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim ws As Worksheet

Dim i As Integer,j As Integer,n As Integer,fName As String

Dim wb As Variant

'指定要汇总的工作簿

wb=Array(" 分公司A"," 分公司B"," 分公司C"," 分公司D"," 分公司E"," 分公司F")

'删除当前工作簿中已经存在的工作表

On Error Resume Next

Fori=0To UBound(wb)

This Workbook.Worksheets(wb(i)).Delete

Next i

'依次访问每个工作簿，查询数据

Fori=0 To UBound(wb)

'创建新工作表，并将新建的工作表重命名为该工作簿名称

Set ws =Worksheets.Add(after:=Worksheets(Worksheets.Count))

ws.Name =wb(i)

'指定每个工作簿的路径和名称

fName =ThisWorkbook.Path&"\案例5-4源文件\"&wb(i)&".xlsx"

'建立与每个工作簿的连接

Set cnn =New ADODB.Connection

With cnn

.Provider ="microsoft.ace.oledb.12.0"

.ConnectionString="Extended Properties=Excel 12.0;"_

&"Data Source="&fName

.Open

End With

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们 18664393530@aliyun.com 处理，核实后本网站将在24小时内删除侵权内容。