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…
Want to learn how to create a MS Query manually? See my MS Query Tutorial
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
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
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]
- 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