Solved

Connect oracle from Excel 2010

Posted on 2012-03-28
2
1,008 Views
Last Modified: 2012-04-21
I want to connect oracle from excel 2010.
My requirement is as below:
My machine has oracle client installed but the excel file needs to be distributed to approx 200 people.Those people does not have oracle client installed.
I want that instead of mentioning service name (which resolves through my tnsnames entry defined in my local machine), connection should use database host name(IP Address) and DB name.
I do not know how to do this.
Pls. give me step by step instruction for doing the same
0
Comment
Question by:surendra_oracle
2 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 300 total points
ID: 37776585
>>Those people does not have oracle client installed.

To connect to an Oracle database, you need an Oracle client.  Ther is one exception that I do not believe Excel can use and that is the JDBC Thin conneciton.

I suggest the Instant Client with ODBC package:

http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html?ssSourceSiteId=ocomen
0
 
LVL 4

Expert Comment

by:ytarkan
ID: 37780678
First of all as slightwv said you "have to" have an oracle client on your excel machines. using service details instead of tnsnames.ora file does not provide you connection without oracle client.

Once you have proper client installed, you can use ADO connection in VBA code in excel macros

Dim adoc As New ADODB.Connection
adoc.Open "Provider=MSDAORA.1;Data Source=" & DSNName & ";User Id=" & User & ";Password=" & PassWord

etc. this part is beyond the scope of Oracle part...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

864 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

18 Experts available now in Live!

Get 1:1 Help Now