Macros Issue 1

[teja1234]

Hi All,

I am very new to VBA excel Macros, i have a requerment like in need to write a macro script which exports excel sheets fron local file to oracle database. Please can any one help me by give idea how to achive this, if any one share code for this is very helpful.
It is very urgent please help me

Thanks in advance
Teja

 

[kjv1611]

Post here:
forum705

And you need to clarify your question. Your asking in an Access Database forum about exporting Excel Spreadsheets? So, do you want to export from Access to Excel, and then from Excel to Oracle? Or do you need to go direct from Access 2 Oracle? Or is Access in the picture at all?

 

[teja1234]

Thanks for you reply, But i don't want to export from Access to Excel. It is already ther in excel sheets just want to export to Oracle using macros.

Regards
Teja

 

[kjv1611]

Hmm, I've not messed directly with Oracle databases so far, but my initial guess is that you'd have to export to a text file from within Excel, and then with Oracle, run whatever import procedure there is there, since it's a SQL database.

So, for the Excel side, you'd just need to look at the command, DoCmd.TransferText.

Then for the Oracle side of things, you'd probably be better posting in one of these forums:

http://www.tek-tips.com/forumsearch.cfm?SearchString=oracle&type=Forum&sub=Go

Specifically, I'd guess (not 100% sure, as there is a fairly long list there), I'd go with:
forum259

Then again, looking at the amount of activity on those forums, it looks like you're more likely to get a response if you post in the one related to the specific release of Oracle that you're working with.. So one of these:
Oracle: Oracle release - 8i and earlier
Oracle: Oracle release - 9i
Oracle: Oracle release 10g
Oracle: Oracle release 11g

 

[jsteph]

Teja,
In general there are a few ways to go about this...I'm just giving high-level examples here...

First and probably the most efficient is to use excel vba to "save as" the excel sheet to a csv or tab-delim file, then call the oracle loader to load this. This can all be done within a macro, you can have an odbc connection to oracle and run the command for the loader passing in the path where you just created the file.

Alternatively, you can use odbc with ADO or DAO and iterate through the rows in the sheet and insert records via either an insert into(oraField, oraField,etc) values(cell, cell, etc) statement or something like that.
--Jim

 

[teja1234]

Thanks kjv1611/jsteph for your reply,

jsteph as you explain me about how my issue can be solve, i am very new to macro so if you can pass macro code for this it will be very helpful.

Thanks once again
Teja