excel怎么启用宏_#数据同步#Excel + mysql 制作自动化报表

最新推荐文章于 2021-08-06 11:50:48 发布

weixin_39851809

最新推荐文章于 2021-08-06 11:50:48 发布

阅读量599

点赞数

文章标签： excel怎么启用宏

本文介绍了如何使用Excel VBA结合ODBC连接MySQL数据库，自动更新Excel报表数据，从而简化日常的数据更新工作。通过创建宏，设置VBA代码，实现一键刷新数据功能，将原本耗时半小时到几小时的数据更新过程缩短到分钟级别，极大地提高了工作效率。

摘要由CSDN通过智能技术生成

问题描述：Excel数据更新繁琐

虽然市面上各类报表工具，可视化工具种类繁多，比如Tableau，Power BI，FineBI 等等，但是Excel 依旧是日常数据工作中的主力，我们依旧使用Excel 来制作各类数据报表，绘制各种图表，制作各类报告，尤其是日报，周报，月报这类内容基本固定的格式化的报表。

但是，数据的更新，往往比较繁琐，需要不停的将数据从数据库中导出，然后复制粘贴至Excel中，非常的不方便~

问题解决：使用Excel VBA 自动的提取mysql库中的数据

第一步：安装mysql的ODBC连接工具

链接地址：Download Connector/ODBC

请特别注意版本问题，比如公司使用的是 mysql 5.7

Excel 64位，下载：mysql-connector-odbc-5.1.13-winx64

Excel 32位，下载：mysql-connector-odbc-5.1.13-win32

第二步：新建一个启用宏的Excel，文件后缀为xlsm

第三步：进入“开发工具”，插入“按钮”

第四步：右键选中按钮，进入“指定宏”，然后“新建”，进入VBA的编辑页面

第五步：编写代码

Sub 刷新数据()
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strSQL As String
    Dim cell_text As String
    
    Dim i
    Dim f_arr

    Dim KK

    Application.Calculation = xlManual '关闭自动计算功能，如果数据体量很大，数据表很多，关闭自动计算，可以有效的提升更新数据的速度
    
    Set conn = New ADODB.Connection
    conn.ConnectionString = "Driver={MySQL ODBC 5.1 Driver};Server=你的host;DB=库名;UID=用户名;PWD=密码;Port=3306;OPTION=3;allowMultiQueries=true"  '建立与数据库的链接
    conn.Open
    
    f_arr = Array("清单")  '需要更新的sheet名称，如果是多个sheet则依次填写
    
    For i = 0 To UBound(f_arr)  '循序更新
        Sheets(f_arr(i)).Select
        Range("A1:z300000").ClearContents '当前sheet中的数据
        Set rs = New ADODB.Recordset
    
        strSQL = "select * from test_table;" '需要提取的数据的select 语句
        rs.Open strSQL, conn  '打开链接，执行语句
        
        '通过循环,在第一列填写表头
        For KK = 0 To rs.Fields.Count - 1
            Cells(1, KK + 1).Value = rs.Fields(KK).Name
        Next
        
        Range("A2").CopyFromRecordset rs  '从A2开始，将查询的结果填写至数据表中
        rs.Close: Set rs = Nothing '关闭刚刚的查询
    Next
    
    conn.Close: Set conn = Nothing '关闭链接
    Application.Calculation = xlAutomatic '打开自动计算功能，结束的时候一定要打开自动计算，否则数据不会自动更新
End Sub

第六步：测试效果

原始数据为空的：