VBA: Find the number of days that occured between dates in records. Example Attached

Sub macro()
Range("D1").Copy Range("E1")
Range("E1").FormulaR1C1 = "DaysSinceLastCall"
Range("E1").EntireColumn.AutoFit
For Each c In Range(Range("A2"), Range("A" & Rows.Count))
    If c.Offset(-1) = c Then
        c.Offset(, 4) = c.Offset(0, 1) - c.Offset(-1, 1)
    Else
        c.Offset(, 4) = 0
    End If
Next
End Sub

Select allOpen in new window

Option Explicit

Public Sub getData()
    Const adOpenStatic As Integer = 3
    
    Dim wb As Excel.Workbook
    Set wb = ThisWorkbook

    Dim cn As Object        '// ADODB.Connection
    Set cn = CreateObject("ADODB.Connection")
    cn.ConnectionString = getConnectionString(wb.FullName)
    cn.Open
    
    Dim SQL As String
    SQL = vbNullString
    SQL = SQL & "SELECT originatordn," & vbCrLf
    SQL = SQL & "       MAX([Date]) AS [Date]," & vbCrLf
    SQL = SQL & "       MAX([Month]) AS [Month]," & vbCrLf
    SQL = SQL & "       MAX([year]) AS [Year]" & vbCrLf
    SQL = SQL & "FROM [Example Data$]" & vbCrLf
    SQL = SQL & "GROUP BY originatordn;"
    
    Dim rs As Object        '// ADODB.Recordset
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open SQL, cn, adOpenStatic
    
    Dim ws As Excel.Worksheet
    Set ws = wb.Worksheets("Example Result")
    copyHeaders rs, ws
    doFormatting ws
    
    Dim rng As Excel.Range
    Set rng = ws.Range("A2")
    rng.CopyFromRecordset rs
    
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    
    computedays ws
    Set ws = Nothing
    Set wb = Nothing
End Sub

Private Function getConnectionString(ByVal path As String) As String
    getConnectionString = vbNullString
    getConnectionString = getConnectionString & "Provider=Microsoft.ACE.OLEDB.12.0;"
    getConnectionString = getConnectionString & "Data Source=" & path & ";"
    getConnectionString = getConnectionString & "Extended Properties=""Excel 12.0 Macro;HDR=YES"";"
End Function

Private Sub copyHeaders(ByRef rs As ADODB.Recordset, ByRef ws As Excel.Worksheet)
    Dim rng As Excel.Range
    Set rng = ws.Range("A1")
    
    Dim field As ADODB.field
    For Each field In rs.Fields
        rng.Value = field.Name
        Set rng = rng.Offset(columnoffset:=1)
    Next
    rng.Value = "DaysSinceLastCall"
    Set rng = Nothing
End Sub

Private Sub computedays(ByRef ws As Excel.Worksheet)
    Dim rng As Excel.Range
    
    Set rng = ws.Range("D2")
    While rng.Value
        rng.Offset(columnoffset:=1).FormulaR1C1 = "=DAYS(NOW(),RC[-3])"
        Set rng = rng.Offset(rowoffset:=1)
    Wend
    Set rng = Nothing
End Sub

Private Function doFormatting(ByRef ws As Excel.Worksheet)
    Dim rng As Excel.Range
    Set rng = ws.Range("B:B")
    
    rng.NumberFormat = "m/d/yyyy"
    Set rng = Nothing
End Function

Select allOpen in new window