Importing from Oracle (Excel 2003 SP3)

Topic

New Reply

Hello, This is my first posting so please forgive me if I make any silly mistakes !
I have a SQL query which takes two parameters (two dates), entered dynamically by the user and returns all rows within that date range from the database (oracle).
Is it possible to design a spreedsheet that allows the user to enter these two dates and then fetch the rows from the database (using ODBC of course) ?
If yes then I would be greatfull for some tips on how to do this .
Many Thanks for your help.
Javid

Reply | Quote

Replies

Welcome to Woody’s Lounge!

You can use Data | Import External Data | New Database Query to retrieve data from an external source such as an Oracle database.
You can then specify parameters to be taken from cells on the worksheet: see Customize a parameter query and expand the section ‘Use data from a cell as a parameter value’.

Reply | Quote

Hans,
Thank you for the quick response !
I have had a look at this but for some reason the Query Parameters button in the External Data toolbar is grayed out ?!?
Would you know why ?
Many Thanks ,
Javid

Reply | Quote

Javid,

You will probably have to define the parameters in MS Query (the application that appears when you select New Database Query) instead of in Oracle.
Make sure that View | Criteria is ticked, and specify the parameter(s) in the Criteria by entering a prompt between square brackets [ ].
When you show the data in Excel, the Query Parameters button on the External Data toolbar should be enabled.
You can edit an existing query by clicking Edit Query on this toolbar.

Reply | Quote

Hans,
Thank you for your help with this. I spent some time paying around with the MS Query tool and have a solution now which will do what I wanted. Basically I will create a view in the database which will fech everything then in Excel I will retreive and filter out with parameter as you suggested. I have two final questions though if I may ? I noticed two files in the “My Data Source” directory, mytest.dqy and mytest.dsn , I am guessing the first one is the query , but what is the second one ? My second question is regarding parameters. I have a parameter called say X and say the user enters value 2 for it. I want to use the same value next time X is used on another field. Can this be done ? In other words I don’t want to be prompted for a vlue for X the second time round .
Thank you for all your help .
Javid

Reply | Quote

A .dsn file is a file data source; it contains the information needed to connect to external data.

You’ll only be prompted once if you use exactly the same parameter more than once in a query.

Reply | Quote

Hans,
Just to say a final thank you for all your help, couldn’t have done this without your help.
Kind Regards,
Javid

Reply | Quote

You’re welcome. Glad to have been able to help.

Reply | Quote