承上篇。

熬夜虽然伤身，但是效率出奇的高。原来心无旁骛的做一件事，可以很快乐！

这份Excel日报将成为我的iKB系统的有力补充。

iKB系统相当于业务交易系统，供我展开日常的知识库管理工作；而Excel日报，又允许我连接到数据库，获取每日的汇总信息，有趣有有用，何乐而不为。

好了，闲话少说，上图上代码。

数据转化区：

 

完工图（自己开发给自己看的日报，爽！）：

最后，呈上完整代码：

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数据库文件完整路径'需在工程中手动添加引用：工具－引用－Microsoft ActiveX Data Objects 2.8 'Library,Microsoft ActiveX Data Objects Recordset 2.8 Library
'建立数据库连接
'AdoConn.Open ("provider=msdaora; data source=orcl; user id=C##SCOTT;password=scott") 'Oracle 12.1.0.2.0-64 版本+Excel2016专业增强版，使用此种连接方式不可行
AdoConn.Open ("provider=OraOLEDB.Oracle.1; data source=orcl; user id=C##SCOTT;password=scott")'从第二行开始循环赋值
Do While ActiveSheet.Cells(i, "B").Value <> ""D1 = ActiveSheet.Cells(i, "B")D2 = D1 + 1'设置SQL查询语句strSQL1 = "SELECT count(termid) FROM ikb"                                '当日词条总数strSQL2 = "SELECT count(distinct phase) FROM ikb"      'Phase词条数（去重不为空,下同）strSQL3 = "SELECT count(distinct type) FROM ikb"       'type词条数strSQL4 = "SELECT count(distinct subtype) FROM ikb"    'subtype词条数strSQL5 = "SELECT count(termid) FROM ikb WHERE date_updated < to_date('" & D2 & "') AND date_updated >= to_date('" & D1 & "')"              '更新日期为该日期的词条总数strSQL6 = "SELECT count(distinct en) FROM ikb"         'EN词条数strSQL7 = "SELECT count(distinct cn) FROM ikb"         'CN词条数strSQL8 = "SELECT count(distinct jp) FROM ikb"         'JP词条数strSQL9 = "SELECT count(distinct ed) FROM ikb"         'ed词条数strSQL10 = "SELECT count(distinct cnd) FROM ikb"       'cnd词条数strSQL11 = "SELECT count(distinct jpd) FROM ikb"        'jpd词条数'执行查询并赋值ActiveSheet.Cells(i, 12).CopyFromRecordset AdoConn.Execute(strSQL1)ActiveSheet.Cells(i, 3).CopyFromRecordset AdoConn.Execute(strSQL2)ActiveSheet.Cells(i, 4).CopyFromRecordset AdoConn.Execute(strSQL3)ActiveSheet.Cells(i, 5).CopyFromRecordset AdoConn.Execute(strSQL4)ActiveSheet.Cells(i, 13).CopyFromRecordset AdoConn.Execute(strSQL5)ActiveSheet.Cells(i, 6).CopyFromRecordset AdoConn.Execute(strSQL6)ActiveSheet.Cells(i, 7).CopyFromRecordset AdoConn.Execute(strSQL7)ActiveSheet.Cells(i, 8).CopyFromRecordset AdoConn.Execute(strSQL8)ActiveSheet.Cells(i, 9).CopyFromRecordset AdoConn.Execute(strSQL9)ActiveSheet.Cells(i, 10).CopyFromRecordset AdoConn.Execute(strSQL10)ActiveSheet.Cells(i, 11).CopyFromRecordset AdoConn.Execute(strSQL11)i = i + 1
LoopAdoConn.CloseSet AdoConn = NothingMsgBox "数据提取完毕！"End SubSub update()'声明定义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 N As Integer
'Dim i As Integer                    '定义i为循环变量，用于初始化时基于数据库数据循环赋值'定义SQL语句所需要的4个字符串变量
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As StringD1 = Date
D2 = D1 + 1
'i = 2                               '表头占用一行，数据从第二行开始MyData = ThisWorkbook.Path & "\业务数据库.accdb"      '制定Access数据库文件完整路径'需在工程中手动添加引用：工具－引用－Microsoft ActiveX Data Objects 2.8 'Library,Microsoft ActiveX Data Objects Recordset 2.8 Library
'建立数据库连接
'AdoConn.Open ("provider=msdaora; data source=orcl; user id=C##SCOTT;password=scott") 'Oracle 12.1.0.2.0-64 版本+Excel2016专业增强版，使用此种连接方式不可行
AdoConn.Open ("provider=OraOLEDB.Oracle.1; data source=orcl; user id=C##SCOTT;password=scott")'从第二行开始循环赋值
'Do While ActiveSheet.Cells(i, "B").Value <> ""'D1 = ActiveSheet.Cells(i, "B")'D2 = D1 + 1N = ActiveSheet.Range("C1").End(xlDown).Row + 1 '获取下一业务日期'设置SQL查询语句strSQL1 = "SELECT count(termid) FROM ikb"                                '当日词条总数strSQL2 = "SELECT count(distinct phase) FROM ikb"      'Phase词条数（去重不为空,下同）strSQL3 = "SELECT count(distinct type) FROM ikb"       'type词条数strSQL4 = "SELECT count(distinct subtype) FROM ikb"    'subtype词条数strSQL5 = "SELECT count(termid) FROM ikb WHERE date_updated < to_date('" & D2 & "') AND date_updated >= to_date('" & D1 & "')"              '更新日期为该日期的词条总数strSQL6 = "SELECT count(distinct en) FROM ikb"         'EN词条数strSQL7 = "SELECT count(distinct cn) FROM ikb"         'CN词条数strSQL8 = "SELECT count(distinct jp) FROM ikb"         'JP词条数strSQL9 = "SELECT count(distinct ed) FROM ikb"         'ed词条数strSQL10 = "SELECT count(distinct cnd) FROM ikb"       'cnd词条数strSQL11 = "SELECT count(distinct jpd) FROM ikb"        'jpd词条数'执行查询并赋值ActiveSheet.Cells(N, 1).Value = ActiveSheet.Cells(N - 1, 1).Value + 1ActiveSheet.Cells(N, 2).Value = ActiveSheet.Cells(N - 1, 2).Value + 1ActiveSheet.Cells(N, 12).CopyFromRecordset AdoConn.Execute(strSQL1)ActiveSheet.Cells(N, 3).CopyFromRecordset AdoConn.Execute(strSQL2)ActiveSheet.Cells(N, 4).CopyFromRecordset AdoConn.Execute(strSQL3)ActiveSheet.Cells(N, 5).CopyFromRecordset AdoConn.Execute(strSQL4)ActiveSheet.Cells(N, 13).CopyFromRecordset AdoConn.Execute(strSQL5)ActiveSheet.Cells(N, 6).CopyFromRecordset AdoConn.Execute(strSQL6)ActiveSheet.Cells(N, 7).CopyFromRecordset AdoConn.Execute(strSQL7)ActiveSheet.Cells(N, 8).CopyFromRecordset AdoConn.Execute(strSQL8)ActiveSheet.Cells(N, 9).CopyFromRecordset AdoConn.Execute(strSQL9)ActiveSheet.Cells(N, 10).CopyFromRecordset AdoConn.Execute(strSQL10)ActiveSheet.Cells(N, 11).CopyFromRecordset AdoConn.Execute(strSQL11)'i = i + 1
'LoopAdoConn.CloseSet AdoConn = NothingMsgBox "数据提取完毕！"End Sub