CODEWITHSUNDEEP
databaseexceloracle-databasevbaodbc
Karthik
Karthik

Reputation: 53

Connecting Excel VBA to oracle DB using 'ODBC'

Basically i work in a software company. My client has a Oracle database.

I do access that database through SQL Developer.We have a virtual desktop too -inside which we have all client applications,SQL Plus etc..!!

Now one other person in my team has created a excel macro in which if you give your username password and your SQL query, it will connect to the oracle database and fetch the records for you and will save it in an excel sheet in the virtual desktop itself.

Now i want to do something similar but i am not able to fins how i connect my excel VBA to Database. As his macro is able to connect to the database i am pretty sure all necesaary drivers are installed in our virtual machine(desktop).

I can see "Oracle - OraClient11g_home1" in All programs in my virtual machine.I can see SQL plus and all those apps. Giving all these details because i read in other posts that these things may be required to connect to DB.

In SQL Devloper if i go to connection properties it shows me the Network Alias name as "xxxxxx"(I have replaced by xx).Connection type as 'TNS' and role as 'Default'.I know my username and password.

Note: In Excel when i go to new connect database option, i dont see Oracle Driver at all:( I can see only "Microsoft Driver for Oracle".

These are all the details i know . Can someone help me with connecting to Database.Once someone can help me do it rest everything i can do in my macro.:)

Upvotes: 2

Views: 20826

Answers (1)

timbmg
timbmg

Reputation: 3328

I just tested it following these steps:

  1. Excel > Data > Get External Data > From Other Sources > From Data Connection Wizard enter image description here
  2. From Data Connection Wizard, select "Other/Advanced" and click Next enter image description here
  3. On the Connection tab, create new connection string by click on Build enter image description here
  4. On Machine Data Source tab, click New enter image description here
  5. Select Oracle driver

enter image description here

  1. Enter corret "TNS Service Name" and your user id. Click test connection and enter your password. enter image description here

Upvotes: 1

Related Questions