Opening the most recent file saved to a folder

Hi Folks,

I am trying to use the code below to open the most recently saved file in my downloads folder, however it is returning an error that it can't find it. I believe this is because the code is including the file extension ".xls" in the name search.. I am wondering if someone can confirm this and help augment the code so that it drops the file extension from the name?

Thanks in advance for all posts

Sub GetMostRecentFile()
    
    Dim FileSys As FileSystemObject
    Dim objFile As File
    Dim myFolder
    Dim strFilename As String
    Dim dteFile As Date
        
    'set path for files - change for your folder
    Const myDir As String = "C:\Users\smokybear\Downloads"
    
    'set up filesys objects
    Set FileSys = New FileSystemObject
    Set myFolder = FileSys.GetFolder(myDir)
        
    
    'loop through each file and get date last modified. If largest date then store Filename
    dteFile = DateSerial(1900, 1, 1)
    For Each objFile In myFolder.Files
        If objFile.DateLastModified > dteFile Then
            dteFile = objFile.DateLastModified
            strFilename = objFile.Name
        End If
    Next objFile
    Workbooks.Open strFilename
            
    Set FileSys = Nothing
    Set myFolder = Nothing
End Sub

Hi there,

Try the following code and see if it does what you need:

Sub GetMostRecentFile()
    
'   Set path for files - change for your folder
    Const MY_DIR    As String = "C:\Users\smokybear\Downloads"

    Dim objFileSys  As FileSystemObject
    Dim sFilename   As String
    Dim objFolder   As Folder
    Dim objFile     As File
    Dim dteFile     As Date
        
'   Set up filesys objects
    Set objFileSys = New FileSystemObject
    Set objFolder = objFileSys.GetFolder(MY_DIR)
    
'   Loop through each file and get date last modified. If largest date then store Filename
    dteFile = DateSerial(1900, 1, 1)
    For Each objFile In objFolder.Files

        If objFile.DateLastModified > dteFile Then
            dteFile = objFile.DateLastModified
            sFilename = objFile.Name
        End If

    Next objFile

    Workbooks.Open objFolder.Path & "\" & sFilename
            
    Set objFileSys = Nothing
    Set objFolder = Nothing

End Sub

The most significant change from your original code is in the highlighted line.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi again,

Many thanks for the Reputation increase

Regards,

Greg M