如何在excel vba中加入从两个不同数据源创建的两个记录集

我的场景是 - 我在.xls文件中有一组数据,在oracle数据库表中有另一组数据.我想使用excel vba导入两个数据然后执行join(sql like),最后将数据保存在某个工作簿中.

问题 - 我不知道如何在vba中获取两组不同的数据然后执行连接.在.Net中有DataSet对象,我们可以在其中保存导入的数据,然后对其执行任何查询但是vba我该怎么做？

请考虑使用ADO的以下示例.该代码允许从单个SQL查询(Jet SQL)中的多个数据源获取数据,特别是从.xlsx文件中生成联合,并将结果记录集放入工作表.遗憾的是,我没有可用的Oracle数据源进行测试,但您也可以通过Oracle ODBC驱动程序与ADO(与任何其他数据库一样)直接连接到Oracle .

代码放在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