SQL in VBA

Using SQL in VBA on Excel. Run SELECT Queries from VBA

1 Star2 Stars3 Stars4 Stars5 Stars (5 votes, average: 4.40 out of 5)
Loading...

Many times I was irritated of the lack of some Excel functionality (or just I don’t know there is) to easily transform data w/o using pivot tables. SQL in VBA was the only thing that was missing for me.

Distinct, grouping rows of Excel data, running multiple selects etc.
Some time agon when I had a moment of time to spare I did my homework on the topic only to discover that running SQL queries from Excel VBA is possible and easy…

Using SQL in VBA example

sql in excel
An Excel Worksheet and the Output of the RunSELECT Macro
Let see how to run a simple SELECT SQL Query in Excel VBA on an example Excel Worksheet. On the right see my Excel Worksheet and the Message Box with the similar output from my VBA Macro. The VBA Code is below:

Explaining the Code

So what is happening in the macro above? Let us break it down:

Connecting to the Data Source

First we need to connect via the ADODB Driver to our Excel Worksheet. This is the same Driver which runs SQL Queries on MS Access Databases:

The Provider is the Drive which is responsible for running the query.

The ConnectionStrings defines the Connection properties, like the path to the Queries File (example above is for ThisWorkbook) or if the first row contains a header (HDR).

The Open command executes the connection.

You can find more information on the ADODB.Connection Object on MSDN.

Looking for other Connection Strings to XLS or Access files? Check out ConnectionStrings.com. Here are some examples:

Running the SQL Select Query

Having connected to our Data Source Excel Worksheet we can now run a SQL SELECT Query:

So what happens here? First we run the Execute command with our SELECT query:

What does it do? It indicates that our records are in Sheet1. We can obviously extend this query just to filter people above the age of 30:

This would be the result:

sql in excel - above 30
SELECT * FROM [Sheet1$] WHERE Age > 30

The Execute command returns a ADODB RecordSet. We need to loop through the recordset to get each record:

Clean up

Lastly we need to Clean up our Objects to free memory. This is actually quite an important step as if you VBA code is runs a lot of queries or computations you might see a slow-down soon enough!

What Else Can I Do?

You can do tons of great things with ADODB / MS Queries / SQL in Excel. Here are some additional ideas:

  • Run Queries Across Worksheets – you can run JOIN queries on multiple Excel Worksheets. E.g.

    On the below tables:
    sql in excel example
    Running SELECT with JOIN on 2 Worksheets
  • Extracting Data from External Data Sources – use different connection strings to connect to Access Databases, CSV files or text files
  • Do more efficient LOOKUPs – read my post on VLOOKUP vs SQL to learn more

Related Posts