兰州城建设计院网站全球搜索网站排名

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 Stringi = 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) ThenActiveSheet.Cells(i, 8).Value = ActiveSheet.Cells(i - 1, 8).Value + ActiveSheet.Cells(i, 3).ValueActiveSheet.Cells(i, 9).Value = ActiveSheet.Cells(i - 1, 9).Value + ActiveSheet.Cells(i, 5).ValueActiveSheet.Cells(i, 10).Value = ActiveSheet.Cells(i - 1, 10).Value + ActiveSheet.Cells(i, 6).ValueEnd Ifi = i + 1
LoopAdoConn.CloseSet AdoConn = NothingMsgBox "数据提取完毕！"End SubSub update()
Dim AdoConn As New ADODB.Connection
Dim MyData As String
Dim N As Integer
Dim D1 As Date
Dim D2 As DateDim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As StringD1 = Date
D2 = D1 + 1
i = 2MyData = ThisWorkbook.Path & "\业务数据库.accdb"With AdoConn
.provider = "Microsoft.ACE.OLEDB.12.0"
.Open MyData
End WithN = ActiveSheet.Range("C1").End(xlDown).Row + 1strSQL1 = "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 + 1ActiveSheet.Cells(N, 2).Value = DateActiveSheet.Cells(N, 8).Value = ActiveSheet.Cells(N - 1, 8).Value + ActiveSheet.Cells(N, 3).ValueActiveSheet.Cells(N, 9).Value = ActiveSheet.Cells(N - 1, 9).Value + ActiveSheet.Cells(N, 5).ValueActiveSheet.Cells(N, 10).Value = ActiveSheet.Cells(N - 1, 10).Value + ActiveSheet.Cells(N, 6).ValueAdoConn.CloseSet AdoConn = NothingMsgBox "数据更新完毕！"
End Sub