Check a file's last modification time
March 10, 2010 • JP • No Comments • Rate This Article
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)
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 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 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
Excel ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Ships and flags
Next Post: Get currency conversion rates with web services
Speak Your Mind
Tell us what you're thinking...Certain comments (including first-time comments) are subject to moderation and will not appear immediately. Please view the Comment Policy for more information. To post VBA code in your comment, use tags like this: [cc lang='vb']Code goes here[/cc].