Excel VBA SQL - 多个数据源 [英] Excel VBA SQL - multiple data sources

问题描述

我有一个相当简单的问题，我找不到答案。

我有以下SQL： -

 从filea中选择一个（从文件b中选择b）
  

我试图在Excel中使用VBA运行。

我遇到的问题是filea是AS / 400上的表，fileb是表格在Excel电子表格中。就是说，两个不同的数据源。
我找不到一个SQL语句中的两个数据源的组合方式。

任何人都有任何明智的想法。

解决方案

以下示例显示了如何从单SQL查询中的两个excel工作簿获取数据（因为我没有任何AS / 400数据源）结果记录集到工作表。代码放在 Query.xlsm 中：

  Option Explicit 
 
 Sub SqlWhereInTest（）
 
 Dim strConnection As String 
 Dim strQuery As String 
 Dim objConnection As Object 
 Dim objRecordSet As Object 
 
 strConnection = _ 
Provider = Microsoft.ACE.OLEDB.12.0; &安培; _ 
用户ID =管理员 &安培; _ 
Data Source ='& ThisWorkbook.FullName& ; &安培; _ 
Mode = Read; &安培; _ 
扩展属性=Excel 12.0宏;; 
 
 strQuery = _ 
SELECT * FROM [Sheet1 $]& _ 
IN& ThisWorkbook.Path& \Src1.xlsx& _ 
[Excel 12.0; Provider = Microsoft.ACE.OLEDB.12.0; Mode = Read; Extended Properties ='HDR = YES;']& _ 
WHERE国家IN& _ 
（SELECT CountryFilter FROM [Sheet1 $]& _ 
IN& ThisWorkbook.Path&\Src2.xlsx'& _ 
 Excel 12.0; Provider = Microsoft.ACE.OLEDB.12.0; Mode = Read; Extended Properties ='HDR = YES;']）
 
设置objConnection = CreateObject（ADODB.Connection）
 objConnection.Open strConnection 
设置objRecordSet = objConnection.Execute（strQuery）
 RecordSetToWorksheet表（1），objRecordSet 
 objConnection.Close 
 
 End Sub 
 
 Sub RecordSetToWorksheet（objSheet As Worksheet，objRecordSet As Object）
 
 Dim i As Long 
 
 With objSheet 
 .Cells.Delete 
对于i = 1 To objRecordSet.Fields.Count 
 .Cells（1，i）.Value = objRecordSet.Fields（i  -  1）.Name 
下一个
 .Cells（2，1） .CopyFromRecordset objRecordSet 
 .Cells.Columns.AutoFit 
 End with 
 
 End Sub 
  

Als o有两个工作簿作为数据源与 Query.xlsm 相同的文件夹。

Src1.xlsx 包含

Src2.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 ; 。实际上，连接对象的数据源不限于 Query.xlsm 文件（请参阅 ThisWorkbook.FullName code> strConnection ），代码放在其中。它可以是另一个数据源，与可用提供程序之一兼容，基于文件或基于服务器。在 http://www.connectionstrings.com/ 上查找数据源的更多连接字符串

I have a fairly simple problem which I cannot find an answer to.

I have the following SQL:-

Select a from filea where a in (select b from fileb)

I am attempting to run this in Excel using VBA.

The problem I have is that filea is a table on an AS/400 and fileb is a table in an Excel spreadsheet. That is, two different datasources. I can't find a way to combine the two datasources in one SQL statement.

Anybody got any bright ideas.

解决方案

The below example shows how to get data from two excel workbooks within single SQL query (since I haven't got any AS/400 data source), and put result recordset to the worksheet. The code is placed in Query.xlsm:

Option Explicit

Sub SqlWhereInTest()

    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 & "\Src1.xlsx' " & _
        "[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] " & _
        "WHERE Country IN " & _
        "(SELECT CountryFilter FROM [Sheet1$] " & _
        "IN '" & ThisWorkbook.Path & "\Src2.xlsx' " & _
        "[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'])"

    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

Also there are two workbooks as data sources in the same folder as Query.xlsm.

Src1.xlsx containing Customers:

Src2.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;. Actually data source for connection object isn't limited the only Query.xlsm file (see ThisWorkbook.FullName part within strConnection), 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 SQL - 多个数据源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！