使用“ODBC”将Excel VBA连接到Oracle数据库 [英] Connecting Excel VBA to oracle DB using 'ODBC'
问题描述
基本上我在一家软件公司工作。
我的客户端有一个Oracle数据库。
Basically i work in a software company. My client has a Oracle database.
我通过SQL Developer访问该数据库。我们有一个虚拟桌面，我们有所有的客户端应用程序， SQL Plus等。
I do access that database through SQL Developer.We have a virtual desktop too -inside which we have all client applications,SQL Plus etc..!!
现在，我们团队中的另一个人创建了一个excel宏，其中如果您提供用户名密码和SQL查询，它将连接到oracle数据库，并为您获取记录，并将其保存在虚拟桌面本身的excel表中。
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.
现在我想做类似的事情，但我不能散热我如何连接我的excel VBA到数据库。因为他的宏能够连接到数据库，我很确定所有必需的驱动程序安装在我们的虚拟机（桌面）。
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).
我可以看到Oracle - OraClient11g_home1在我的虚拟机中的所有程序。我可以看到SQL plus和所有这些应用程序。提供所有这些细节，因为我在其他帖子中读到这些东西可能需要连接到DB。
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.
在SQL Devloper如果我去连接属性，它显示我的网络别名为xxxxxx（我替换为xx）.Connection类型为'TNS'，角色为'Default'.我知道我的用户名和密码。
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.
注意：在Excel中，当我去新的连接数据库选项，我没有看到Oracle驱动程序:(我只能看到Microsoft驱动程序为Oracle。
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".
这些都是有人可以帮助我连接到Database.Once有人可以帮助我做一些我可以在我的宏中做的事情。）
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.:)
推荐答案
我刚刚测试它按照以下步骤：
I just tested it following these steps:
- Excel>数据>获取外部数据>数据连接向导
- 从数据连接向导中选择其他/高级，然后点击下一步
- 在机器数据源选项卡上，单击新
- 选择Oracle驱动程序
- Excel > Data > Get External Data > From Other Sources > From Data Connection Wizard
- From Data Connection Wizard, select "Other/Advanced" and click Next
- On the Connection tab, create new connection string by click on Build
- On Machine Data Source tab, click New
- Select Oracle driver
- 输入正确的TNS服务名称和您的用户ID。单击测试连接并输入您的密码。
这篇关于使用“ODBC”将Excel VBA连接到Oracle数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
