Re: Excel (dataquery) reading Oracle database (VAX)

milapier_at_ge.hydro.qc.ca (Michel Lapierre) wrote:

>I need to use Excel to get information from tables and views (if
>possible) with Excel Dataquery. We do have Developer/2000
>and Sql*net for TCP/IP to access the VAX.

I have recently used Excel to produce reports that required graphics.

I used a PLSQL procedure to carry out the complicated SQL bits and wrote the results to a temporary table. I then used Excel to draw the data from the temporary table. This avoided the necessity of persuading Excel to use database procedures, etc.

I found the best way to write the Excel queries was to develop the query witht he macro recorder turned on. I did not save the query in the spreadsheet, just preserved the macro. I then doctored the macro (ie the Visual BASIC the recorder produced) to allow connection to more than one database, different user names and so on.

I used DDE commands in Oracle forms to fire up Excel. I was not able to use DDE commands to pass data from Oracle into Excel directly. I found that after starting Excel, Oracle forms would not wait for Excel to load a spreadsheet and insisted on sending the DDE stuff into a void.

Hope this is of some use.  

   /\/\     :  Ian Searle
  / / /\    :  Consultant
 / / /  \   :  BHP Information Technology, Melbourne
/ / / /\ \  :  Level 31, Bourke Place, 600 Bourke St, Melbourne
\ \/ / / /  :  Phone    +61-3-609-3158
 \  / / /   :  E-mail (private)  searlei_at_werple.mira.net.au
  \/\/\/    :  E-mail (business)  searle.ian.i_at_bhp.com.au