Microsoft Office Forums - View Single Post - Get the latest filename from directory with a macro

gmayor · 07-10-2017, 08:33 PM

If you can access the directory from Windows File Explorer then yes. Call the following function from your macro to write the name of the file in your document. Test it with the test macro and the folder in question.

Code:

Option Explicit

Sub Test()
MsgBox LatestFile("C:\Path") 'The path you want to test
End Sub

Function LatestFile(strPath As String) As String
Dim oFS As Object
Dim strFile As String
Dim strFilename As String
Dim dDate As Date
    Do Until Right(strPath, 1) = Chr(92)
        strPath = strPath & Chr(92)
    Loop
    Set oFS = CreateObject("Scripting.FileSystemObject")
    dDate = "01/01/1900"
    strFile = Dir$(strPath & "*.*")
    While strFile <> ""
        If oFS.GetFile(strPath & strFile).DateLastModified > dDate Then
            strFilename = strPath & strFile
            dDate = oFS.GetFile(strPath & strFile).DateLastModified
        End If
        strFile = Dir$()
    Wend
    LatestFile = strFilename
lbl_Exit:
    Set oFS = Nothing
    Exit Function
err_Handler:
    LatestFile = ""
    Err.Clear
    GoTo lbl_Exit
End Function