经常数据库，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

• 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连接数据库并查询的代码就算完成了。