山东喵

导航

统计
 

本文参考【东围居士】的cnblog博文  Excel、VBA与MySQL交互  在自己机器上调试成功,把调试中遇到的问题一并写出了。

本文主要涉及:

  1. VBA中的MySQL环境配置
  2. VBA连接MySQL数据库
  3. VBA读写MySQL数据
  4. 在Excel中连接MySQL数据库及数据读写
  5. (2019.11.07更新)常见问题答疑
  6. (2019.11.11更新)常见问题答疑

系统环境:

  • Windows 7 64bit
  • Excel 2016 32bit
  • WAMP(3.0.6 32bit)集成的MySQL版本为5.7.14
  • (2019.08.02更新) 本文章在windows10 企业版 64bit下测试通过

1. VBA连接MySQL前的环境配置

如果想连接本地数据库,必须先安装MySQL服务。可以选择使用官方安装包,或者使用PHP集成环境中的数据库都可以(windows平台上的有wamp或者phpstudy)。

我这里使用的是内网其他电脑上的MySQL数据库,这就需要在数据库里开启远程访问。

1.1 启用MySQL的局域网访问

想让局域网中的所有机器都能连接MySQL数据库,首先要给MySQL开启远程连接的功能,在MySQL服务器控制台上执行MySQL命令:

1 grant all privileges on *.* to root@"%" identified by 'abc' with grant option;  
2 flush privileges;

其中上面两行代码的意思是给从任意ip地址连接的用户名为root,密码为abc的用户赋予所有的权限。其中的"%"为任意的ip地址,如果想设为特定的值也可以设定为特定的值(以通配符%的内容增加主机/IP地址,也可以直接增加IP地址)

例如:

1 grant all privileges on *.* to root@"192.168.1.1" identified by 'abc' with grant option;  
2 flush privileges;

这里就是指 192.168.1.1这个IP的机器可以使用 root /abc 这个账户远程访问MySQL数据库

PS:数据连接工具推荐使用Navicat,可以同时连接不同的数据库,非常方便。

我常用的是11.0.16版本   下载地址   提取码: s5kt

1.2 启用脚本支持

数据库配置好之后,在Excel这边,需要先在VBE中启动数据库连接支持。按下Alt+F11打开VBE,在菜单栏选择“工具”-“引用”,在弹出的引用窗口中,找到"Microsoft ActiveX Data Objects 6.1 Library"和"Microsoft ActiveX Data Objects Recordset 2.8 Library",把前面的框勾选上,点击确定即可。 (如果不是这两个版本,则选择一个版本号最高的勾选即可)

1.3 安装MySQLODBC 连接服务

下载地址:Download Connector/ODBC

选择操作系统及系统版本,下载对应的MSI文件安装即可。
这里应当注意的是,在下载安装ODBC连接器时,要选择和你的数据库系统相同位数的版本,而不是系统版本,否则安装完后依然会提示找不到数据库驱动。
注意:在64bit的系统上装的32bit的connector,装完后在ODBC数据源里看不到MySQL Driver,这时需要把64位的也装上才能看到MySQL Driver

但是!如果在64bit的系统上安装64bit的connector,数据库版本32bit,虽然可以看到ODBC的数据源了,但是在excel中连接还是提示找不到驱动,这时装上32bit的connector才成功

所以说 数据库、操作系统、连接工具这些统一用一种位数的多么重要。。。

PS:如果安装的时候报错,你可能需要安装 VC++ 2015 Redistributable     微软官方下载地址  选择对应自己安装的ODBC连接器版本的下载即可

1.4 添加ODBC数据源

打开“控制面板”-“管理工具”-“ODBC 数据源”

在弹出的窗口中的“用户DSN”选项卡右侧,选择“添加”,在新数据库源中会出现两个MySQL驱动,分别为“MySQL ODBC 8.0 ANSI Driver”及"MySQL ODBC 8.0 Unicode Driver",

很明显两者的区别在于编码标准。我选择的是Unicode版本。选中其中一个,点完成即可。

点击完成后弹出配置界面,关于界面的说明如下:

Data Source Name: 连接名称,会显示在之前的“ODBC数据源管理器“的列表中;

TCP/IP Server: 服务器地址,如果是内网/外网,需要填写对应的IP地址。如果是本机则填写 127.0.0.1;

User: 登录用户名,这里如同我在1.1中设置的,填写 root;

Password: 登录密码,这里如同我在1.1中设置的,填写 abc;

Database:这里需要选择 数据库(一个服务器上可能有多个数据库),根据自己的需要选择一个就可以。

填写完毕后,推荐点击【test】按钮测试一下连接是否正常,如果有问题的话,需要重新检查一下1.1中的局域网访问设置,如果是本地服务器的话,可能是数据库未启动。

1.2和1.3的配置顺序可以随意,但1.4必须在1.3之后,否则在ODBC数据源中添加新数据源时,是找不到MySQL选项的。


2. VBA连接MySQL

在按照上述步骤配置了环境支持后,就可以在VBA中使用代码连接MySQL了。

首先需定义连接对象:

Dim conn as ADODB.Connection
Set conn = new ADODB.Connection

这里也可以简写为:

Dim con As New ADODB.Connection

连接数据库

conn.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};Server=192.168.1 139;DB=test;UID=root;PWD=abc;OPTION=3;"
conn.Open

