Check a file's last modification time

Ray writes and asks how we can check if a file's modification date is over a certain period of time, and act accordingly.

He has a procedure that prompts the end user for a folder to save some workbooks. Problem is, if the wrong folder is selected, a worm hole will open and the Earth will be destroyed. I kid.

So, how do we check if the "right" folder is selected? In Ray's case, the "wrong" folder will contain other workbooks over 180 days old. The following function will check if a given file is over 180 days old. (See Usenet for original solution)

Function IsFileTooOld(fileName As String) As Boolean
  IsFileTooOld = (Now - FileDateTime(fileName) > 180)
End Function

So let's ask the end user for a folder. I use the code found at Browse For Folder to do that. After, I grab the first workbook in the folder and check if the modification date is over 180 days from today. If so, then we know we have the wrong folder; ALL of the files are over 180 days old, so we only need to check one (the first one).

Dim folder As String
Dim fileName As String
Dim pathSep As String

pathSep = Application.PathSeparator

folder = BrowseForFolder
fileName = Dir(folder & pathSep & "*.xls")

If Len(fileName) > 0 Then
  If IsFileTooOld(folder & pathSep & fileName) Then
    MsgBox _
    "The folder you selected contains processed files. Please select another."
  End If
End If

But we're not done yet. We need to loop this to keep asking until a valid folder is selected. We'll use a Do Loop with at least one iteration.

Dim folder As String
Dim fileName As String
Dim pathSep As String
Dim validFolder As Boolean

pathSep = Application.PathSeparator

Do
  ' get folder and first workbook name
  folder = BrowseForFolder
  fileName = Dir(folder & pathSep & "*.xls")

  If Len(fileName) > 0 Then
    If IsFileTooOld(folder & pathSep & fileName) Then
      MsgBox _
    "The folder you selected contains processed files. Please select another."
    Else
      ' first
      validFolder = True
    End If
  Else ' no files at all, must be a valid folder
    validFolder = True
  End If
Loop Until validFolder = True