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函数，很有用哦
