[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2014-03-13
14
Medium Priority
?
2,162 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
LVL 46

Expert Comment

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

Accepted Solution

by:
regmigrant earned 2000 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 49

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

656 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