Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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: http://http://stackoverflow.com/, including adding to MS Access, SQL Server, MySQL and so forth.

share|improve this answer
    
Thanks a lot - wow this is a little more over my head than I thought. I'll check out some solutions on S.O. and maybe even look into just transferring my data straight over into a small database. –  NoCatharsis Nov 23 '10 at 17:10

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:

XLTools SQL Queries - Query Builder

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.