连接字符串ConnectionString中的各个参数应该很明了,就不一一解释了。最后一个OPTION,按MySQL官方的说法是用于指定ODBC Connector的工作方式的,但是在他们官方文档中并没有找到有哪个选项是的值对应是3的。所以这里只有照写了。

上一段代码也可以简写为

con.Open "Driver={MySQL ODBC 8.0 Unicode Driver};Server=192.168.1.139;DB=test;UID=root;PWD=abc;OPTION=3;"

这里注意,Driver变量的值是必须要和数据源中添加的新数据源一致的,否则会提示找不到数据源。

至此,数据库连接成功!

可以使用连接对象的State属性和Version属性查看数据库状态和版本(检查是否连接成功)

MsgBox("连接成功!" & vbCrLf & "数据库状态:" & con.State & vbCrLf & "数据库版本:" & con.Version)

最后关闭数据库连接

con.Close
Set con = Nothing

整个过程的完整代码如下:

Sub 连接MySQL数据库()
'1. 引用ADO工具
'2. 创建连接对象
Dim con As New ADODB.Connection
'3. 建立数据库的连接
con.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};Server=192.168.1.139;DB=test;UID=root;PWD=abc;OPTION=3;"
con.Open
MsgBox ("连接成功!" & vbCrLf & "数据库状态:" & con.State & vbCrLf & "数据库版本:" & con.Version)
con.Close
Set con = Nothing
End Sub

 


3. VBA读写MySQL数据表

3.1 读取MySQL数据到Excel

代码如下:

Sub linkMySQL()
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
'配置连接串
    conn.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};Server=192.168.1.139;DB=test;UID=root;PWD=abc;OPTION=3;"
    conn.Open
'从test数据库的YGXM表中取出所有数据
    rs.Open "select * from `YGXM`", conn
'设置表头
    Range("A1:B1").Value = Array("ID", "Name")
'将数据输出到工作表
    Range("A2").CopyFromRecordset rs
'关闭连接
    rs.Close: Set rs = Nothing
    conn.Close: Set conn = Nothing
End Sub

相比前面的代码,以上代码多了 ADODB.Recordset 和 rs.Open,ADODB.Recordset 用于执行SQL语句并接收查询语句返回的结果集。

这里需要提一下的是,在VBA中执行SQL语句有两种方式,其一是使用连接对象执行: conn.Execute ,其第一个参数就是SQL语句;另一种则使用结果集对象执行: rs.Open ,这种方式有两个必要参数,分别是SQL语句和连接对象,如上例中的 rs.Open "select * from `test`", conn 。

接下来的两行Range是用于把查询结果复制到Excel表格中的。

3.2 写入数据到MySQL

其实写入数据,只需要把上例中的SQL语句改成 UPDATE 或者 INSERT 即可,就不多说了。


4. 在Excel中直接操作MySQL

MySQL推出了一个Excel插件,用于直接在Excel操作MySQL数据库。

首先需要下载:Download MySQL for Excel

下载完运行安装即可。

然后打开(重启)Excel,打开时可能会询问是否添加该插件。打开后,在菜单栏点开“数据”,即可在右侧看到有个MySQL for Excel的东西。点开它,就会出现本地的MySQL数据库。

MySQL for Excel

试用了一下,还算方便

 

工具推荐: VBA自动排版工具   提取码: qjsq   安装之后,右键选择“智能排版”——》“工程智能排版” 即可

 


5. 常见问题答疑

 

Q1:我在测试的时候,运行到打开数据库连接这一行,报自动化错误,这是怎么回事?如下图

方法:同时安装32bit和64bit的ODBC驱动

A1:这是由于ODBC数据源的问题,经过我测试,当系统64bit/excel64bit时,如果装的odbc数据源为32bit版,就会报错,此时安装一个64bit版odbc驱动并设置,即可解决问题。

     PS:我发现安装完32bit和64bit驱动后,貌似把设置好的odbc驱动删掉仍然可以正常连接。。。太迷了

 

 

Q2:我在测试时,运行到打开数据库连接这一行,报系统错误,这是啥情况?如下图

方法:检查服务器IP地址是否写对

A2:这可能是由于数据库IP地址设置错误导致,假如你的数据库是在本地上的,那么在连接串中不需要输入自己机器的IP地址,而是需要输入127.0.0.1或者localhost

如下例所示:

 Cnstring = "Driver={MySQL ODBC 8.0 Unicode Driver};server=127.0.0.1;db=zhixiao_acct;uid=root;pwd=;option=3" 

或者

 Cnstring = "Driver={MySQL ODBC 8.0 Unicode Driver};server=localhost;db=zhixiao_acct;uid=root;pwd=;option=3" 

特别注意:

这种失误也有可能报如Q1所说的自动化错误,届时可按A1所说方法处理试试。

 

 

 

Q3:我已经按你说的设置好了一切 ,但是运行到打开数据库连接这一行,还是报自动化错误,这是啥情况?

 

方法:检查数据库是否需要设置端口号

 

A3:这可能是由于数据库只开放了指定端口导致,假如你的数据库还需要设置段口号,那么在连接串中需要还需要增加 port=XXXX

 

如下例所示:

 

 Cnstring = "Driver={MySQL ODBC 8.0 Unicode Driver};server=127.0.0.1;port=3306;db=zhixiao_acct;uid=root;pwd=;option=3" 

 

posted on 2018-10-20 11:21  山东喵  阅读(23418)  评论(18编辑  收藏