Excel VBA / SQL Union

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.

Sheet1 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.

Thanks

Answers


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

Need Your Help

How to get field names and offsets of a struct using dbghlp and pdb

windows delphi windbg pdb-files dbghelp

I would like to dump the fields and offsets of structures in the same way as windbg's dt command. Let's say for example I would like to dump the _PEB structure which is in the Microsoft Public symb...

Remove specific directory from multiple users in Windows

cmd rmdir delete-directory

I'm trying to remove a specific directory from every User's AppData directory within Win7. We had a bad install of Cisco on multiple computers, and part of fixing it is that we need to remove AppD...