VBA to write Excel data to Oracle table

  • Thread starter Thread starter AP
  • Start date Start date
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.
 
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:
Back
Top