Take the last part.

Staying up late may hurt, but it's surprisingly efficient. Originally, it can be very happy to do one thing without distraction.

This Excel Daily will be a powerful supplement to my iKB system.

iKB system is similar to business transaction system for me to carry out daily knowledge base management; Excel Daily also allows me to connect to the database to obtain daily summary information, interesting and useful, why not.

Okay, let's talk less. The code on the picture above.

Data Conversion Zone:

Completion Map (Daily newspaper developed for myself, cool!) :

Finally, the complete code is presented:

Sub initialize()

'Declaration definition VBA Variable types to be used in statements
Dim AdoConn As New ADODB.Connection 'Defining variables AdoConn Connect database objects( ADODB It is a database access component. Connection It is one of the objects used to connect to database and other operations.
Dim MyData As String                'Defining variables MyData For string variables, for database path assignment
Dim D1 As Date                      'Definition D1 For date variables, for business date assignment
Dim D2 As Date                      'Definition D2 For date variables, for business date assignment
Dim i As Integer                    'Definition i As a cyclic variable, it is used for initialization based on database data cyclic assignment

'Definition SQL Four String Variables Required by Statement
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String

i = 2                               'The header takes up one line, and the data starts on the second line.


MyData = ThisWorkbook.Path & "\Business database.accdb"      'Formulate Access Complete path of database file


'You need to add references manually in the project: tools－Quote－Microsoft ActiveX Data Objects 2.8 'Library,Microsoft ActiveX Data Objects Recordset 2.8 Library
'Establishing database connection
'AdoConn.Open ("provider=msdaora; data source=orcl; user id=C##SCOTT; password = Scott ")'Oracle 12.1.0.2.0-64 + Excel 2016 Professional Enhancement Edition, using this connection is not feasible
AdoConn.Open ("provider=OraOLEDB.Oracle.1; data source=orcl; user id=C##SCOTT;password=scott")


'Loop assignment from the second line
Do While ActiveSheet.Cells(i, "B").Value <> ""
    D1 = ActiveSheet.Cells(i, "B")
    D2 = D1 + 1

    'Set up SQL Query statement
    strSQL1 = "SELECT count(termid) FROM ikb"                                'Total number of entries of the day
    strSQL2 = "SELECT count(distinct phase) FROM ikb"      'Phase Number of entries,The same below)
    strSQL3 = "SELECT count(distinct type) FROM ikb"       'type Entry number
    strSQL4 = "SELECT count(distinct subtype) FROM ikb"    'subtype Entry number
    strSQL5 = "SELECT count(termid) FROM ikb WHERE date_updated < to_date('" & D2 & "') AND date_updated >= to_date('" & D1 & "')"              'Update date date is the total number of entries for that date
    strSQL6 = "SELECT count(distinct en) FROM ikb"         'EN Entry number
    strSQL7 = "SELECT count(distinct cn) FROM ikb"         'CN Entry number
    strSQL8 = "SELECT count(distinct jp) FROM ikb"         'JP Entry number
    strSQL9 = "SELECT count(distinct ed) FROM ikb"         'ed Entry number
    strSQL10 = "SELECT count(distinct cnd) FROM ikb"       'cnd Entry number
    strSQL11 = "SELECT count(distinct jpd) FROM ikb"        'jpd Entry number
    
    'Execute queries and assign values
    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
Loop


AdoConn.Close

Set AdoConn = Nothing

MsgBox "Data extraction finished!"

End Sub


Sub update()

'Declaration definition VBA Variable types to be used in statements
Dim AdoConn As New ADODB.Connection 'Defining variables AdoConn Connect database objects( ADODB It is a database access component. Connection It is one of the objects used to connect to database and other operations.
Dim MyData As String                'Defining variables MyData For string variables, for database path assignment
Dim D1 As Date                      'Definition D1 For date variables, for business date assignment
Dim D2 As Date                      'Definition D2 For date variables, for business date assignment
Dim N As Integer
'Dim i As Integer                    'Definition i As a cyclic variable, it is used for initialization based on database data cyclic assignment

'Definition SQL Four String Variables Required by Statement
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String

D1 = Date
D2 = D1 + 1
'i = 2                               'The header takes up one line, and the data starts on the second line.


MyData = ThisWorkbook.Path & "\Business database.accdb"      'Formulate Access Complete path of database file


'You need to add references manually in the project: tools－Quote－Microsoft ActiveX Data Objects 2.8 'Library,Microsoft ActiveX Data Objects Recordset 2.8 Library
'Establishing database connection
'AdoConn.Open ("provider=msdaora; data source=orcl; user id=C##SCOTT; password = Scott ")'Oracle 12.1.0.2.0-64 + Excel 2016 Professional Enhancement Edition, using this connection is not feasible
AdoConn.Open ("provider=OraOLEDB.Oracle.1; data source=orcl; user id=C##SCOTT;password=scott")


'Loop assignment from the second line
'Do While ActiveSheet.Cells(i, "B").Value <> ""
    'D1 = ActiveSheet.Cells(i, "B")
    'D2 = D1 + 1

N = ActiveSheet.Range("C1").End(xlDown).Row + 1 'Get the next business date

    'Set up SQL Query statement
    strSQL1 = "SELECT count(termid) FROM ikb"                                'Total number of entries of the day
    strSQL2 = "SELECT count(distinct phase) FROM ikb"      'Phase Number of entries,The same below)
    strSQL3 = "SELECT count(distinct type) FROM ikb"       'type Entry number
    strSQL4 = "SELECT count(distinct subtype) FROM ikb"    'subtype Entry number
    strSQL5 = "SELECT count(termid) FROM ikb WHERE date_updated < to_date('" & D2 & "') AND date_updated >= to_date('" & D1 & "')"              'Update date date is the total number of entries for that date
    strSQL6 = "SELECT count(distinct en) FROM ikb"         'EN Entry number
    strSQL7 = "SELECT count(distinct cn) FROM ikb"         'CN Entry number
    strSQL8 = "SELECT count(distinct jp) FROM ikb"         'JP Entry number
    strSQL9 = "SELECT count(distinct ed) FROM ikb"         'ed Entry number
    strSQL10 = "SELECT count(distinct cnd) FROM ikb"       'cnd Entry number
    strSQL11 = "SELECT count(distinct jpd) FROM ikb"        'jpd Entry number
    
    'Execute queries and assign values
    ActiveSheet.Cells(N, 1).Value = ActiveSheet.Cells(N - 1, 1).Value + 1
    ActiveSheet.Cells(N, 2).Value = ActiveSheet.Cells(N - 1, 2).Value + 1
    ActiveSheet.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
'Loop


AdoConn.Close

Set AdoConn = Nothing

MsgBox "Data extraction finished!"

End Sub