Getting Most Recently Created File in a Folder

[El_Diabolo]

Hi everyone.

I want to find the most recently created file in a folder. I came across a previous post on this subject and I have taken the liberty of posting its code below (I hope this doesn't break forum rules). My problem is that I don't understand how it works. What is "fileDate"? If both "file.DateCreated" and "fileDate" belong to the same file then how can the former ever exceed the latter? Sorry to be thick, but I just don't get it. I would be very grateful if someone could explain it to me. Many Thanks. Here is the code:

Sub mostRecentCreatedFile()
 
    'Folder path you want to search
    Dim strFolderPath As String: strFolderPath = "C:\Meeting 2013"
 
    Dim fileSystem As Object: Set fileSystem = CreateObject("Scripting.FileSystemObject")
 
    Dim folder As Object: Set folder = fileSystem.GetFolder(strFolderPath)
    Dim file As Object
 
    Dim strFilename As String
    Dim fileDate As Date
    Dim ShowDate As Date
 
    'Loop through all files in directory
    For Each file In folder.Files
 
        'Find newest file that is also an HTML or XML (Report/Excel)
        'If file.DateCreated > fileDate And (file.Type = "HTML Document" Or file.Type = "XML") Then
 
        'Finds newest file of any type
        If file.DateCreated > fileDate Then
            fileDate = file.DateCreated
            strFilename = file.Name
            ShowDate = file.DateCreated
        End If
    Next file    'Displays last created file name
    MsgBox strFilename & "  " & fileDate & "  " & ShowDate
 
End Sub


Sub

 

[John_w]

fileDate stores the date of the newest file as the For Each loops through each file and is updated when file.DateCreated sees a file which is newer than the newest file seen so far. Add the following line inside the loop and step through the code with the F8 key if you're still not sure:

        Debug.Print file.Name, file.DateCreated, fileDate

 

[El_Diabolo]

Thank you, John_W. The penny has dropped. Sometimes I think I have only two neurons pointing in the same direction, but that would probably be disrespectful to people with two neurons pointing in the same direction. Best regards.

 

[Rick Rothstein]

Thank you, John_W. The penny has dropped. Sometimes I think I have only two neurons pointing in the same direction, but that would probably be disrespectful to people with two neurons pointing in the same direction. Best regards.

Just pointing out that the most recently "created" file is not necessarily the most recently "modified" file. So, if you had a file created a year ago along with several newer files and that year old file was just worked on, the code you posted will not return the name/date/time for it, rather, it will return the last file created in that directory. If you want to know what the last file that was modified in a given folder, then you can use this code to find it...

Sub MostRecentModifiedFile()

  Dim strFolderPath As String, FN As String, FileName As String, MaxDateTime As Date
  
  '  Folder path you want to search
  strFolderPath = "C:\Test"
  
  FN = Dir(strFolderPath & "\*.*")
  Do While Len(FN)
    If FileDateTime(strFolderPath & "\" & FN) > MaxDateTime Then
      FileName = FN
      MaxDateTime = FileDateTime(strFolderPath & "\" & FN)
    End If
    FN = Dir()
  Loop
  
  MsgBox FileName & " - " & MaxDateTime
  
End Sub

 

[El_Diabolo]

Thank you, Mr Rick. Very kind and a pleasure to hear from a man of such renown. Thank you for highlighting the "created/modified" anomaly. In this case it is definitely the most recently created file I am after.
However, I am very happy to now have an elegant solution for the last modified file. The reason I made the original post is that I am trying to come up with a solution for a particular requirement, albeit my solution would be pretty clumsy. I think forum rules require me to start a new thread for that, rather than expand this thread with new information. If possible could you please tell me if I must start a new thread, or not. Many thanks.

Best regards.

 

[Rick Rothstein]

If possible could you please tell me if I must start a new thread, or not. Many thanks.

Probably (I'm not really sure), but I think it would be better for you to start a new thread... more people will see the question (if you post it here, only people who saw and were interested in your original question will see it... others who were not interested in your original question would not look back here again to see that you posted a new question).

 

[El_Diabolo]

Thank you, Rick. I'll do that.