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

PATH is a module level constant pointing to the folder.

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

  1. That’s where Dictionary comes in (besides FileDateTime)

  2. 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.

Leave a Reply

Your email address will not be published.