借助Access对Excel多表合并提速又简单

深海迷航 · 发表于 2022-12-22 17:50:16

从前有一个工作簿，里面有3张表（也可能有30张表或更多）

想将他们进行合并，并注明来源，且能自由选择对哪些列进行合并

ExcelVBA肯定能做到，大家感兴趣可以看我的ExcelVBA

但是现在问题来了，如果数据量特别大，假设每个表都有104万行，ExcelVBA使用数组也很慢，在ExcelVBA中使用ADO可以，但是要考虑的问题较多（例如表头问题等等），并不灵活。

这时，可以请出Excel的大哥哥Access

本文配套视频：

本文配套课件：

课件.xlsx
11.9K
· 百度网盘

思路及方法：

第一步：我们将指定工作簿的文件中的所有工作表，以链接表形式导入到Access中【你只需要修改你的工作簿所在的详细路径即可】

Sub 导入()
路径 = &#34;C:\Users\sunyihang\Desktop\网友问题.xlsx&#34;
Set excel对象 = CreateObject(&#34;Excel.Application&#34;)
Set 工作簿 = excel对象.Workbooks.Open(路径)
For Each 工作表 In 工作簿.Worksheets
DoCmd.TransferSpreadsheet acLink, 10, 工作表.Name, 路径, True, 工作表.Name & &#34;!&#34;
Next
工作簿.Close
Set 工作簿 = Nothing
End Sub第二步：将导入的链接表批量改名，我暂时改成表01、表02、表03 可以根据你的需求变更，也可以写成selectcase形式

Sub 批量改名()
For Each 表 In CurrentDb.TableDefs
If 表.Attributes = dbAttachedTable Then
      If 表.Name = &#34;表1&#34; Then
         表.Name = &#34;表01&#34;
      ElseIf 表.Name = &#34;表2&#34; Then
         表.Name = &#34;表02&#34;
      ElseIf 表.Name = &#34;表3&#34; Then
         表.Name = &#34;表03&#34;
      End If
End If
Next
Application.RefreshDatabaseWindow
End Sub第三步：我们需要讲所有表做全外链接的查询。其实这里我们只是拼接了一个SQL语句创建了一个查询。

Sub 查询所有字段()
For Each 查询 In CurrentDb.QueryDefs
If 查询.Name = &#34;全部字段&#34; Then CurrentDb.QueryDefs.Delete &#34;全部字段&#34;
Next
s = &#34;select * from &#34;
For Each 表 In CurrentDb.TableDefs
If 表.Attributes = dbAttachedTable Then
      SQL = SQL & &#34; union all &#34; & s & 表.Name
End If
Next
Set qdf = CurrentDb.CreateQueryDef(&#34;全部字段&#34;, Mid(SQL, 12))
Application.RefreshDatabaseWindow
End Sub第四步：我们改进上一步代码，因为我们的需求中包含合并表的来源

Sub 查询所有字段带来源()
For Each 查询 In CurrentDb.QueryDefs
If 查询.Name = &#34;全部字段带来源&#34; Then CurrentDb.QueryDefs.Delete &#34;全部字段带来源&#34;
Next
s1 = &#34;select *,分数,&#39;&#34;
s2 = &#34;&#39;as 来源 from &#34;
For Each 表 In CurrentDb.TableDefs
If 表.Attributes = dbAttachedTable Then
      SQL = SQL & &#34; union all &#34; & s1 & 表.Name & s2 & 表.Name
End If

Next
Set qdf = CurrentDb.CreateQueryDef(&#34;全部字段带来源&#34;, Mid(SQL, 12))
Application.RefreshDatabaseWindow
End Sub第五步：如果我们需要将指定列进行合并，不合并所有列，很简单，只需要将S1变量中的*号改成你需要的字段就可以了。

第六步：如果需要保存成Excel文件，可以导出，详见本文章配套视频。

想学习详细的VBA知识，可以观看我的视频：

课件和笔记：https://share.weiyun.com/vvoV5Xpe

		自动登录	找回密码
密码			立即注册