如何加入由excel vba中的两个不同数据源创建的两个记录集 [英] How to join two recordset created from two different data source in excel vba

问题描述

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

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.

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

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?

推荐答案

考虑下面的例子。该代码允许从单个SQL查询中的多个数据源获取数据，特别是从 .xlsx 文件中创建工会，并将结果记录集放在工作表中。代码放在 Query.xlsm 中：

Consider the below example. The code allows to get data from several data sources within single SQL query, particularly make unions from .xlsx files, and put result recordset to the worksheet. 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

同样的文件夹中有三个数据源文件与 Query.xlsm 。不幸的是，我没有可用的Oracle数据源进行测试。

Also there are three data source files in the same folder as Query.xlsm. Unfortunately I have no available Oracle data source to test.

Source1.xlsx ：

Source2.xlsx ：

Source3。 xlsx ：

所得到的工作表如下：

它适用于我64位版本的Excel 2013。要使其与 .xls 和Excel 2003（其中未安装提供程序 ACE.OLEDB.12.0 ）兼容，您必须将 Provider = Microsoft.ACE.OLEDB.12.0; 替换为 Provider = Microsoft.Jet.OLEDB.4.0; ，还有扩展属性 Excel 12.0宏; / Excel 12.0; 与 Excel 8.0 ; 。您可以轻松地向查询添加 WHERE 子句和其他SQL内容。实际上，连接对象的数据源并不限于代码所在的唯一 Query.xlsm 文件。它可以是另一个数据源，与可用提供程序之一兼容，基于文件或基于服务器。在 http://www.connectionstrings.com/ 上查找数据源的更多连接字符串

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/

这篇关于如何加入由excel vba中的两个不同数据源创建的两个记录集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！