sql - How to join two recordset created from two different data source in excel vba

Question

My Scenario is - I have one set of data in .xls file and another set of data in oracle data base table. I want import both data using excel vba then perform join (sql like) and finally save the data in some workbook.

Problem - I do not know how to get two different set of data in vba and then perform join. In .Net there is DataSet object where we can save the imported data and then perform any query on it but vba How I can do this?

Answer 1

Consider the below example using ADO. The code allows to get data from several data sources within single SQL query (Jet SQL), particularly make unions from .xlsx files, and put result recordset to the worksheet. Unfortunately I have no available Oracle data source to test, though you can connect directly to Oracle also with ADO (like any other database) via Oracle ODBC driver.

The code is placed in Query.xlsm:

Option Explicit

Sub SqlUnionTest()

    Dim strConnection As String
    Dim strQuery As String
    Dim objConnection As Object
    Dim objRecordSet As Object

    strConnection = _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "User ID=Admin;" & _
        "Data Source='" & ThisWorkbook.FullName & "';" & _
        "Mode=Read;" & _
        "Extended Properties=""Excel 12.0 Macro;"";"

    strQuery = _
        "SELECT * FROM [Sheet1$] " & _
        "IN '" & ThisWorkbook.Path & "Source1.xlsx' " & _
        "[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] " & _
        "UNION " & _
        "SELECT * FROM [Sheet1$] " & _
        "IN '" & ThisWorkbook.Path & "Source2.xlsx' " & _
        "[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] " & _
        "UNION " & _
        "SELECT * FROM [Sheet1$] " & _
        "IN '" & ThisWorkbook.Path & "Source3.xlsx' " & _
        "[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] " & _
        "ORDER BY ContactName;"

    Set objConnection = CreateObject("ADODB.Connection")
    objConnection.Open strConnection
    Set objRecordSet = objConnection.Execute(strQuery)
    RecordSetToWorksheet Sheets(1), objRecordSet
    objConnection.Close

End Sub

Sub RecordSetToWorksheet(objSheet As Worksheet, objRecordSet As Object)

    Dim i As Long

    With objSheet
        .Cells.Delete
        For i = 1 To objRecordSet.Fields.Count
            .Cells(1, i).Value = objRecordSet.Fields(i - 1).Name
        Next
        .Cells(2, 1).CopyFromRecordset objRecordSet
        .Cells.Columns.AutoFit
    End With

End Sub

Note, ThisWorkbook.Path should not contain ' within path. Otherwise you have to escape them by replacing ' with ''.

Also there are three data source files in the same folder as Query.xlsm.

Source1.xlsx:

Source2.xlsx:

Source3.xlsx:

The resulting worksheet is as follows:

It works on 64-bit version Excel 2013 for me. To make it compatible with .xls and Excel 2003 (where the provider ACE.OLEDB.12.0 isn't installed) you have to replace Provider=Microsoft.ACE.OLEDB.12.0; with Provider=Microsoft.Jet.OLEDB.4.0;, and also in extended properties Excel 12.0 Macro; / Excel 12.0; with Excel 8.0;. You can easily add WHERE clause and other SQL stuff to the query. Actually data source for connection object isn't limited the only Query.xlsm file, which the code placed in. It could be another data source, compatible with one of the available providers, either file-based or server-based. Find more connection strings for your data source on http://www.connectionstrings.com/