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