Excel function to make SQL-like queries on worksheet data?

Question

I have a largish table in an Excel worksheet:

Column_1 | Column_2 | Column_3  ValueA       ValueB     ValueC .... 

What I need is a function that will take as input the range and an SQL-like query String and return a range of rows that match the query, e.g.:

=SQL_SELECT(A1:C1000, "SELECT * WHERE Column_1 = ValueH AND Column_3 = blah") 

Does something like this exist? Or what would be the best way to implement myself?

Answer 1

You can use Get External Data (despite its name), located in the 'Data' tab of Excel 2010, to set up a connection in a workbook to query data from itself. Use From Other Sources From Microsoft Query to connect to Excel

Once set up you can use VBA to manipulate the connection to, among other thing, view and modify the SQL command that drives the query. This query does reference the in memory workbook, so doesn't require a save to refresh the latest data.

Here's a quick Sub to demonstrate accessing the connection objects

Sub DemoConnection()     Dim c As Connections     Dim wb As Workbook     Dim i As Long     Dim strSQL As String          Set wb = ActiveWorkbook     Set c = wb.Connections     For i = 1 To c.Count         ' Reresh the data         c(i).Refresh          ' view the SQL query         strSQL = c(i).ODBCConnection.CommandText         MsgBox strSQL     Next End Sub 

Answer 2

If you can save the workbook then you have the option to use ADO and Jet/ACE to treat the workbook as a database, and execute SQL against the sheet.

The MSDN information on how to hit Excel using ADO can be found here.