Excel VBA / SQL 联合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3040508/
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
Excel VBA / SQL Union
提问by Edge
I am trying to Join 2 separate columns from 2 different sheets to make a longer column from which i can then use a Vlookup from.
我正在尝试从 2 个不同的工作表中加入 2 个单独的列，以制作一个更长的列，然后我可以从中使用 Vlookup。
Sheet1 A, B, C, D, E, F, G
Sheet1 A、B、C、D、E、F、G
Sheet2 A, B, C, D, E, F, G
Sheet2 A、B、C、D、E、F、G
I want to Join(Union) Columns B from sheet1 and C from sheet2 together and find the Distinct values of the new list. I have been working on this for weeks.
我想将 sheet1 中的 B 列和 sheet2 中的 C 列连接在一起并找到新列表的不同值。我已经为此工作了几个星期。
Thanks
谢谢
回答by Fionnuala
You can use ADO with Excel.
您可以在 Excel 中使用 ADO。
Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim s As String
Dim i As Integer, j As Integer
''This is not the best way to refer to the workbook
''you want, but it is very conveient for notes
''It is probably best to use the name of the workbook.
strFile = ActiveWorkbook.FullName
''Note that if HDR=No, F1,F2 etc are used for column names,
''if HDR=Yes, the names in the first row of the range
''can be used.
''This is the Jet 4 connection string, you can get more
''here : http://www.connectionstrings.com/excel
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
''Late binding, so no reference is needed
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
''A sample query
strSQL = "SELECT Distinct A, B C FROM ( " _
& "SELECT A, B, C " _
& "FROM [Sheet1$] " _
& "UNION ALL " _
& "SELECT A, B, C " _
& "FROM [Sheet2$] ) As J "
''Open the recordset for more processing
''Cursor Type: 3, adOpenStatic
''Lock Type: 3, adLockOptimistic
''Not everything can be done with every cirsor type and
''lock type. See http://www.w3schools.com/ado/met_rs_open.asp
rs.Open strSQL, cn, 3, 3
''Write out the data to an empty sheet (no headers)
Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rss
