How to SQL query Excel data in VBA?

There are tons of ways to get the data from Excel worksheet. This one might be not so obvious at the first glance. In this article I’m going to show You how to query Excel data using SQL in VBA.

Yes, I am going to mix some SQL with VBA! And this is better than You might think! But before that let’s start with the nice & sweet reminder about ListObject approach. Really cool and quite easy alternative amongs such methods like Excel table range or arrays.

But I think it is time to go level up and use SQL query. Personally it helped me with big and heavy Excel files, which struggles even with such simple task as opening. I’m not even mentioning here any kind of loop on such data (oops, I just did…).

Project references

Now let’s remind ourselfes the Microsoft SQL Server connection, because needed references will be the same here: one of the Microsoft ActiveX Data Object Library, in my case it’s 6.1 version.

Sample data

To have reproducible results we’ll take once again financial sample data from Microsoft documentation site, just like in the previous article.

The exercise

To be able to compare the results of approaches (I mean the results should be the same as in ListObject method) take the same order as last time – filter out the sample data by specified Country and specified Manufacturing Price.

The function

Assuming that we already know that we will need to check the amount of rows for 2 sets of specified parameters, let’s create a function for that. The creation of this function can be broken down into 3 parts:
– SQL string query
– connection query
– ADODB recordset

SQL string query

In a simple words – just write SELECT query as You do in SQL, as string variable.

Const COUNTRY_COL_NAME = "Country"
Const MAN_PRICE_COL_NAME = "Manufacturing Price"
    
Dim mySQL As String
mySQL = "SELECT COUNT([Country]) " & _
    "FROM [" & myFile & "].[Sheet1$] " & _
    "WHERE [" & MAN_PRICE_COL_NAME & "] > " & manufacturingMinimumPrice & _
    " AND [" & COUNTRY_COL_NAME & "] = '" & country & "'"

This time all the calculations are on the SQL side. The VBA will get the final result, what I exactly need in the end.

Connection string

The connection string can be taken from the external site. There are tons of examples, take the one which fits the situation.

Dim myConnection As String
myConnection = "Provider=Microsoft.ACE.OLEDB.16.0;" & _
    "Data Source=" & myFile & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"

ADODB recordset

Now some constants, set ADODB Recordset and create the query using Open function with parameters: SQL string query, connection string and constants.

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
    
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
    
rs.Open mySQL, myConnection, adOpenStatic, adLockOptimistic, adCmdText

Function return

The outcome will be 1 dimensional – 1 column – and have only 1 row. That’s why You don’t have to move to first record and can just take the value from the first (0) field – rs.Fields(0).Value.

countRowsForGivenParamsSQLExcel = "Country: " & country & "; Manufacturing Minimum Price: " & manufacturingMinimumPrice & "; Amount: " & rs.Fields(0).Value

The results

Putting all together, the function will look like this:

Function countRowsForGivenParamsSQLExcel(ByVal myFile As String, country As String, manufacturingMinimumPrice As Integer) As String

Const COUNTRY_COL_NAME = "Country"
Const MAN_PRICE_COL_NAME = "Manufacturing Price"

Dim mySQL As String
mySQL = "SELECT COUNT([Country]) " & _
    "FROM [" & myFile & "].[Sheet1$] " & _
    "WHERE [" & MAN_PRICE_COL_NAME & "] > " & manufacturingMinimumPrice & " " & _
    "AND [" & COUNTRY_COL_NAME & "] = '" & country & "'"

Dim myConnection As String
myConnection = "Provider=Microsoft.ACE.OLEDB.16.0;" & _
    "Data Source=" & myFile & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open mySQL, myConnection, adOpenStatic, adLockOptimistic, adCmdText

countRowsForGivenParamsSQLExcel = "Country: " & country & "; Manufacturing Minimum Price: " & manufacturingMinimumPrice & "; Amount: " & rs.Fields(0).Value

End Function

Function call:

Sub getDataUsingSQL()

Dim myFile As String
myFile = "...\Financial Sample.xlsx"

Debug.Print countRowsForGivenParamsSQLExcel(myFile, "Canada", 100)
Debug.Print countRowsForGivenParamsSQLExcel(myFile, "Germany", 200)

End Sub

The final results of the call are the same as in previous ListObject article:

Country: Canada; Manufacturing Minimum Price: 100; Amount: 60
Country: Germany; Manufacturing Minimum Price: 200; Amount: 40

Get Excel data using SQL in VBA?

And this was how to query data from Excel using SQL in VBA. This is really simple, isn’t it? And really fast, especially in case of bigger data.
This time was complex SQL query string, in next articles I will show You how to manipulate data looping through recordsets and get the data You need.