Solved

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

Posted on 2014-03-13
14
2,072 Views
Last Modified: 2014-04-06
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

0
Comment
Question by:morinia
14 Comments
 
LVL 19

Expert Comment

by:regmigrant
ID: 39926334
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
0
 

Author Comment

by:morinia
ID: 39926416
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.
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39926498
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
0
 

Author Comment

by:morinia
ID: 39926656
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
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39926805
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

0
 

Author Comment

by:morinia
ID: 39926932
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
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

Expert Comment

by:aikimark
ID: 39926964
I expected to see a semicolon before "Uid="
0
 
LVL 19

Accepted Solution

by:
regmigrant earned 500 total points
ID: 39926988
- If its not a typo :)

try the following from a cmd prompt in the oracle folder (or wherever tnsping is):

TNSPING oracleserverIP/databasename

this should give you output with all the relevant information to plug in to the string

If that doesn't work there's only a few things I can suggest without a better understanding of the local environment

The Oracle server side is not setup to accept the odbc connection (ie: server side driver not there/blocked)

The local driver is not configured properly (Oracle client not properly installed, odbc driver not configured)

Try another Oracle connector -such as the MS one

If you have Access try setting up a link using the Jet database driver which might be easier to troubleshoot


or you can give this forum a shot:
http://www.connectionstrings.com/oracle
0
 

Author Comment

by:morinia
ID: 39927160
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';"
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39979184
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.
0
 

Author Closing Comment

by:morinia
ID: 39979185
The expert gave me great support and help the problem in more environmental.  The expert should still get credit for his input.
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39982096
cheers - hope it worked out
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now