asked on

Creating a VB module in EXCEL to read Oracle table - Excel 2010 -Windows 7

Experts,

I am trying to create a Workbook that I will be able to run against Oracle tables in Excel. I have seen this done before but was not the one who created the initial workbook. I have found some code on the internet which I am trying to use but I am getting an error.

I am trying to start with a very basic query to see if I can connect and run.

This is what I have, but I am getting an error on the second line.
Dim cn as ADODB.Connection
The error I get is User-defined type not defined

Below is the code I have so far. Can someone tell me what I am doing incorrectly.

Private Function GetRow(user As String) As Variant
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Dim v As Variant

Set cn = New ADODB.Connection
cn.ConnectionString = "Driver={Oracle in OraClient 11g-home1} Dbq=PIMF1;Uid=myID;Pwd=myPassword;"
cn.Open
sql = "Select memberid from claimsods.facclaim where memberid = '123456789';"

Set rs = cn.Execute(sql)


v = rs.Getrows()
GetRow = v

rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Function

Sub a()
Dim user As String
For i = 2 To x
user = ActiveSheeet.Cells(i, 1).Value

        GetRow (user)
        ActiveSheet.Cells(i, 2).Value = GetRow(0)
 Next
End Sub

Open in new window

regmigrant

The error would indicate you have not 'referenced' Microsoft activex data objects.

From VBA go to tools, references and tick the box. Here's a link if you have trouble
Microsoft ActiveX Data Objects x.x Library
http://msdn.microsoft.com/en-us/library/windows/desktop/ms677497(v=vs.85).aspx

If it's already referenced let us know

morinia

ASKER

Thanks, that was the problem. The module compiles, but can you possibly tell me what I need to do to see the code execute to see if I am connecting?

I just want to try to read one record to see if I am connecting and able to read the table.

Any help would be appreciated. I copied the above code, but can't seem to execute it.

I just want to read a record and see the memberid listed in Excel.

regmigrant

well from a purely practical standpoint if the connection fails the code will error when you try and execute it so in the active sheet in excel (I'm assuming you put the above in a module?)

Put a 'break' on line 7 (press F9 whilst on 'Set cn = New ADODB.Connection') and then in a1 type: =getrow("<testuser>") - when you press enter the function should execute and the debug window will pop up with that line highlighted, press F8 to step through line by line. If you get past line 9 'open' then you can be reasonably sure your connection is working, If the SQL you've put it in is ok then line 15 should get the row you expect

The code quoted has a 'sub' which, when executed (developer tab, execute macro) reads the 'user' information from cells 2 to 'x' in column A (x is not defined, you will need to give it a value), executes the function (getrow (user)) passing the content of each cell and puts the return value in column B. - obviously you will need to correct the 'sql' string to return something useful based on the 'user' information passed to the function

morinia

ASKER

regmigrant,

I am sorry but I am not really sure what Sub(A) is doing. Do you have a simple example of a module that is connecting to Oracle?

As I stated I copied this piece from an article on google and I get an error on the sub(a) routine.

Watch : : GetRow(user) : <Format of the initialization string does not conform to the OLE DB specification.> : Variant/Empty : Module1.a

regmigrant

the sub in your example code is just a test harness for the Function at the top which is and example of defining an excel User Defined Excel Function.

To help you up the learning curve try this instead - its the same basic thing but created as a procedure rather than a function so you can run/step through from the IDE

Export the module as it stands and replace all the code with

Sub GetRow()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Dim v As Variant

Set cn = New ADODB.Connection                 ' CN is the connection object

' this is the connection string (go figure) ensure the information is correct for your environment; Uid - user ID, PWD = password; Server = DB
'NB: I've been assuming you actually have an Oracle ODBC driver available, you can check in control panel 'configure ODBC' data sources- and this will give you the correct 'Driver' parameter - this is the default MS one 

cn.ConnectionString = "Driver={Microsoft ODBC for Oracle} ;Uid=myID;Pwd=myPassword; Server=MyOracleServer


cn.Open                                            ' passes the connection string to the ADO driver which will attempt to open the connect to the Oracle Listener you have already made available (did you? - your setup of the ODBC driver should point at the right place!) 


' This is a sql statement that will be executed
sql = "Select memberid from claimsods.facclaim where memberid = '123456789';"

Set rs = cn.Execute(sql)         'this uses the execute method of the object to execute your SQL statement and returns a 'recordset' as an array object


v = rs.Getrows()      'this moves the recordset to the Variant array 'v'

debug.print v          ' outputs the first record in the immediate window

' the rest is garbage collection

rs.Close               
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub

Open in new window

morinia

ASKER

I think there is a problwm with my connection string

cn.ConnectionString = "Driver={Oracle in OraClient 11g-home1} Uid=jdoe;Pwd='jdoe#7';Dbq=tnsname;"

The driver is using the tnsname not the Server (I am running on Windows 7)

This is what I have. My password has a "#" in it. I am getting this (see attached)

"Format of the initialization string does not conform to the OLE DB specification.
visual-basic-error.doc

aikimark

I expected to see a semicolon before "Uid="

ASKER CERTIFIED SOLUTION

regmigrant

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

morinia

ASKER

The syntax was incorrect. When I fixed it, I don't get a syntax error but it doesn't find the Driver.

I get "Data source name not found and no default driver is specified". This is from the ODBC Manager.

The ODBC driver works connecting to Access, SAS and Excel. I am running Windows 7, I don't know if that makes a difference.

I will look at the string closely again. I will aslso try what you said to see if I can find the server name.

This is my string now.

cn.ConnectionString = "Driver={Oracle in OraClient 11g_home1};Dbq=PCDW1;Uid=jdoe;Pwd='jdoe#7';"

Martin Liss

I've requested that this question be closed as follows:

Accepted answer: 0 points for morinia's comment #a39926416

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

morinia

ASKER

The expert gave me great support and help the problem in more environmental. The expert should still get credit for his input.

regmigrant

cheers - hope it worked out