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?
Excel function to make SQL-like queries on worksheet data?
Re: Excel function to make SQL-like queries on worksheet data?
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
```
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
```