when a folder has no files in it, the date value is shown as "1900-01-01". I want the value to be today's date if there are no files in it
The below code helps in finding the newest file date in a folder, but when a folder has no files in it, then the date value is shown as "1900-01-01". Instead I want the value to be today's date if there are no files in it.
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 accomplish this task.
After the loop insert this code line.
if NewestDT = DateValue("1900-01-01") then NewestDT = Date