Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Sep 2008
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing from Oracle (Excel 2003 SP3)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing from Oracle (Excel 2003 SP3)

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

  3. #3
    New Lounger
    Join Date
    Sep 2008
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing from Oracle (Excel 2003 SP3)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing from Oracle (Excel 2003 SP3)

    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.

  5. #5
    New Lounger
    Join Date
    Sep 2008
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing from Oracle (Excel 2003 SP3)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing from Oracle (Excel 2003 SP3)

    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.

  7. #7
    New Lounger
    Join Date
    Sep 2008
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing from Oracle (Excel 2003 SP3)

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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing from Oracle (Excel 2003 SP3)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •