excel-ADO技术-VBA连接mySQL 与orcal

转至http://club.excelhome.net/tree/index-164.html

http://club.excelhome.net/tree/index-164.html

可以使用以下代码在EXCEL上使用VBA连接MYSQL数据库

Dim strconnt As String
  strconnt = ""
  Set connt = New ADODB.Connection
  Dim sevip, Db, user, pwd As String
  '设服务器地址、所连数据，及登录用户密码
  sevip = "localhost"
  Db = “test"
  user = "root"
  pwd = "123456"
  strconnt = "DRIVER={MySql ODBC 5.3 Unicode Driver};SERVER=" & sevip & ";Database=" & Db & ";Uid=" & user & ";Pwd=" & pwd & ";Stmt=set names GBK"
  connt.ConnectionString = strconnt
  connt.Open

有几点值得提一下
网上查到了些参考的代码，驱动基本写的是MySql ODBC 3.51 Driver，但如果你是使用ODBC5.3版本的话，光将3.51改成5.3是不行的，驱动的名称一定要和管理工具-数据源-驱动程序中的名称一致（windows）。安装了ODBC5.3版后，MYSQL的驱动有两个，一个是MySql ODBC 5.3 Unicode Driver，还有一个是MySql ODBC 5.3 ANSI Driver。
我用的版本是EXCEL2013,需要引用MS ADO 2.8
MySql ODBC 是5.3.4版
MySql是5.6.22版

自己也是一知半解，折腾了下才搞明白的，所以写出来给需要的人分享。

_________________________

2.连接orcal

使用Excel通过VBA连接Oracle数据库查询数据，可以实现一键自动快速生成所需的多个标准报表，大大地提高工作效率，节省很多时间。这里将此方法分享给大家，为方便学习，简化了很多功能，仅介绍如何连接数据库并将库表USER_USERS的查询结果保存到Excle表中，先看效果图：

 

通过@微盘分享文件“Excel通过VBA连接Oracle数据库V1.0.rar”，欢迎转发分享  http://t.cn/zO0PnEz

 

大家可以根据业务需求，灵活调整VBA代码，将数据库中的数据输出到标准报表格式之中。

 

接下来介绍实现过程。要连接Oracle数据库，须获取服务名、IP地址、端口、用户名、密码等关键信息，假设Oracle数据库服务器信息如下：

• 服务名：orcl
• IP地址：192.168.2.10
• 端口：1521
• 用户名：data2analysis
• 密码：123456

有了以上信息之后，如何用Excel通过VBA连接Oracle数据库呢？

首先，应安装Oracle客户端（不了解安装过程的朋友请使用Google或百度搜索答案），并用Net Manager进行配置（熟悉的朋友可以跳过下面介绍的配置步骤）。Net Manager的配置步骤如下：点击 创建Net服务名后，出现Net服务名向导。

第一步：输入网络服务名。

第二步：选择协议。

第三步：输入主机名和端口。

第四步：输入服务名。

第五步：测试。

点击完成，结果如下图：

点击上图中的 进行连接测试，利用数据库的用户名和密码，直到连接测试成功！

保存配置后应该就可以正常连接到Oracle数据库了。

下面开始介绍如何用Excel通过VBA连接Oracle数据库。

第一步：按Alt+F11，设计窗体。

第二步：为窗体添加代码。

第三步：插入“查询”模块。

第四步：插入“菜单”模块。

第五步：为ThisWorkbook添加代码。

最后，在“数据化分析”表中添加SQL语句（可自定义），保存Excle文件，重新打开“Excel通过VBA连接Oracle数据库V1.0.xls”文件，即可像本文开头的效果图那样开始使用了。 

 

------------

2012年11月25日更新：

有用户反馈查出来的数据只有一行，我今天抽空检查了一下，因为当时举的例子只有一行，结果循环时出了点小问题，现已对查询模块的代码进行了修正，修正后的代码为：

       For i = 1 To rs.Fields.Count
               ws2.Cells(row, i) = rs.Fields(i - 1).Name
       Next
     
       Do Until rs.EOF
               row = row + 1
               For i = 1 To rs.Fields.Count
                       ws2.Cells(row, i) = rs.Fields(i - 1).Value
               Next
               rs.MoveNext
       Loop
       rs.Close

 

在@微盘 分享"Excel通过VBA连接Oracle数据库V1.1.rar"  http://t.cn/zjbxg32

 

 

欢迎评论和转载，转载请注明来源于@数据化分析 的博客：http://blog.sina.com.cn/data2analysis，除非特别声明，本站采用许可：署名，非商业。多谢支持！

____________________________________

http://club.excelhome.net/forum.php?mod=viewthread&tid=1126917&extra=page%3D1

ORACLE取数工具

