1

The below code helps in finding the oldest file date in a folder, but I am looking for a VBA code that can be help me in finding the newest file date or most recent file date in a folder.

    Sub oldestdate()

Range("G10").Value = GetOldestFile("C:\Users\xxx\Downloads\My files")

End Sub


Public Function GetOldestFile(ByVal FileFolder As String, _
                              Optional ByVal FileMask As String = "*.*", _
                              Optional ByVal FullName As Boolean = True) As String

   Dim FoundFile        As String
   Dim FileDT           As Date

   Dim NewestFile       As String
   Dim NewestDT         As Date

   Dim FS As Object

   '// Get rid of any terminating '\' just to get to a known state
   If Right(Trim(FileFolder), 1) = "\" Then
      FileFolder = Left(FileFolder, Len(Trim(FileFolder)) - 1)
   End If

   '// Get First file found in described folder
   FoundFile = Dir$(FileFolder & "\" & FileMask)

   '// Default return date
   NewestDT = DateValue("1900-01-01")

   Set FS = CreateObject("Scripting.FileSystemObject")

   '// Loop through the rest of the files in that folder
   Do Until FoundFile = ""

      FileDT = FS.GetFile(FileFolder & "\" & FoundFile).DateCreated

      '// Compare Current File datetime with oldest found
      If FileDT > NewestDT Then
         NewestFile = FoundFile
         NewestDT = FileDT
      End If

      '// Get next file
      FoundFile = Dir$
   Loop

   Set FS = Nothing

   GetOldestFile = Format(NewestDT, "mm/dd/yyyy")

End Function

Please let me know how to find the newest file date in a folder.

Community
  • 1
  • 1
sat
  • 87
  • 1
  • 3
  • 12
  • Check this out as it seems to be what your looking for https://stackoverflow.com/questions/9205137/using-vbscript-to-find-most-recent-file-date-in-a-single-folder – Darthchai Feb 11 '18 at 23:33

1 Answers1

2

You just have to revert the // Compare Current File datetime with oldest found part to look for a newer date instead for an older one:

First initialise with a reasonably old date:

NewestDT = DateValue("1900-01-01")

And change the target condition:

...
If FileDT > NewestDT Then
   NewestDT = FileDT
   ...
End if

You might have to adapt the initialization for the oldest date, I didn't test it. I also suggest you change the naming of the variables.

moa
  • 93
  • 2
  • 8
  • Got it ! Thanks. Very helpful – sat Feb 11 '18 at 23:55
  • Quick question, when the folder has no files in it, then the date value is shown as "1900-01-01". But I don't want that value to be shown like that, because it doesn't make sense. Instead I want the value to be today's date when there are no files in the folder. Do you know what need to be changed in the code to accomplish this task? Thanks. – sat Feb 13 '18 at 00:09