数据分析（入门篇）-第四章-让报告自动化（VBA）-Part1（Excel报告自动化）

VBA是一种通用的自动化语言，可以使Excel常用操作步骤自动化。

VBA基本概念：对象、属性、方法、事件

VBA调试技巧：立即窗口、监视窗口、本地窗口、编辑窗口

Excel日报自动化原理：

源数据：

提取到Excel中：

数据转化区：

日报正文区：

VBA代码：

Sub initialize()

'声明定义VBA语句中需要使用到的各个变量类型
Dim AdoConn As New ADODB.Connection '定义变量AdoConn为连接数据库对象（ADODB是数据库访问组件，Connection是其中的一个对象），用于实现连接数据库连接等操作
Dim MyData As String                '定义变量MyData为字符串型变量，用于数据库路径赋值
Dim D1 As Date                      '定义D1为日期型变量，用于业务日期赋值
Dim D2 As Date                      '定义D2为日期型变量，用于业务日期赋值
Dim i As Integer                    '定义i为循环变量，用于初始化时基于数据库数据循环赋值

'定义SQL语句所需要的4个字符串变量
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String

i = 2                               '表头占用一行，数据从第二行开始


MyData = ThisWorkbook.Path & "\业务数据库.accdb"      '制定Access数据库文件完整路径

'建立数据库连接
With AdoConn
.provider = "Microsoft.ace.oledb.12.0"
.Open MyData
End With

'从第二行开始循环赋值
Do While ActiveSheet.Cells(i, "B").Value <> ""
    D1 = ActiveSheet.Cells(i, "B")
    D2 = D1 + 1

    '设置SQL查询语句
    strSQL1 = "SELECT count(用户ID) FROM 用户明细 WHERE 注册日期<#" & D2 & "#AND 注册日期>=#" & D1 & "#"                                '新增用户数
    strSQL2 = "SELECT count(用户ID) FROM (SELECT DISTINCT 用户ID FROM 订购明细 WHERE 订购日期<#" & D2 & "# AND 订购日期>=#" & D1 & "#)" '订购用户数
    strSQL3 = "SELECT count(订单编号), sum(订购金额) FROM 订购明细 WHERE 订购日期<#" & D2 & "# AND 订购日期>=#" & D1 & "#"          '订单数，业务收入
    strSQL4 = "SELECT count(用户ID) FROM (SELECT DISTINCT 用户ID FROM 订购明细 WHERE 订购日期<#" & D2 & "#)"                       '累计订购用户数
    
    '执行查询并赋值
    ActiveSheet.Cells(i, 3).CopyFromRecordset AdoConn.Execute(strSQL1)
    ActiveSheet.Cells(i, 4).CopyFromRecordset AdoConn.Execute(strSQL2)
    ActiveSheet.Cells(i, 5).CopyFromRecordset AdoConn.Execute(strSQL3)
    ActiveSheet.Cells(i, 7).CopyFromRecordset AdoConn.Execute(strSQL4)
    If (i >= 3) Then
        ActiveSheet.Cells(i, 8).Value = ActiveSheet.Cells(i - 1, 8).Value + ActiveSheet.Cells(i, 3).Value
        ActiveSheet.Cells(i, 9).Value = ActiveSheet.Cells(i - 1, 9).Value + ActiveSheet.Cells(i, 5).Value
        ActiveSheet.Cells(i, 10).Value = ActiveSheet.Cells(i - 1, 10).Value + ActiveSheet.Cells(i, 6).Value
    End If
    
    i = i + 1
Loop


AdoConn.Close

Set AdoConn = Nothing

MsgBox "数据提取完毕！"

End Sub


Sub update()
Dim AdoConn As New ADODB.Connection
Dim MyData As String
Dim N As Integer
Dim D1 As Date
Dim D2 As Date

Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String

D1 = Date
D2 = D1 + 1
i = 2


MyData = ThisWorkbook.Path & "\业务数据库.accdb"

With AdoConn
.provider = "Microsoft.ACE.OLEDB.12.0"
.Open MyData
End With

    
    N = ActiveSheet.Range("C1").End(xlDown).Row + 1

    
    strSQL1 = "SELECT count(用户ID) FROM 用户明细 WHERE 注册日期<#" & D2 & "#AND 注册日期>=#" & D1 & "#"
    strSQL2 = "SELECT count(用户ID) FROM (SELECT DISTINCT 用户ID FROM 订购明细 WHERE 订购日期<#" & D2 & "# AND 订购日期>=#" & D1 & "#)"
    strSQL3 = "SELECT count(订单编号), sum(订购金额) FROM 订购明细 WHERE 订购日期<#" & D2 & "# AND 订购日期>=#" & D1 & "#"
    strSQL4 = "SELECT count(用户ID) FROM (SELECT DISTINCT 用户ID FROM 订购明细 WHERE 订购日期<#" & D2 & "#)"
    
    ActiveSheet.Cells(N, 3).CopyFromRecordset AdoConn.Execute(strSQL1)
    ActiveSheet.Cells(N, 4).CopyFromRecordset AdoConn.Execute(strSQL2)
    ActiveSheet.Cells(N, 5).CopyFromRecordset AdoConn.Execute(strSQL3)
    ActiveSheet.Cells(N, 7).CopyFromRecordset AdoConn.Execute(strSQL4)
    ActiveSheet.Cells(N, 1).Value = ActiveSheet.Cells(N - 1, 1).Value + 1
    ActiveSheet.Cells(N, 2).Value = Date
    ActiveSheet.Cells(N, 8).Value = ActiveSheet.Cells(N - 1, 8).Value + ActiveSheet.Cells(N, 3).Value
    ActiveSheet.Cells(N, 9).Value = ActiveSheet.Cells(N - 1, 9).Value + ActiveSheet.Cells(N, 5).Value
    ActiveSheet.Cells(N, 10).Value = ActiveSheet.Cells(N - 1, 10).Value + ActiveSheet.Cells(N, 6).Value

AdoConn.Close

Set AdoConn = Nothing

MsgBox "数据更新完毕！"
End Sub

后记：

1. VBA代码是在原示例的基础上作了改进（可以批量提取数据，而非只提取一天的值）

2. 学习了Offset函数，很有用哦