Excel VBA/SQL联合会 [英] Excel VBA / SQL Union

问题描述

我正在尝试将2个不同工作表中的2个单独的列连接起来，以制作更长的列，然后可以从中使用Vlookup.

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.

Sheet1A，B，C，D，E，F，G

Sheet1 A, B, C, D, E, F, G

Sheet2A，B，C，D，E，F，G

Sheet2 A, B, C, D, E, F, G

我想将工作表1中的B列与工作表2中的C列(联合)连接在一起，并找到新列表的Distinct值.我已经为此工作了好几个星期.

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.

谢谢

推荐答案

您可以将ADO与Excel一起使用.

You can use ADO with Excel.

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

这篇关于Excel VBA/SQL联合会的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！