Use Excel 2007 VBA to access Oracle DB

[phudgens]

I'm hoping to find a way to access an Oracle database on a UNIX machine via VBA. Does anyone know if it's possible? I see that there are conversion tools for converting VBA to Python and have heard that Python can access Oracle. Does anyone have any experience along those lines? Thanks for any help.

Paul Hudgens
Denver

 

[Andrzejek]

I have Excel 2003, but I am sure you can do it in 2007, too.

One way:

In your VBA editor, Insert - UserForm
Tools - References...
Select: MicroSoft Active X Data Object x.x library

Then you can do something like:

Dim Cn As ADODB.Connection
Dim rst As ADODB.Recordset

Set Cn = New ADODB.Connection
Cn.ConnectionString = "Driver={Microsoft ODBC for Oracle};" & _
            "SERVER=YourServer;"
Cn.CursorLocation = adUseNone
Cn.Open

Set rst = New ADODB.Recordset
rst.CursorType = adOpenForwardOnly
rst.CursorLocation = adUseClient
rst.LockType = adLockReadOnly

rst.Open "Select * From MyOracleTable", Cn
[green]
'You have your data from ORACLE here
'just loop thru rst recordset
[/green]
rst.Close
Set rst = Nothing

Cn.Close
Set Cn = Nothing

Have fun.

---- Andy

 

[phudgens]

I didn't expect so fast an answer - thanks very much. It will be a few days before I can try this but will let you know how it turns out.

Thanks again,
Paul Hudgens

 

[SkipVought]

Hi,

It can be accomplished much simpler, perhaps even without any VBA, using Data > Get External Data > From Other Sources -- MS Query -- SELECT YOUR DATA SOURCE TNS Name.

Both these approches assumes that you have an ODBC driver configured for Oracle to your TNSName source.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[phudgens]

I'll try them both - Thanks.

PH

 

[SkipVought]

...or

rst.Open "Select * From MyOracleTable", Cn

'You have your data from ORACLE here
'just loop thru rst recordset
[b]
'OR...
'if your resultset is < 1mllion rows paste the whole schmere
dim fld as ADODB.Field, iCol as integer
for each fld in rst.Fields
   iCol=iCol + 1
   activesheet.cells(1, icol).value = fld.name
next 

Activesheet.Cells(2,1).CopyFromRecordset rst
[/b]
rst.Close

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

Paul,

If you give us a little more information of what you are hoping to do, you get better answer since - as you can see - there are many ways to do it.

Have fun.

---- Andy

 

[phudgens]

I have an existing VBA program that reads txt files output by software (using an Oracle db) operating on UNIX machines. This program performs further needed calculations that the software does not do. The process of outputing the necessary txt files from the software, however, is not automated and requires significant effort. I'm hoping to provide users with a list of tables/fields in the Oracle database, and allow them to choose which ones to export. Once the VBA program loads the data into Excel the further calculations can be done. I'm assuming that it is possible to compile a list of all tables/fields within an Oracle db. Once I've got those, I already know how to provide the user with a selectable list of entries. Thanks for any help.

 

[Andrzejek]

You are talking here about text files and Oracle tables/fields. I am lost - which is it? Are you getting some text files from Oracle? Or you are trying to access data in Oracle's tables?

If the latter, you are in luck - I just wrote a little app to check the integrity of the Oracle DB i am working with. This may help you a little.

To get the list of Tables from your schema from Oracle:

SELECT TABLE_NAME
FROM all_tables
WHERE owner = 'S123'       [green]'schema name[/green]
AND TABLE_NAME LIKE 'AB%'  [green]'names that start with AB[/green]
ORDER BY TABLE_NAME

To get the Fields names of a table, you may:

SELECT column_name
FROM ALL_TAB_COLUMNS
WHERE (table_name = 'tblNameFrom1stSelect') 
ORDER BY column_name

Have fun.

---- Andy

 

[phudgens]

My users are running software (called GF) on UNIX machines. GeoFrame accesses an Oracle database. GF, however, does not do certain needed calculations. In order to do those calculations, data must be exported from the Oracle database, using GF, into text files. My VBA program reads those text files and performs the needed calculations. I am hoping to find a way to compile a list of tables/fields in the Oracle database via my VBA program, and then export user selected fields to text files. The code that you provided earlier looks like it will get me most of the way to that objective.

 

[Andrzejek]

I see. It looks to me like you want to by-pass GeoFrame and do all what you need in VBA: grab data from Oracle, create some text files, and perform the needed calculations.

Or you may just grab data from Oracle, performs the needed calculations, and give user the results.

Sounds like fun. Good luck.

Have fun.

---- Andy