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/
