Maybe something like this?
Sub main()
MsgBox GetLatestFile("C:\myPath\", "Volumes", " (dmmmyy)", ".xlsx")
End Sub
Function GetLatestFile(ByVal sPath As String, _
sName As String, _
sFmt As String, _
sExt As String) As String
' Returns the fullname of the lastest file having the name structure
' described by the arguments
Dim t As Date
Dim sTest As String
If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
If Len(Dir(sPath, vbDirectory)) = 0 Then
MsgBox "Bad directory"
Else
For t = Date To Date - 14 Step -1
sTest = sPath & sName & Format(t, sFmt) & sExt
If Len(Dir(sTest)) Then
GetLatestFile = sPath & sTest
Exit Function
End If
Next t
MsgBox "No file found!"
End If
End Function
Bookmarks