Counting Files by Date

Someone told me we are posting more frequently lately. (For non-accountants, posting means taking the entered transactions and updating other files with the information.) Ever the skeptic, I decide to see for myself. Whenever we post, we produce a pre-post report in the form of

Pre-Post_Sales_Journal_yyyymmddhhmmss.TXT

Public Sub CountPosts()
    
    Dim fileName As String
    Dim nameDate As String
    Dim fileDate As Date
    
    fileName = Dir(PATH & "Pre-Post_Sales_Journal_*")
    
    Do While Len(fileName) > 0
        nameDate = Split(Split(fileName, ".")(0), "_")(3)
        fileDate = DateSerial(Left(nameDate, 4), Mid(nameDate, 5, 2), Mid(nameDate, 7, 2))
        On Error Resume Next
            Sheet1.Columns(1).Find(fileDate, , xlValues, xlWhole).Offset(0, 1).Value = Sheet1.Columns(1).Find(fileDate, , xlValues, xlWhole).Offset(0, 1).Value + 1
        On Error GoTo 0
        fileName = Dir
    Loop
    
End Sub

Public Sub CountPosts()

Dim fileName As String

Dim nameDate As String

Dim fileDate As Date

fileName = Dir(PATH & "Pre-Post_Sales_Journal_*")

Do While Len(fileName) > 0

nameDate = Split(Split(fileName, ".")(0), "_")(3)

fileDate = DateSerial(Left(nameDate, 4), Mid(nameDate, 5, 2), Mid(nameDate, 7, 2))

On Error Resume Next

Sheet1.Columns(1).Find(fileDate, , xlValues, xlWhole).Offset(0, 1).Value = Sheet1.Columns(1).Find(fileDate, , xlValues, xlWhole).Offset(0, 1).Value + 1

On Error GoTo 0

fileName = Dir

Loop

End Sub

PATH is a module level constant pointing to the folder.

nameDate = Split(Split(fileName, ".")(0), "_")(3)

1	nameDate = Split(Split(fileName, ".")(0), "_")(3)

If this was more than a one-off program, I would have written this line in a way that you could read it. The inner Split creates an array like

[0] = Pre-Post_Sales_Journal_yyyymmddhhmmss, [1] = .TXT

and I take the first element (the zeroth index) of the array. Then I split that further

[0] = Pre-Post, [1] = Sales, [2] = Journal, [3] = yyyymmddhhmmss

and I take the fourth element (index = 3) of that array. That’s my date in string form.

I put a bunch of dates in column A of sheet1 for as far back as I wanted to go. Then I add 1 to the cell to the right of the date. It turns out we are posting more frequently.

2 thoughts on “Counting Files by Date”

That’s where Dictionary comes in (besides FileDateTime)

Sub M_snb()
    c00 = "G:\OF\"
    c01 = Dir(c00 & "Pre-Post_Sales_Journal*.txt")
    
    With CreateObject("scripting.dictionary")
        Do While c01 <> ""
             c02 = Format(FileDateTime(c00 & c01), "yyyy_mm_dd")
            .Item(c02) = .Item(c02) + 1
            c01 = Dir
        Loop
        Cells(1, 20).Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
    End With
End Sub

Sub M_snb()

c00 = "G:\OF\"

c01 = Dir(c00 & "Pre-Post_Sales_Journal*.txt")

With CreateObject("scripting.dictionary")

Do While c01 <> ""

c02 = Format(FileDateTime(c00 & c01), "yyyy_mm_dd")

.Item(c02) = .Item(c02) + 1

c01 = Dir

Loop

Cells(1, 20).Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))

End With

End Sub

We set up some SQL triggers on our accounting software to create audit log tables on all out transaction postings. Appreciate your process is slightly different; but for me was also quite fun and instructive to pull that audit log data into Power BI and graph who in my team was doing the postings and when they were doing them. You can easily spot the coffee break spikes, and that one member of staff was more productive in the afternoons etc.
Looks like you have the time data to do that also if you wanted to.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Daily Dose of Excel

Haphazardly Posted Excel Information and Other Stuff

2 thoughts on “Counting Files by Date”

Leave a Reply