Open last modified file from relative path

[Josu]

With great help from amazing forum community I have VBA script which opens file from relative path which looks like "C:\GENERAL\Handover\2023\7 July\"
But in some cases I can have 2 files inside, for example somebody havent done hygiene in folder
Is it any chance to to add criteria to open last modified file?

Public Sub Test2()
   
    Dim basePath As String, fullPath As String
    Dim yearMonth As String
    Dim workbookFileName As String
   
    basePath = "C:\GENERAL\Handover\"
   
    'Current year and month
   
    yearMonth = Format(Date, "yyyy\\m mmmm\\")
    fullPath = basePath & yearMonth
   
    If Dir(fullPath, vbDirectory) = vbNullString Or Dir(fullPath & "Handover*.xlsm") = vbNullString Then
   
        'Path doesn't exist or doesn't contain Handover*.xlsm, so construct path for previous month (and year)
       
        yearMonth = Format(DateAdd("m", -1, Date), "yyyy\\m mmmm\\")
        fullPath = basePath & yearMonth
       
    End If
   
    workbookFileName = Dir(fullPath & "Handover*.xlsm")
    If workbookFileName <> vbNullString Then
        Workbooks.Open fullPath & workbookFileName
    End If
   
End Sub

 

[John_w]

But in some cases I can have 2 files inside, for example somebody havent done hygiene in folder
Is it any chance to to add criteria to open last modified file?

Add this function:

Private Function Find_Latest_File(ByVal folder As String, Optional matchFiles As String = "*.*") As String

    Dim fileName As String
    Dim fileTime As Date, latestTime As Date
    
    If Right(folder, 1) <> "\" Then folder = folder & "\"
    
    Find_Latest_File = ""
    fileTime = 0
    fileName = Dir(folder & matchFiles)
    While fileName <> vbNullString
        fileTime = FileDateTime(folder & fileName)
        If fileTime > latestTime Then
            Find_Latest_File = folder & fileName
            latestTime = fileTime
        End If
        fileName = Dir
    Wend

End Function

and call it thus:

    workbookFileName = Find_Latest_File(fullPath, "Handover*.xlsm")
    If workbookFileName <> "" Then
        Workbooks.Open workbookFileName
    End If

 

[Josu]

Basically looks like this - and works like a charm. Thanks a lot!
Need to start learning VBA. would be very helpfull for my work

Public Sub Test2()
   
    Dim basePath As String, fullPath As String
    Dim yearMonth As String
    Dim workbookFileName As String
   
    basePath = "C:\GENERAL\"
   
    'Current year and month
   
    yearMonth = Format(Date, "yyyy\\m mmmm\\")
    fullPath = basePath & yearMonth
   
    If Dir(fullPath, vbDirectory) = vbNullString Or Dir(fullPath & "Handover*.xlsm") = vbNullString Then
   
        'Path doesn't exist or doesn't contain Handover*.xlsm, so construct path for previous month (and year)
       
        yearMonth = Format(DateAdd("m", -1, Date), "yyyy\\m mmmm\\")
        fullPath = basePath & yearMonth
       
    End If
   
    workbookFileName = Find_Latest_File(fullPath, "Handover*.xlsm")
    If workbookFileName <> "" Then
        Workbooks.Open workbookFileName
    End If
   


End Sub

Private Function Find_Latest_File(ByVal folder As String, Optional matchFiles As String = "*.*") As String

    Dim fileName As String
    Dim fileTime As Date, latestTime As Date
    
    If Right(folder, 1) <> "\" Then folder = folder & "\"
    
    Find_Latest_File = ""
    fileTime = 0
    fileName = Dir(folder & matchFiles)
    While fileName <> vbNullString
        fileTime = FileDateTime(folder & fileName)
        If fileTime > latestTime Then
            Find_Latest_File = folder & fileName
            latestTime = fileTime
        End If
        fileName = Dir
    Wend

End Function