vba连接mysql数据库案例

excel做数据方便，但是在数据传递上不方便，经常通过邮件，U盘等方式传递，虽然可以通过在电脑上做共享文件夹，但始终要打开报表-复制数据等等操作，而且由于各电脑上的excel版本不同的问题，经常出现报错等问题。那么就可以用采用EXCEL和数据库对接来解决数据传递的问题。

建立mysql数据库

从官网下载mysql数据库，由于电脑系统配置的版本比较老，有的win7系统，有的win10,甚至有的XP系统，所以在安装的时候用比较老的数据库版本。
https://dev.mysql.com/downloads/

image.png
image.png
选择的5.3.7左右的版本
下载完成后安装就行了。
我们建库的目的是公用，所以要解决局域网连接问题。
在mysql的黑屏终端上输入
grant all privileges on . to root@"192.168.1.1" identified by '123456' with grant option;
这里就是指 192.168.1.1这个IP的机器可以使用 root /123456 这个账户远程访问MySQL数据库
flush privileges;

安装MySQLODBC 连接服务

下载地址
https://dev.mysql.com/downloads/connector/odbc/

image.png
注意这里我们选择32位的版本，64的odbc在excel配置连不上，估计是excel默认配置的问题。实测个人是win10,64位系统，连接没有问题。
在C盘建一个文件夹，将压缩包解压到这个文件夹。
image.png
image.png
有个install.bat文件，我们右键以管理员身份运行。
image.png
然后就需要配置ODBC数据源了。
image.png
打开这个应用程序。
image.png
image.png
这两个选那个都可以。
image.png
连接成功后到了excel和mysql来回传递数据的部分。

数据库建表

这里不建议用sql语句创建表，不好写，我们直接用navicate数据库工具创建设置表。

image.png

然后往表里写点数据用于测试。

image.png
都弄好了我们进入EXCELvba部分。

vba设置

image.png

工具-引用两个库
"Microsoft ActiveX Data Objects 6.1 Library"
"Microsoft ActiveX Data Objects Recordset 6.0 Library"

image.png
image.png

代码部分：

Sub test()
On Error Resume Next
Dim conn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strConn As String
    Dim strconn1 As String
    Dim sql
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.recordset")
    strConn = "Driver={MySQL ODBC 5.3 ANSI Driver};server=192.168.XX.XX; port=3306;database=XXXX;Uid=root;Pwd=123456;"
'     OraClient11g_home1为oracle安装目录，Dbq为数据库名
    conn.Open strConn '打开数据库
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Sheets("结果").Cells.ClearContents
    sql = "select * from 测试表"
    Set rs = conn.Execute(sql)
    For i = 1 To rs.Fields.Count
         Sheets("结果").Cells(1, i) = rs.Fields(i - 1).Name
    Next
    Range("a2").CopyFromRecordset rs
 rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
MsgBox "导出完成"
End Sub

image.png