Can I use SQL to build an Excel data table from other Excel files?

I know SQL well enough to perform basic queries and manipulate data split into multiple tables in a database. I know Excel 2007 a bit better and use it frequently to manage my mechanical engineering projects.

Sometimes in Excel I want to manipulate data from multiple spreadsheets and combine into one table - a task well-suited for a SQL query. Usually when I encounter problems like this, my immediate reaction is to start building a query in my head. Unfortunately, a lot of times SQL queries do not easily translate to Excel functions and this stops me in my tracks.

Is it possible to utilize SQL for these functions within Excel?

Solution 1:

You can use ADO with Excel VBA. For example:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset 

''This is just a convenient name to test, it would probably be
''better to use the full file name eg C:\Docs\XL.xls
strFile = Workbooks(1).FullName

''For ACE see: http://www.connectionstrings.com/excel-2007
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

''Pick one:
strSQL = "SELECT * FROM DataTable" ''Named range
strSQL = "SELECT * FROM [Sheet1$A1:E346]" ''Range
strSQL = "SELECT * FROM [Sheet1$]" ''All the data in a sheet
strSQL = "SELECT * FROM [Excel 8.0;HDR=YES;IMEX=1;" _
       & "database=C:\Docs\LTD.xls].[Sheet1$]" ''Refer to second workbook

rs.Open strSQL, cn

''Write a recordset to a sheet
Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rs

The query can use anything acceptable in Jet SQL:

Fundamental Microsoft Jet SQL for Access 2000
Intermediate Microsoft Jet SQL for Access 2000
Advanced Microsoft Jet SQL for Access 2000

You will find quite a bit more on: https://stackoverflow.com/, including adding to MS Access, SQL Server, MySQL and so forth.

Solution 2:

Your case is exactly like I had couple months ago. I also try to build SQL query in my head every time I need to do some manipulate with data in Excel. So, I have created a little Add-In called XLTools SQL Queries. Now I have published it on our website for others like you and me!

It allows to build any SQL query against tables in Excel workbook(s) using embedded SQL editor and run it immediately with the option to put result on a new or any existing worksheet.

It is a lot easier to use if you just need to create and run SQL query - no VBA, no complex manipulations with MS Query...

Here is an example: