Re: Excel Function - VBA & Oracle SQL query

In article <31BC62B6.58D2_at_hotliquid.com>, Rob Weeks <rweeks_at_hotliquid.com> wrote:

> I kind of asked this before but....
>
> I have a report that I need to run in Excel that will contain about 90 or so
> separate SQL queries to ORACLE. There is a worksheet function in the
 ODBC addin
> that works, but it looks like it has to log into ORACLE each time that
 it executes
> a query. Is there either: 1) a commercial product that will do this
 (CI-Link has
> something that would work if my server ran on Unix instead of NT), or..
 2) has
> anyone done anything like this in VBA using either ODBC or Oracle
 Objects for OLE.
>
> Thanks in advance...
>
> Rob Weeks
> rweeks_at_kodak.com
> rweeks_at_hotliquid.com
I would recommend using the Visual Basic ODBC commands to connect into a database. These are a little more flexible than the worksheet ones. (You need at least Excel v5.0). If you are using Excel 95, Microsoft changed over to using DAO (Data Access Objects), and these are even more flexible.

The Visual Basic functions definitely allow you to open a connection and keep it open to execute numerous SQL commands. (Performance will take too big a hit if you have to open a new database session each time).

In Excel 5, the ODBC commands all start with SQL_. You can do a search in the online help for them. There are examples on how to use the commands as well. If you need additional help, I can email you sample code.

My advice is to stay stay away from the worksheet function and use Visual Basic. It is easy to use and much easier to maintain than coding calls directly into worksheet cells.

-- 
Davis W. Edwards