Getting Most Recently Created File in a Folder

El_Diabolo

New Member
Joined
Nov 23, 2013
Messages
4
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:

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
 
Last edited:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,811
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:
Code:
        Debug.Print file.Name, file.DateCreated, fileDate
 

El_Diabolo

New Member
Joined
Nov 23, 2013
Messages
4
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,034
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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...

Code:
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

New Member
Joined
Nov 23, 2013
Messages
4

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,034
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,132,790
Messages
5,655,318
Members
418,189
Latest member
sam1123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top