I’ve got a lot to write about and I wanna beef up my newly born website with good content on a daily basis.
Today, I selected a good topic for Excel users with good SQL experience (assuming you have little VBA knowledge).
You can avoid the hassle of searching about a working method to query Excel files from Excel by following this tutorial as a shortcut. I am sure you can discover the other methods later and the differences between them.
This tutorial is right if you are aiming to run SELECT SQL queries against an Excel spreadsheet.
It is not the right one if you are looking to run SQL (from Excel) against an Access database or other sources.
Tested on Excel 2007 and 2010 but it should work on Excel 2007 and later versions and all Excel types (*.xls , *.xlsx, *.xlsm and etc).
Some methods explained in the Internet only works on *.xls and others only work on *.xlsx. I selected a method which works on all formats.
OK Let’s begin:
Download the sample file where I made everything ready to make it easy for you guys to copy the same into your Excel VBA projects.
If you would like to implement the same method in your Excel files, please go through the below points:
1. Note that the data should have headers (Row 1)
2. Microsoft ADO (ActiveX Data Objects) Library should be enabled*
3. Copy Sub Run_SQL and Function Recordset code blocks into a VBA module
4. database string variable can be the active workbook (as in my sample file) or you can put the full path to the Excel file you want to query from
5. write your SELECT query into Qry variable (I’ve made a multi line one as an example if you wanna break your lengthy queries to make it more readable)
6. change the sheet name as required ([Sheet1$] in the example and please don’t miss the $ sign)
7. Either you paste the returned recordset into the desired destination or navigate through it using a loop
* Please enable ADO (Alt-F11 > Tools > References) in each of your VBA projects that will implement the method used in this tutorial (already enabled in the sample file). Refer to the screenshot.
Please follow for coming tutorials.