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

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:

Sub RunSELECT()
    Dim cn As Object, rs As Object, output As String, sql as String
    
    '---Connecting to the Data Source---
    Set cn = CreateObject("ADODB.Connection")
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
        .Open
    End With
    
    
    '---Run the SQL SELECT Query---
    sql = "SELECT * FROM [Sheet1$]"
    Set rs = cn.Execute(sql)
    
    Do
       output = output & rs(0) & ";" & rs(1) & ";" & rs(2) & vbNewLine
       Debug.Print rs(0); ";" & rs(1) & ";" & rs(2)
       rs.Movenext
    Loop Until rs.EOF
    MsgBox output
    
    '---Clean up---
    rs.Close
    cn.Close
    Set cn = Nothing
    Set rs = Nothing
End Sub

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:

'---Connect to the Workbook---
    Set cn = CreateObject("ADODB.Connection")
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
        .Open
    End With

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.

Running the SQL Select Query

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

'---Running the SQL Select Query---
Sql = "SELECT * FROM [Sheet1$]"
Set rs = cn.Execute(Sql)
 
Do
   output = output & rs(0) & ";" & rs(1) & ";" & rs(2) & vbNewLine
   Debug.Print rs(0); ";" & rs(1) & ";" & rs(2)
   rs.Movenext
Loop Until rs.EOF
MsgBox output

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

SELECT * FROM [Sheet1$]

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:

SELECT * FROM [Sheet1$] WHERE Age > 30

This would be the result:

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

Do
   '...
   'Loop through records - rs(0) - first column, rs(1) - second column etc.
    '...
   rs.Movenext 'Move to next record
Loop Until rs.EOF 'Have we reached End of RecordSet

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!

rs.Close
cn.Close
Set cn = Nothing
Set rs = Nothing

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.

  SELECT [Sheet1$].[First Last], [Age], [Salary] FROM [Sheet1$] INNER JOIN [Sheet2$] ON [Sheet1$].[First Last]=[Sheet2$].[First Last]
  

  On the below tables:
  

• 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