I’m trying to make SQL query on multiple Excel worksheets.
Three worksheets – one with condition, second with source data and third with the output.
In the Output I need all records from the Source where value in the ColumnA is present in the Condition next to a positive number.
I was trying to do something like this:
Select * From [Source$] Where [ColumnA] In ( Select [Column1] From [Condition$] Where [Column2] > 0 )
First problem occurred because of blank values in the Condition table. It can not handle number comparison if there are blank values in that column –
Data type mismatch in criteria expression. For now I dealt with it by doing this ugly thing:
Where Column2 <> '' And Column2 <> '0'
But that’s not the main question. I have bigger problem and it is has something to do with nesting one Select inside another. Even though inner Select returns single column with values like this:
I’m still getting error
Type mismatch in criteria expression. (slightly different error than above).
So I can run inner expression just fine. I can run outer expression with hard coded values also just fine (for example
Where ColumnA In "'value1','value2',..."). But when I’m nesting them I’m getting the error.
To my SQL knowledge I think this query should work, but for some reason inner query in excel returns data that is not compatible with
This is my full code (thanks to this SO answer):
Option Explicit Private Const adCmdText As Long = 1 Private Const adStateOpen As Long = 1 Public Sub DisplayView() Dim dbField As Variant Dim fieldCounter As Long Dim dbConnection As Object Dim dbRecordset As Object Dim dbCommand As Object Dim OutputSheet As Excel.Worksheet Set dbConnection = CreateObject("ADODB.Connection") Set dbRecordset = CreateObject("ADODB.Recordset") Set dbCommand = CreateObject("ADODB.Command") Set OutputSheet = ThisWorkbook.Worksheets("Output") 'Do a quick check to determine the correct connection string 'if one of these don't work, have a look here --> https://www.connectionstrings.com/excel/ If Left$(ThisWorkbook.FullName, 4) = "xlsm" Then dbConnection.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ ThisWorkbook.FullName & ";Extended Properties='Excel 12.0 Macro;HDR=YES';" Else dbConnection.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ ThisWorkbook.FullName & ";Extended Properties='Excel 12.0;HDR=YES';" End If 'Open the connection and query dbConnection.Open With dbCommand .ActiveConnection = dbConnection .CommandType = adCmdText .CommandText = "Select * From [Source$] Where [ColumnA] In (Select [Column1] from [Condition$] where [Column1] > 0)" Set dbRecordset = .Execute End With 'Clear the Output Sheet OutputSheet.Cells.Clear 'Add Headers to output For Each dbField In dbRecordset.Fields fieldCounter = fieldCounter 1 OutputSheet.Cells(1, fieldCounter).Value2 = dbField.Name Next 'Dump the found records OutputSheet.Range("A2").CopyFromRecordset dbRecordset If dbConnection.State = adStateOpen Then dbConnection.Close End Sub 'Run from here Public Sub ExampleRunner() Dim t As Double t = Timer DisplayView Debug.Print "Getting data took: " & Timer - t & " seconds" End Sub