This function combs a file folder searching for the most recent version of a file and returns the file name as a string so you can pass it to Workbook.Open or whatever else you like. I'd seen other examples of subroutines that can open the most recent file in a folder, but I wanted to be able to a) Search folders that had lots of different files…

vbaMostRecentFileFunc

Public Function MostRecentFile(ByVal FileName As String, ByVal FolderName As String) As String
    
    Dim objFileSys As FileSystemObject
    Dim objFile As File
    Dim objFolder As Object
    Dim strFilename As String
    Dim datFile As Date
    Dim strDir As String
    
    'Set file path
    strDir = FolderName
    
    'Establish filesys objects
    Set objFileSys = New FileSystemObject
    Set objFolder = objFileSys.GetFolder(strDir)
        
    
    'Loop through each file in FolderName to determine if the file name is of the form
    'specified by FileName
    'If file name matches, get date last modified
    'If largest date then store file name in strFileName
    datFile = DateSerial(1900, 1, 1)
    For Each objFile In objFolder.Files
        If Left(objFile.Name, Len(FileName)) = FileName Then
            If objFile.DateLastModified > datFile Then
                datFile = objFile.DateLastModified
                strFilename = objFile.Name
            End If
        End If
    Next objFile
    
    MostRecentFile = strFilename
            
    Set objFileSys = Nothing
    Set objFolder = Nothing

End Function