I can link the results of an existing table/view to an Excel spreadsheet (by choosing the table from the "Select Database and Table" window of the ODBC Wizard, however I'd like to execute a raw query instead.
I would like to generate a query (as a string) based on cell values. Then, the user could manually "refresh" the data table which would send the current string to Oracle and return the results.
Is there a way to link the results of raw query (DML) to an Excel spreadsheet?
I have once (well, twice) about this in my blog here and here. Maybe, it is of some help for you.
Rene
Once you have a database table linked into your spreadsheet, you can do this with a macro. Assuming your linked table starts in cell A1:
' Build your query string - using whatever cell values you need
sqlQuery = "select * from myTable"
' Get the cursor on the top left cell of the existing linked table
Range("A1").Select
' Set the CommandText of that QueryTable to your new query and refresh it
With Selection.QueryTable
.CommandText = sqlQuery
.Refresh BackgroundQuery:=False
End With
Give that a try ...
You have to setup the query, then create a parameter in the query to limit the result set. Typically, you then have that query populate a new sheet. You will be prompted to put a value for the parameter. Set it to a cell reference, and check the checkbox to automatically re-query if that cell changes.
