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?
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
Answered By – chris neilsen
Answer Checked By – Jay B. (BugsFixing Admin)