retrieving last updated file in folder

[MikeCDPQ]

I would like to know how to be able to retrieve the name of the last updated file that is found in a given folder.

I have tried different file scripting scenarios but could not get it to work yet it should be so simple.

Thanks for any suggestion.

Mike

 

[CautionMP]

MikeCDPQ,
Here are two options, the first uses the native VBA.FileSystem object, the second uses the Scripting.FileSystemObject (FSO).

Function FileSystemLastModified(path As String) As String
On Error GoTo FileSystemLastModified_Error
Dim dteLastModified As Date
Dim strFileName As String, strBuffer As String

strBuffer = Dir(path)
Do While strBuffer <> ""
  If FileDateTime(path & strBuffer) > dteLastModified Then
    dteLastModified = FileDateTime(path & strBuffer)
    strFileName = strBuffer
  End If
  strBuffer = Dir
Loop

FileSystemLastModified_Exit:
FileSystemLastModified = strFileName
Exit Function

FileSystemLastModified_Error:
On Error Resume Next
Debug.Print Err.Number, Err.Description
Resume FileSystemLastModified_Exit
End Function

Function FSOLastModified(path As String) As String
On Error GoTo FSOLastModified_Error
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim dteLastModified As Date
Dim strFileName As String
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(path)
For Each objFile In objFolder.Files
  If objFile.DateLastModified > dteLastModified Then
    dteLastModified = objFile.DateLastModified
    strFileName = objFile.Name
  End If
Next objFile

FSOLastModified_Exit:
On Error Resume Next
FSOLastModified = strFileName
Set objFile = Nothing
Set objFolder = Nothing
Set objFSO = Nothing
Exit Function

FSOLastModified_Error:
Debug.Print Err.Number, Err.Description
Resume FSOLastModified_Exit
End Function

Sub Test()
Const cPath = "C:\"
Debug.Print FSOLastModified(cPath)
Debug.Print FileSystemLastModified(cPath)
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[MikeCDPQ]

Hi CautionMP

Your solution works beautifully.

That's exactly what I was looking for.

Thanks a bunch !

Mike