Excel、VBA与MySQL交互

QQ叫紫珊

　　本文主要涉及：

• VBA中的MySQL环境配置
  
• VBA连接MySQL数据库
  
• VBA读写MySQL数据
  
• 在Excel中连接MySQL数据库及数据读写
  

　　系统环境：

• Windows 10
  
• Excel 2013
  
• XAMPP。其中集成的数据库是MariaDB 10.1.13（如果不懂MariaDB为何物，可把它当成是MySQL。事实上它的使用和MySQL也几乎完全一致）
  

1. VBA连接MySQL前的环境配置
　　系统中必须先安装有MySQL服务，这个就不多说了。可以选择使用官方安装包，或者使用PHP集成环境中的数据库都可以。

1.1 启用脚本支持
　　在使用前，需要先在VBE中启动数据库连接支持。按下Alt+F11打开VBE，在菜单栏选择“工具”－“引用”，在弹出的引用窗口中，找到"Microsoft ActiveX Data Objects 6.1 Library"和"Microsoft ActiveX Data Objects Recordset 6.0 Library"，把前面的框勾选上，点击确定即可。

1.2 安装MySQL ODBC 连接服务
　　下载地址：Download Connector/ODBC
　　选择操作系统及系统版本，下载对应的MSI文件安装即可。
　　
这里应当注意的是，在下载安装ODBC连接器时，要选择和你的数据库相同位数的版本，而不是系统位数，否则安装完后依然会提示找不到数据库驱动。
　　
（我试过在64bit的系统上装的64bit的connector，装完后连接提示找不到驱动，然后装上32bit的connector才成功）

1.3 添加ODBC数据源
　　打开“控制面板”－“管理工具”－“ODBC 数据源”，在弹出的窗口中的“用户DSN”选项卡右侧，选择“添加”，在新数据库源中会出现两个MySQL驱动，分别为“MySQL ODBC 5.3 ANSI Driver”及"MySQL ODBC 5.3 Unicode Driver"，很明显两者的区别在于编码标准。我选择的是Unicode版本。选中其中一个，点完成即可
　　1.1和1.2的配置顺序可以随意，但1.3必须在1.2之后，否则在ODBC数据源中添加新数据源时，是找不到MySQL选项的。

2. VBA连接MySQL
　　在按照1.1步骤配置了环境支持后，就可以在VBA中使用代码连接MySQL了。
　　先定义连接对象
　　

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

　　连接数据库
　　

conn.ConnectionString = "Driver={MySQL ODBC 5.3 Unicode Driver};Server=localhost;DB=test;UID=root;PWD=root;OPTION=3;"　　
conn.Open
　　

　　连接字符串ConnectionString中的各个参数应该很明了，就不一一解释了。最后一个OPTION，按MySQL官方的说法是用于指定ODBC Connector的工作方式的，但是我在他们官方文档中并没有找到有哪个选项是的值对应是3的。所以这里只有照写了。
　　
这里可以看到，Driver变量的值是必须要和数据源中添加的新数据源一致的，否则会提示找不到数据源。
　　致此，数据库连接成功！
　　可以使用连接对象的State属性和Version属性查看数据库状态和版本（检查是否连接成功）
　　

MsgBox("数据库状态：" & conn.State & vbCrLf & "数据库版本：" & conn.Version)　　

　　最后关闭数据库连接
　　

conn.Close　　
Set conn = Nothing
　　

3. VBA读写MySQL数据表

3.1 读取MySQL数据到Excel
　　代码如下：
　　

Sub db()　　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 5.3 Unicode Driver};Server=localhost;DB=test;UID=root;PWD=root;OPTION=3;"
　　conn.Open
　　

　　rs.Open "select * from `test`", 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数据、编辑MySQL数据、添加MySQL数据。如果选中导入或编辑，都会在Excel中新增一个工作表用于操作数据，如果是选择添加，则不会。至于具体的操作，就都是傻瓜式的了，这里就不再多讲了。