Accessing oracle database through excel cell

[Guest]

Hi,
The problem I am trying to solve is acessing an oracle database and excecute
a query through an excel cell.

The query would be as follows: select count(XXX_ID) from XXX.YYY_YYY where
AAA in (Excel cell B2) and CCC in (Excel cell G2);

Is is possible to execute such a query, and capture variable info from
another excel cell?

Thanks for any help!

 

[Guest]

You can do Data > GetExternalData > CreateNewQuery > and follow the menus.
You must however, have specific access and Rights to the database or it won't
work.

hth
Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Unfortunately, I can't seem to figure out how to call on values from Excel
cells to insert into the queries written in the Query Wizard.

 

[Guest]

It's not real friendly in that way........you'll have to get into some pretty
intense VBA programming if you want to do much of that..........you might try
going through the steps manually one-time with the Macro Recorder turned on
and see what you get....sometimes it's clear enough to be editable.


hth
Vaya con Dios,
Chuck, CABGx3

 

[Guest]

I'm somewhat comfortable writing the VBA, I just dont know how to use it to
connect to my oracle db

 

[CLR]

Well then, just turn on the Macro Recorder.........Tools > Macros >
RecordNewMacro........... and go through the MSQuery steps of Data >
GetExternalData > CreateNewQuery > and then follow the menus to select your
Oracle Database, etc etc..........if you don't see the selection of Oracle,
then you do not have access to it and need to get the rights/password/etc
from your MIS folks...............

After you get there, turn off the recorder and go to the VBE to see what you
got.........sometimes you can directly replace a fixed value with something
like Range("b3").Value .........................

hth
Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Thanks for the advice, I'll give it a shot

Well then, just turn on the Macro Recorder.........Tools > Macros >
RecordNewMacro........... and go through the MSQuery steps of Data >
GetExternalData > CreateNewQuery > and then follow the menus to select your
Oracle Database, etc etc..........if you don't see the selection of Oracle,
then you do not have access to it and need to get the rights/password/etc
from your MIS folks...............

After you get there, turn off the recorder and go to the VBE to see what you
got.........sometimes you can directly replace a fixed value with something
like Range("b3").Value .........................

hth
Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Okey doke.........good luck, and post back if you still have difficulties
with it.........

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

The QueryMaster file on Debra Dalgleish's website might be a good starting
point for you. The VBA is already done. All you need to do is enter the SQL
(which can reference another cell).

http://www.contextures.com/excelfiles.html#External

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP