编写Excel VBA工具,连接并操作Mysql 数据库。
系统环境:
OS:Win7 64位 英文版
Office 2010 32位 英文版
1、VBA连接MySql前的准备
Tools—>References…---->引用
勾选Microsoft ActiveX Data Objects 2.8 Librarys 和Microsoft ActiveX Data Objects Recordset 2.8 Librarys
2、安装Mysql ODBC连接服务
下载连接:https://dev.mysql.com/downloads/connector/odbc
注意:这里使用32位的版本
下载上图中Windows(x86,32-bit),Zip Archive版本,在测试中,使用64位的版本无法连接到Mysql
把下载上述文件拷贝放到资源管理器里,放到合适位置,解压,点击里面的install.bat批处理文件。
从“控制面板”—>“管理工具”---->ODBC,打开以后,从这个里面无法看到Mysql的驱动,实际上使用的是下面32位的ODBC驱动
C:\Windows\SysWOW64\文件夹下的odbcca32.exe
3.连接示例
Sub` `TestConnectTodb()`` ``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;Port=3306;Database=db_hptms;Uid=root;Pwd=123456;OPTION=3;"`` ``conn.Open`` ``rs.Open ``"select fdEmpNo,fdEmpName from tbUser"``, conn`` ``With` `ThisWorkbook.Worksheets(``"MySqlData"``)`` ``.Visible = ``True`` ``.Range(``"a1:b1"``).Value = Array(``"EmpNo"``, ``"EmpName"``)`` ``.Range(``"A2"``).CopyFromRecordset rs`` ``.Activate`` ``End` `With`` ``rs.Close: ``Set` `rs = ``Nothing`` ``conn.Close: ``Set` `conn = ``Nothing`` ``MsgBox ``"处理完毕"``, vbOKOnly, ``"提示信息"``End` `Sub
上述示例经过测试,可以正常连接到Mysql数据库,这里注意使用的ODBC连接的版本是32位,在测试中,使用64位的,始终无法正常打开连接。
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中新增一个工作表用于操作数据,如果是选择添加,则不会。至于具体的操作,就都是傻瓜式的了,这里就不再多讲了。