mysql for excel x64_经常数据库，Excel两边跑？别费劲了，VBA一键搞定

累成狗.jpg

在日常工作当中，很多人每天的任务就是做表做表做表，而表格数据又得从数据库中去查找，然后查询出来的数据导出为Excel文件，然后再在Excel里面用函数，数据透视表做着以往每天都要做的内容。实话说，既费时，又苦逼了自己。

其实这些重复性工作完全可以通过VBA去实现。

这篇文章就是想展示怎么样利用VBA链接MySQL数据库进行查询的。

因为我们是通过ODBC和ADO方式进行连接的，所以b在此之前必须保证对电脑进行以下的配置

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

1.1首先你电脑上安装了MySQL

1.2启用脚本支持

在使用前，需要先在VBE窗口中启动数据库连接支持。在菜单栏选择“工具”－“引用”，在弹出的引用窗口中，找到"Microsoft

ActiveX Data Objects 6.1 Library"和"Microsoft ActiveX Data Objects

Recordset 6.0 Library"，把前面的框勾选上，点击确定即可。

1.3安装mysql ODBC连接服务

下载地址：Download Connector/ODBC

这里有一点要注意的是，如果mysql安装的是5.5版本的，Connector/ODBC应该安装5.1版本。如果数据库安装的是5.7版本的，Connector/ODBC可以安装5.3版本的，其他版本的数据库应该安装什么版本的Connector/ODBC，因为没用过，所以不清楚。至于是安装32位的还是64位的，并不是看你电脑位数，而是看你安装的mysql是什么位数的。

1.4.添加ODBC数据源

打开“控制面板”－“管理工具”－“ODBC 数据源”，在弹出的窗口中的“用户DSN”选项卡右侧，选择“添加”，如果是5.3版本的，在新数据库源中会出现两个MySQL驱动，分别为“MySQLODBC 5.3 ANSI Driver”及"MySQL ODBC 5.3 Unicode Driver"，很明显两者的区别在于编码标准。选中其中一个，点完成即可。如果是5.1版本的，那出现的会是MySQL ODBC 5.1 Driver

(图1)用户DSN

(图2)驱动程序里查看是否正确安装了ODBC Connect

已上环境配置好之后就可以进行在VBE窗口里面写代码了

2. VBA连接MySQL并执行简单查询

Sub importFromMySQL()

Dim conn As Object

Dim rs As Object

Set conn = CreateObject("adodb.connection")

With conn

.ConnectionString = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;DB=train;UID=root;PWD=123456;OPTION=3;Stmt=set names GBK"

.Open

End With

Sql = "select * from orders"

Set rs = conn.Execute(Sql)

For i = 1 To rs.Fields.Count

Cells(1, i) = rs.Fields(i - 1).Name

Next i

Range("a2").CopyFromRecordset rs

rs.Close: Set rs = Nothing

conn.Close: Set conn = Nothing

End Sub

接下来详细解释一下这段代码的思路含义：

1 最开头要对我们之后要用到的变量对象定义

Dim i As Integer #将i定义为整型数据

Dim conn As Object #将conn定义为对象

Dim rs As Object #将rs定义为对象

Dim Sql As String #将Sql定义为字符型

2 接下来要创建一个对象，即数据库连接对象

Set conn = CreateObject("ADODB.Connection") #数据库连接对象

3 第三步是要创建一个连接字符串，因为我们平时登录MySQL也是需要什么密码啊，用户名之类的。连接字符串的功能就相当于我们写的登录MySQL的语句。

With conn

.ConnectionString = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;DB=train;UID=root;PWD=123456;OPTION=3;Stmt=set names GBK"

.Open '相当于conn.open，即用open将conn和数据库相连

End With

参数

默认值

注释

uid

ODBC (on Windows)

用于链接至MySQL的用户名。

server

localhost

MySQL服务器的主机名。

database(DB)

数据库。

port

3306

如果服务器不是本地主机将要使用的TCP/IP端口。

pwd

服务器上用户账户的密码。

DRIVER

MySQL的驱动程序

OPTION

用于指定ODBC Connector的工作方式的

DRIVER是MySQL的驱动程序,这个驱动程序的版本名称怎么写得看自己电脑上管理工具里的ODBC数据源-驱动程序，具体看图2

OPTION，按MySQL官方的说法是用于指定ODBC Connector的工作方式的，但是我在他们官方文档中并没有找到有哪个选项是的值对应是3的。所以这里只有照写了。但是经过测试，好像是代表前三次能够正常查询，以后的查询结果好像都跟第3次一样，读者可以自己去测试一下。

server 数据库服务器的计算机名字,或者IP。如果本机是服务器,设置为Localhost或127.0.0.1。

其他的database，UID，pwd相信大家都看得懂，我就不细说了

4 第四步 ，写上自己的sql语句

Sql = "select * from orders"

from 后面的表是要你在用户DSN设置的时候选的数据库中的表

5 第五步：执行sql语句，并将加过返回到excel中

Set rs = conn.Execute(Sql) '用于执行SQL语句并接收查询语句返回的结果集，rs对象即为adodb.recordset对象

For i = 1 To rs.Fields.Count #查询结果字段名的个数

Cells(1, i) = rs.Fields(i - 1).Name #将字段名赋值给第一行第一列至第一行第rs.Fields.Count列的单元格

Next i

Range("a2").CopyFromRecordset rs #将查询结果通过CopyFromRecordset这个方法存到在Excel表中，以A2单元格为开头

6 第六步，关闭和释放rs和conn两个对象

rs.Close

conn.Close

Set rs = Nothing

Set conn = Nothing

至此，一个相对简单的VBA连接数据库并查询的代码就算完成了。