2

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.:)

Improve this question
3
  • Here is a step by step example: blog.mclaughlinsoftware.com/microsoft-excel/… Commented May 20, 2016 at 6:52
  • Possible duplicate of How can I connect ORACLE from EXCEL with JDBC? Commented May 20, 2016 at 6:54
  • Hi blckbird thanks for sharing link:) The link tells me to select oracle driver . but i dont see oracle driver in the list . I can see "Microsoft OLE DB for oracle".But even that doesnot work :( Commented May 20, 2016 at 7:22

1 Answer 1

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for your idea, but ddnt work:(. I dont see any option to create new connection !!In the connection tab i dont see an option as new :( I can see fields like "Use data Source","Use connectionstring" etc etc. Can you please explain how to create new connection?
Added some screenshots.. hope this helps now. If you dont have orcale driver you need to install it. Search for it in google.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.