分享

Excel、VBA与MySQL交互

 刮骨剑 2018-12-26

本文主要涉及:

  1. VBA中的MySQL环境配置
  2. VBA连接MySQL数据库
  3. VBA读写MySQL数据
  4. 在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.Recordsetrs.OpenADODB.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

可以右键单击其中的数据库,编辑连接信息:

或者双击打开数据库目录,依然打开数据表目录,再选中其中一个数据表时,在下方会出现三个选项,分别是导入MySQL数据、编辑MySQL数据、添加MySQL数据。如果选中导入或编辑,都会在Excel中新增一个工作表用于操作数据,如果是选择添加,则不会。至于具体的操作,就都是傻瓜式的了,这里就不再多讲了。

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多