So I was working on a project and was faced with a little issue. You see, I created a program which processes files that are generated by a web application and that get downloaded to the user’s PC. The problem is that if the file already exists, Windows helpfully adds a numeric prefix to differentiate the files.
So, say the original files was named ‘DataArchive.zip’, subsequent downloads results in
- DataArchive.zip’
- DataArchive(1).zip’
- DataArchive(2).zip’
- DataArchive(3).zip’
- …
Well that is in Windows 10. Microsoft tried to help us and in Windows 11 now add an extra space, so we get:
- DataArchive.zip’
- DataArchive (1).zip’
- DataArchive (2).zip’
- DataArchive (3).zip’
- …
You know, just to spice up our coding life a bit.
I did some Googling, figuring I couldn’t be the first developer to need to do this type of thing, but came up empty handed. My GoogleFu was not up to the job this occasion.
As such, I set out to create something myself. I wanted to devise a ‘simple’ way I could search a folder, in this case the download folder, to find the latest downloaded file with a given name pattern.
How could it be done?
Well, I ended up turning towards the File System Object (FSO). I was hoping I could simply use Dir(), but it didn’t provide access to the necessary file properties that FSO did.
Here’s what I came up with:
'---------------------------------------------------------------------------------------
' Procedure : FindLatestFile
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Find the latest (DateCreated) file based on a search pattern
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding -> none required
' References:
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sPath : Directory to search through
' sSearchPattern : Search pattern to use to identify matching files
'
' Usage:
' ~~~~~~
' FindLatestFile("C:\Users\Dev\Downloads\", "DataArchive (*).zip")
' Returns -> DataArchive (4).zip
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2024-07-15 Initial Public Release
'---------------------------------------------------------------------------------------
Function FindLatestFile(ByVal sPath As String, _
ByVal sSearchPattern As String) As Variant
On Error GoTo Error_Handler
Dim oFSO As Object 'Scripting.FileSystemObject
Dim oFolder As Object 'Scripting.Folder
Dim oFile As Object 'Scripting.File
Dim dtCurrFileDt As Date
Dim dtFileDt As Date
Dim CurrentRow As Long
Dim sOutputFile As String
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(sPath)
For Each oFile In oFolder.Files
If oFile.Name Like sSearchPattern Then
dtCurrFileDt = oFile.DateCreated
If CurrentRow = 0 Then
dtFileDt = oFile.DateCreated
sOutputFile = oFile.Name
Else
If dtCurrFileDt > dtFileDt Then
dtFileDt = oFile.DateCreated
sOutputFile = oFile.Name
End If
End If
CurrentRow = CurrentRow + 1
End If
Next oFile
If sOutputFile = "" Then
FindLatestFile= Null
Else
FindLatestFile= sOutputFile
End If
Error_Handler_Exit:
On Error Resume Next
Set oFile = Nothing
Set oFolder = Nothing
Set oFSO = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Source: FindLatestFile" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
and an example of using it might be:
sArchive = FindLatestFile("C:\Users\Dev\Downloads\", "DataArchive(*).zip") 'Windows 10 Search Pattern
If IsNull(sArchive) Then sArchive = FindLatestFile("C:\Users\Dev\Downloads\", "DataArchive (*).zip") 'Windows 11 Search Pattern
If IsNull(sArchive) Then sArchive = "DataArchive.zip" 'If none found fall back to the original filename
'Check if sArchive Exist ...
and this can be used for all sorts of other scenarios, all you have to do is change the sSearchPattern for the pattern that meets your needs.
In a future version, I will allow for multiple patterns rather than requiring to process a folder multiple times, to improve performance, but that will be for another day.
Lastly, since we are using an FSO object here, this would be a great opportunity to implement Self-Healing Object variables. To learn more on the subject I’ll refer you to my article on the subject:
