search folder for latest file creation date, not latest modified date

[ajm]

The other day i found a snippet of code that has helped me locate the latest version of all files in a particular folder. How do i amend this code so that the file that is ultimately opened is the one that has been created latest, irrespective of when files have been modified.

https://www.mrexcel.com/forum/excel-questions/1111630-workbook-close-not-working.html

Option Explicit
Sub reportpackage()
    Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date
    Dim MLPwkb As String, SourceWb As Workbook
    
    Set SourceWb = ThisWorkbook
   
    MyPath = \\FPMB1FNP02\Groups$\Internal_Management_Reports\DailyMLP
      
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
        
    MyFile = Dir(MyPath & "*.xls", vbNormal)
    
    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If

    Do While Len(MyFile) > 0
         
        LMD = FileDateTime(MyPath & MyFile)
          
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
       
        MyFile = Dir
        
    Loop
    
    MLPwkb = MyPath & LatestFile
   With Workbooks.Open(LatestFile)

 

[John_w]

We have to use FileSystemObject to determine a file's creation date. Add this function:

Public Function Get_Latest_Workbook(searchPath As String) As String

    Dim FSO As Object
    Dim FSfolder As Object
    Dim FSfile As Object
    Dim latestDate As Date
            
    Get_Latest_Workbook = ""
    latestDate = 0
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set FSfolder = FSO.GetFolder(searchPath)
    
    For Each FSfile In FSfolder.Files
        If FSfile.Name Like "*.xls" And FSfile.DateCreated > latestDate Then
            Get_Latest_Workbook = FSfile.path
            latestDate = FSfile.DateCreated
        End If
    Next
    
End Function

and call it like this from your code:

    MyPath = "\\FPMB1FNP02\Groups$\Internal_Management_Reports\DailyMLP"  'no need to add trailing backslash
    LatestFile = Get_Latest_Workbook(MyPath)
    If LatestFile <> "" Then
        Workbooks.Open LatestFile
    Else
        MsgBox "No *.xls files found in " & MyPath
    End If

 

[ajm]

We have to use FileSystemObject to determine a file's creation date. Add this function:

Public Function Get_Latest_Workbook(searchPath As String) As String

    Dim FSO As Object
    Dim FSfolder As Object
    Dim FSfile As Object
    Dim latestDate As Date
            
    Get_Latest_Workbook = ""
    latestDate = 0
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set FSfolder = FSO.GetFolder(searchPath)
    
    For Each FSfile In FSfolder.Files
        If FSfile.Name Like "*.xls" And FSfile.DateCreated > latestDate Then
            Get_Latest_Workbook = FSfile.path
            latestDate = FSfile.DateCreated
        End If
    Next
    
End Function

and call it like this from your code:

    MyPath = "\\FPMB1FNP02\Groups$\Internal_Management_Reports\DailyMLP"  'no need to add trailing backslash
    LatestFile = Get_Latest_Workbook(MyPath)
    If LatestFile <> "" Then
        Workbooks.Open LatestFile
    Else
        MsgBox "No *.xls files found in " & MyPath
    End If

thanks John. does

    MyPath = "\\FPMB1FNP02\Groups$\Internal_Management_Reports\DailyMLP"  'no need to add trailing backslash
    LatestFile = Get_Latest_Workbook(MyPath)
    If LatestFile <> "" Then
        Workbooks.Open LatestFile
    Else
        MsgBox "No *.xls files found in " & MyPath
    End If

completely replace

 MyPath = \\FPMB1FNP02\Groups$\Internal_Management_Reports\DailyMLP      
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
        
    MyFile = Dir(MyPath & "*.xls", vbNormal)
    
    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If

    Do While Len(MyFile) > 0
         
        LMD = FileDateTime(MyPath & MyFile)
          
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
       
        MyFile = Dir
        
    Loop
    
    MLPwkb = MyPath & LatestFile
   With Workbooks.Open(LatestFile)

 

[John_w]

Yes. Have you tried it?

 

[ajm]

not yet. was at a different site yesterday. will be doing so this morning though. thank you for your help. i'll close the loop here when i have done so.

 

[ajm]

John, many thanks. works like a charm! happy days.