Re: How can I put oracle data in excel worksheet

There seem to be many ways of doing this. I'm sure that the way I do it isn't the best but here goes.

1. In ODBC Administrator in Control Panel - setup an ODBC data source to point at your oracle database
2. In VBA editor select tools references and an add a sheet called XLODBC.XLA this gives you access to the ODBC functions.
3. Then in your VBA cod something like
   'Open Channel to database
   chan = SQLOpen("DSN=datasource;DBQ=sqlnet2name;UID=myuser;PWD=mypass", , 4)
   'Setup SQL String
   Q$ = "select column_name from table_name
   'Execute Query
   SQLExecQuery chan, Q$
   'Setup location for output
   Set output = Worksheets("Sheet1").Range("A1")
   'Retrieve data from query to output location
   SQLRetrieve chan, output, , , False
   'Close channel to database
   SQLClose chan

If you lookup the various ODBC functions like SQLOpen and SQLRetrieve you will find that there are many parameters that you can play with. If you are using Office 97 you may find that help is not available for these functions because the help file is incorrectly named. Just open file VBAXL8.HLP they're all documented in there.

There are other ways to access Oracle data which I have used in VB but not in VBA. They are DAO (Data Access Object), RDO (Remote Data Objects) and soon to be ADO (Advanced Data Objects????). But my favorite way in VB is using
OO4O (Oracle Objects for OLE) This seems much easier to code, the performance in comparable to the other methods and the error messages are much much much better than the others.

If you do manage to get OO4O working with Excel then I'd like to see an example. I would investigate it but I'm a bit busy

I hope that this has been some help for help with DAO and RDO search the microsoft web, they have some good stuff on it.

Best wishes

Paul Anthony
anthony_at_dial.pipex.com Received on Thu Mar 19 1998 - 00:00:00 CST