Excel function to make SQL-like queries on worksheet data?
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.
If you want run formula on worksheet by function that execute SQL statement then use Add-in A-Tools
Example, function BS_SQL("SELECT ...")
:
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
One quick way to do this is to create a column with a formula that evaluates to true for the rows you care about and then filter for the value TRUE in that column.