Pruning logs, burning the remains After implementing some logging in my system, I got tired of digging through very long files trying to determine where specific runs started/stopped to look for differences, so I began adding a date/time stamp to the file name so I'd get a new log file each time the processing kicked off. This was wonderful, but solving one problem created a new one - now I have a proliferation of log files and I have to keep the collection of files under control.

I've written the following procedure to do the pruning and it works quite well. I'm wondering if there are any gotcha's I may have missed in my extensive (2 hours of) testing, or if there are any more efficient ways of doing this.

I believe the implementation is fairly efficient. There's no need to keep a list of the naxLogsToKeep newest (or oldest) files in memory, only the one I'm looking at now and the one known to be the oldest I've looked at so far. By not having an array, collection, dictionary or other structure of all the files, there's no need to sort anything, so this should be fairly efficient. But, I don't know it all, so I'm open to suggestions.

There are some situations that I'm aware of:


What happens if filePath doesn't have a trailing slash? It's already been checked in the calling procedure. I suppose it should be checked here again, just in case I ever reuse this code.
The error handling is... weak. Yup. I threw something in there just for giggles and grins, but will probably remove it and let it bubble up to be dealt with at a higher level.
It doesn't work well if there are more than maxLogsToKeep logs. Since Dir() gives you no indication whatsoever of what order you'll get files, if there are extra files than this will delete the oldest one(s) it's come across when it hits maxLogsToKeep. I'm pretty sure, in my limited testing, that it will work just fine, once there are only maxLogsToKeep log files in the given directory.


With that, the code:

Private Sub DeleteExtraLogFiles(ByVal filePath As String, ByVal fileNamePattern As String, ByVal maxLogsToKeep As Long)

  On Error GoTo ErrorHandler

  Dim fileCount As Long

  Dim oldestFileDate As Date
  oldestFileDate = #12/31/3999#

  Dim currentFile As String
  currentFile = Dir(filePath &amp; fileNamePattern)

  Do While Len(currentFile) &gt; 0
    fileCount = fileCount + 1
    Dim currentFileDate As Date
    currentFileDate = FileDateTime(filePath &amp; currentFile)
    If fileCount &lt; maxLogsToKeep Then
      If currentFileDate &lt; oldestFileDate Then
        Dim oldestFile As String
        oldestFile = currentFile
        oldestFileDate = currentFileDate
      End If
    Else
      If currentFileDate &lt; oldestFileDate Then
        Kill filePath &amp; currentFile
      Else
        Kill filePath &amp; oldestFile
        oldestFile = currentFile
        oldestFileDate = currentFileDate
      End If
    End If
    currentFile = Dir
  Loop

CleanExit:   
  Exit Sub

ErrorHandler:
  With Err
    MsgBox "Error: " &amp; .Number &amp; vbCrLf &amp; .Description
  End With
  Resume CleanExit

End Sub
Let's create a scenario. You have lots of log files to prune - some from January, February and March. You have, for whatever reason, way more than maxLogsToKeep. I can see a possibility that your routine would delete the February files and keep the January ones because you do not sort and you trust the order in which Dir returns the files. However, this may not be important to you.

Firstly, reference Microsoft Scripting Runtime in your VBA project. This gives you access to the FileSystemObject and related classes. This gives you more helper functions that will make the code easier to read and maintain (e.g. the File class allows you to access file information without additional reference to the file path; and a File.Delete). 

The only way around that scenario I noted is to sort. While the FSO has a Files collection, I don't see any additional functionality that allows you to sort the collection, so you can create your own collection (array in VBA for sorting) and sort that.

Some web references (links are now old - while the fundamentals stay the same the coding could take advantage of newer functionality in VBA):


https://forums.overclockers.com.au/threads/vba-retrieve-files-names-in-order.863553/
http://www.vb-helper.com/howto_dir_quicksorted.html
https://docs.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/arrays/
http://www.cpearson.com/excel/CollectionsAndDictionaries.htm


If I was writing this, then I would traverse the Files collection and build the Array (of File) as I went (the Files.Count will let you dimension the array properly in the first place) but perhaps a quick sort would be more efficient. After you have built the sorted array, you can traverse the array from the end until maxLogsToKeep and delete the files (File.Delete) (*).

Yes, a little more complicated, but addresses a scenario where you might retain ancient logs at the expense of more recent ones.

(*) I know I am working with an array rather than a For Each or other collection enumeration, but I now have a habit of working from the end when deleting things.