Have you ever had a need to take and excel spreadsheet and load its data into an oracle table. I must say I had this very need a few years back and came upon a small DLL that saved my butt.Welcome to my OO4O tutorial. There is going to be a prerequisite to this tutorial.
1- You must have a version or oracle install on your desktop. Now I understand oracle is expensive I wouldn’t suggest you go buying an oracle database. On the contrary you can download a miniature version of an oracle database from
here. It pretty much has all the belts and whistles that a full version has but much less space. It’s great for testing and tinkering if you don’t want to spoil a production database.
2-You obviously must have excel installed in on your computer to get this to work. A prior knowledge of VBA (visual Basic for applications) is helpful but not mandatory.
3-A little motivation.
The first thing we need to do is download something oracle refers to as ODAC it stands for Oracle Data Access Components. You can download these components from this oracles website here.
Once you have downloaded this navigate to your C:\app\user\product\ 11.1.0\client_1\bin Warning depending on where you choose to install your oracle instance you might end up with a different location then mine but it should be pretty simple to find the dll. Something else to look out for is the “11.1.0\client_1\” in my path above. This can all be different by the time you try this. Don’t be alarmed
Ok we now have everything we need to get started let’s get into some code. First off lets create a simple table. I use sql developer to perform database queries is a free tool from oracle but this is not mandatory. You can use toad or even eclipse with a plugin to the database that’s up to you.
CREATE TABLE X_CEL (FIELD1 VARCHAR2(10),
FIELD2 VARCHAR2(10));
1-Open and excel workbook. Your screen should look like this.
(Note: To make images bigger just click the image.)
Now click on the developer tab.
Your screen should now look like so
Click on the visual basic button
Ok hopefully if you are still following this tutorial and you are not falling to sleep on me. If you are falling to sleep get up jump around and come back when you have a little energy… Ok your back lets move on.
Create a module like such
Now click on tools and references
You will get a pop up that looks like such. Click on the Browse button.choose your references
Once you click on the browse button you get to navigate to reference.So navigate to C:\app\user\product\ 11.1.0\client_1\bin and choose oip11.tlb otherwise known as the oracle inProc Server and continue to choose OraOLEDB11.tlb, and OO4OADDIN.DLL
Houston you are now ready to start talking to oracle!!!
Let’s create a sub
Sub InsertTable()
Dim sid As String
Dim UserName As String
Dim pass As String
sid = "XE"
UserName = "Miguel83"
pass = "XXXX" '-- your password i'm not telling you mine.
'this will stop the screen from flickering when the sub is running
Application.ScreenUpdating = False
' create the oracle connection objects.
Set OBJSESSION = CreateObject("OracleInProcServer.XOraSession")
Set OBJDATABASE = OBJSESSION.OpenDatabase(sid, UserName & "/" & pass, 0&)
'Use the object method and property to add a parameter.
OBJDATABASE.Parameters.Add "field1", "", 1
LROWS = Columns(1).SpecialCells(xlCellTypeConstants, 23).Cells.Count
For lngrow = 1 To LROWS
MyColumnA = Range("A" & CStr(lngrow)).Value
If MyColumnA = "" Then Exit For
With OBJDATABASE
.Parameters("field1").Value = MyColumnA
.ExecuteSQL "insert into miguel83.X_CEL (FIELD1)" & _
"values(:FIELD1)"
End With
Next
Application.ScreenUpdating = True
End Sub
Put some values in column A and let her run. You should end up with a column full of A’s on your oracle table.
