使用 VBA 和 ADODB 在 Excel 中组合表格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26674223/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Combing Tables in Excel using VBA and ADODB
提问by h.l.m
Using Excel VBA I would like to be able to combine two tables in excel with a common key. I have suggested ADODB as a method,but am open to any other more efficient/elegant methods. Please see below for a minimal example:
使用 Excel VBA，我希望能够将 excel 中的两个表与一个公共键组合在一起。我建议将 ADODB 作为一种方法，但我对任何其他更有效/更优雅的方法持开放态度。请参阅下面的最小示例：
I have the below to start with...
我有以下开始...
Sheet1
表 1
A B C
1 type year1 year2
2 aaa 100 110
3 bbb 220 240
4 ccc 304 200
5 ddd 20 30
6 eee 440 20
Sheet2
表 2
A B C
1 type year1 year2
2 bbb 10 76
3 ccc 44 39
4 ddd 50 29
5 eee 22 23
6 fff 45 55
And would like to combine it so that I have the following as a result:
并想将它结合起来，以便我得到以下结果：
Sheet3
Sheet3
A B C D E
1 type year1 year2 year1 year2
2 aaa 100 110 0 0
3 bbb 220 240 10 76
4 ccc 304 200 44 39
5 ddd 20 30 50 29
6 eee 440 20 22 23
7 fff 0 0 45 55
Have done a bit of googling and SQL type outer joins seems close but not sure how to implement it.
已经做了一些谷歌搜索和 SQL 类型的外连接似乎很接近但不确定如何实现它。
Below is the code used to try and implement it so far...
以下是迄今为止用于尝试和实现它的代码......
Option Explicit
Sub JoinTables()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties=Excel 8.0;"
.Open
End With
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM [Sheet1$] OUTER JOIN [Sheet2$] ON [Sheet1$].[type] = " & _
"[Sheet2$].[type]", cn
With Worksheets("Sheet3")
.Cells(2, 1).CopyFromRecordset rs
End With
rs.Close
cn.Close
End Sub
回答by Michael
So JET does not support full outer joins so I ended up having to rewrite it using a left join and then a right join to check for types we missed:
所以 JET 不支持完全外连接，所以我最终不得不使用左连接和右连接重写它来检查我们错过的类型：
Sub Button1_Click()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
.Open
End With
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT [Sheet1$].[Type] AS Type" & _
" ,[Sheet1$].[Year1] as Year11" & _
" ,[Sheet1$].[Year2] as Year21" & _
" ,[Sheet2$].[Year1] as Year12" & _
" ,[Sheet2$].[Year2] as Year22" & _
" FROM [Sheet1$] LEFT JOIN [Sheet2$] ON [Sheet1$].[type] = " & _
"[Sheet2$].[type] UNION ALL " & _
" SELECT [Sheet2$].[Type] AS Type" & _
" ,[Sheet1$].[Year1] as Year11" & _
" ,[Sheet1$].[Year2] as Year21" & _
" ,[Sheet2$].[Year1] as Year12" & _
" ,[Sheet2$].[Year2] as Year22" & _
" FROM [Sheet1$] RIGHT JOIN [Sheet2$] ON [Sheet1$].[type] = " & _
"[Sheet2$].[type] WHERE [Sheet2$].[type] NOT IN (SELECT type FROM [Sheet1$]) ", cn
With Worksheets("Sheet3")
.Cells(2, 1).CopyFromRecordset rs
End With
rs.Close
cn.Close
End Sub
The flow is like this:
流程是这样的：
- Get matches between Sheet1 and Sheet2
- Get matches between Sheet2 and Sheet1 that we didn't get in the previous query
- 获取 Sheet1 和 Sheet2 之间的匹配
- 获取之前查询中未获得的 Sheet2 和 Sheet1 之间的匹配项
Let me know if you have any questions or if I missed anything.
如果您有任何问题或我遗漏了什么，请告诉我。
Edited the query: I realized did not need the third step and it was excessive.
编辑查询：我意识到不需要第三步，而且太过分了。
