VBA to write Excel data to Oracle table

A

AP

Is it possible to use VBA to grab a value from an Excel spreadsheet
and to then write to an Oracle database table ?

Any example code would be very much appreciated.
 
Joined
Jun 15, 2011
Messages
1
Reaction score
0
Use VBA to write Excel data to an Oracle table

It's possible to write from Excel data to an Oracle database using OO4O (Oracle Objects for OLE) which you can download, for free, from: http://www.oracle.com/technetwork/database/windows/downloads/utilsoft-101505.html.

Once you have installed OO4O, you could use VBA like this:

Sub uploadListOfItems()
'Oracle connection variables
Dim dbUserName As String
Dim dbPassword As String
dbUserName = "ORACLE_USERNAME"
dbPassword = "ORACLE_PASSWORD"

Dim dbDSN As String
dbDSN = "MY_TNS_ALIAS"

'Create and Set Session / Create Dynaset = Column Names
Dim OraSession As Object
Set OraSession = CreateObject("OracleInProcServer.XOraSession")

Dim OraDatabase As Object
Set OraDatabase = OraSession.OpenDatabase("" & dbDSN & "", "" & dbUserName & " / " & dbPassword & "", 0&)

Dim Oradynaset As Object
Set Oradynaset = OraDatabase.DBCreateDynaset("SELECT * FROM MY_ORACLE_TABLE", 0&)

'Upload data variables
Dim site As String
Dim item As String

'Initialize upload
ActiveSheet.Activate
Range("A2").Select
order_id = 1

'Iterate through each cell and upload to MY_ORACLE_TABLE
Do Until Selection.Value = ""
site = ActiveCell.Value
item = ActiveCell.Offset(0, 1).Value

'Add new record
Oradynaset.AddNew
Oradynaset.Fields("USERNAME").Value = Application.UserName
Oradynaset.Fields("SITE").Value = site
Oradynaset.Fields("ITEM").Value = item
Oradynaset.Update

Selection.Offset(1, 0).Select

order_id = order_id + 1
Loop
End Sub
 
Last edited:

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top