一、起因

      在实施ORACLE ERP的过程中，经常会要将一些设置、初始化静态数据、初始化动态数据导出到EXCEL中发给用户进行核对和确认，或者一些简单报表的数据查询，通常的做法是在PLSQL中写脚本将数据导出，如果是多份数据，还需要导出到EXCEL中进行合并，这种做法虽然无可厚非，但总有一种不断做重复工作的感觉，每个项目都要做同样数据查询、导出，何不将查询脚本存放在某个固定地方，需要导出数据时，点一个按钮就能直接将多份数据导出到EXCEL中，既然有这样的想法，就有将其实现的必要。

    首先在网上查了一些资料和模板，发现EXCEL是可以用VBA直接从ORACLE取数的，特别要感谢EXCELHOME论坛的数据化分析网友的帖子（http://club.excelhome.net/thread-856303-1-1.html Excel通过VBA连接Oracle数据库），里面的模板有了链接并查询数据库的代码，这是此工具的基础。

      但是光链接并查询数据库是没有用的，还要利用EXCEL的VBA进行批量化处理，做到一键完成所有数据的查询和导出，否则和在PLSQL中查询数据就没有什么区别了。经过一个月断断续续，终于搞出了这个取数工具。其中一些功能还在完善中，有不少错漏，期待高手指正。

二、前提

1、已经安装PLSQL、ORACLE客户端，并能用PLSQL查询数据。

2、已经通过ODBC数据源管理器建立了与ORACLE的连接。具体连接方法，用百度搜索“EXCEL链接ORACLE”即可。

三、适用对象

需要从ORACLE数据库后台取数并到导出到EXCEL的人，如ORACLE技术顾问、功能顾问等。

二、原理及功能说明

1、首先在ORACLELINKDATA.xlsx的LINK表中设置连接数据库的名称、用户名、密码；

2、在ORACLELINKDATA.xlsx的LINK表后面自由添加表格，用于存放脚本集，比如可以根据不同的项目创建不同的脚本集，也可以根据不同EBS版本创建不同的脚本集；

3、测试脚本是否可用，在PLSQL中是否能查询出数据并且数据量不大（海量数据不适合导出到EXCEL，容易导致EXCEL无响应），在使用的过程中会出现在PLSQL中能查询出数据，但EXCEL还是无相应，需要将脚本最后的的分号去掉，或者耐心等待脚本执行完成。

4、“查询”按钮功能。链接数据库并根据选择的单个脚本进行查询，将查询的数据直接导出至EXCEL特定的位置（本表首格、新表首格、选中位置、窗体）。

5、“批查”按钮功能。链接数据库并根据选择的多个脚本进行查询，将查询的数据批量导出至EXCEL的新表首格，每个脚本导出的数据会导出至一个表格。

6、其他特性。

   （1）点批查之后，选择相关相关的脚本有单选、多选两种方式，在列表框中双击可以添加和移除项目；

   （2）选择数据库，用户和密码自动填充；

   （3）双击脚本列表框内部，可以弹出脚本对应的代码；

   （4）在弹出的脚本代码中可以直接修改，并保存至ORACLELINKDATA.xlsx对应的表中；

   （5）在脚本查看器中可以新增脚本并保存；

   （6）查询前需要检测脚本是否可用，双击颜色按钮可以改变脚本的可用性；

   （7）在输出选项中设置可以限定脚本查询的记录数量，防止数据量太大导致EXCEL卡死；

   （8）在表格中右键点击任意单元格，会出现ORACLE取数工具的菜单。

三、功能及操作截图

点“DATA”打开ORACLELINKDATA.xlsx

在link表中维护数据库用户名、密码、数据库名称，在H1：N2区域维护默认值

在link后面自由添加表格，存放脚本，并标示脚本是否已经测试通过

打开ORACLE取数工具后，可以看到用户名、密码、数据库已经自动填入，是因为在link表中维护了默认值，在脚本运行完成后，会在将选择的值记录到默认值处，下次运行取数工具时，将会按上次运行的参数进行查询

脚本集对应LINK表后的表名，脚本对应表中存放的脚本，可用性决定脚本是否可用，目的是先确定好脚本已经测试通过再运行，防止在EXCEL卡死

选项用于限定数据输出的条数和存放的位置

点“批查”按钮之后，将会出现选择脚本的界面，有点类似发邮件时选择联系人，双击或点击方向按钮，用于添加和移除项目。选择脚本也可以多选，需要先勾选多选复选框。

双击脚本框内部

弹出脚本编辑器，可以查看和修改脚本

点“查询”按钮

数据导出至指定位置

点“批查”按钮，选择多个脚本，点“开始批查”

每个脚本导出至单独的表

当脚本未通过测试时，可用性按钮是红色的，点查询不会查询出数据

双击“可用性”按钮，脚本变为黄色，点查询就会查询出数据

点“加载项”

点“浏览”，添加可用加载宏，之后打开任意EXCEL文件，都可以使用此工具

  ORACLE取数工具.zip (1.53 MB, 下载次数: 235)