Execute Oracle Query (DML) from Excel

Question

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?

Answer 1

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.

Answer 2

I have once (well, twice) about this in my blog here and here. Maybe, it is of some help for you.

Rene

Answer 3

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 ...

Ron