It may be useful to know the last time a file was modified or, if never modified, when the file was created. You could use this information to run an update script, avoid unnecessary processing, check the existence of a file, and even avoid overwriting important data. The VBA FileDateTime function is a simple function requiring only one input, but this simple function is so powerful it can be used in a variety of applications.
VBA FileDateTime Function
The FileDateTime function is a built-in VBA function with only a single input parameter, PathName. It doesn’t matter how you enter the PathName argument, as long as it’s a string. It can be chosen by a file picker, deduced by the current script, or painstakingly typed out by the user.
FileDateTime(PathName as String)The output is a Date variable, which can then be fed into any number of date-manipulating functions, like the DateAdd function. The output provides the last modified date/time or, if the file was never modified, the date/time of file creation. When displayed, it takes the form of the locale settings of the current system, so it’s safer to store the Date than to convert it to a String until necessary. The Date type will not confuse 03-03-03 with 03-03-03 or 03-03-03, while a String most certainly will. That’s mm-dd-yy, dd-mm-yy, yy-mm-dd, by the way - or is it? Programming with dates is confusing since different regions use different date formats. That’s actually why we created an entire tutorial on VBA date formatting.
Anyway, VBA can implicitly convert Dates to Strings when you want to display the date to the user. That said, to be rigorous, apply a CStr conversion to the Date only when you’re truly ready to use the String version.
As far as inputs and outputs go, they are not much simpler or more intuitive than that. Input a file path, output a date.
Another "Output"
As long as the file specified in PathName exists, there will be a Date output. If you get a Run-time error ‘53’, it means the file could not be found. Depending on the input capture method (file picker, typed out, deduced), the script should call the appropriate error handler.
Run-time errors are usually discouraged, but a meaningful error saying a file does not exist could be really useful. For example, if your code is clean, you may consider applying On Error Resume Next to signal the file doesn’t exist and thus the script is free to create a file with that name. However, I only recommend doing this if you are absolutely certain there is no bug in the PathName capture method.
Applications
So why would you care about the last modified date or the creation date of a file? Many decisions in life are sensitive to timing, and the FileDateTime function provides some information to help us determine what we should do next. As is common in information communication theory, the presence of the Run-time error 53 error also provides some information.
Avoiding Overwrites
It’s an awful feeling to realize you just overwrote an old file that had data you wanted to keep. It is infuriating to find out that a program didn’t alert you but instead blindly overwrote a file. Luckily, Windows and most modern major applications have precautions built in, such as appending numbers to a download if a pre-existing file is found. When you write your own VBA scripts, no such precautions are in place unless you put them in place.
Take a look at our Export Charts tutorial, for example. The .Export method in that tutorial writes the exact name the user provides. If there is already a file with that file path (name and folder), the method overwrites the existing one. For periodic scripts, this could leave you with only the latest chart, while for iterative scripts, you will only see a single chart (the last processed) instead of the entire set. Using the FileDateTime is a quick way to check if a file exists (though it’s worth mentioning the VBA Dir Function is the preferred way to check if a file exists):
Sub FileDateTimeDemo()
Dim lastModTime As Date 'initializes as 00:00:00
On Error GoTo handler 'when file not found, handle the error
lastModTime = FileDateTime(targetFile) 'resets initial 00:00:00 to found time
On Error GoTo 0
If lastModTime <> 0 Then
'code for when targetFile already exists
Else
'code for when targetFile did not exist (may exist now, depending on error handler code)
End If
End SubImportantly here, note that
If the function does throw an error because the specified file does not exist, one potential error handler could enter the subroutine responsible for creating the file then return control to the original subroutine. This code flow is outside the scope of this article, but I encourage you to read our VBA error handling tutorials to get a grasp on this type of logic flow.
Make powerful macros with our free VBA Developer Kit This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy, along with our Big Book of Excel VBA Macros, to your email address below.
Timing Overwrites
Overwriting is not always a bad thing. In fact, if we never overwrote any data, the world would be filled with hard drives and hard drives of useless data.
One case may be to overwrite a file that is older than a certain threshold. Perhaps there are 7 files in a folder, each named for a day of the week. In our imagined scenario, the data need only be stored for one week, so the programmer could cycle through the folder checking for the file more than 6 days old. Once found, we know it’s the oldest file in our set (i.e., the one from one week ago), and that is the one to overwrite.
Inversely, we may want to overwrite the latest file but only on the same day. The script may output a file, do some checks that take a variable number of minutes, and reschedule the script for an hour later if the checks succeed. Each day has its own file, resetting at midnight. One way to schedule the script, relative to the time of file modification (not the time of the completion of the checks), is to use FileDateTime and DateAdd functions:
newScriptTime = DateAdd("h", 1, FileDateTime(outputFile))Once midnight arrives, a new FileDateTime and Now together.
Saving Time
Yet another scheduling example may be to check that a file has not been created or modified within the last day in a folder. Your macro could loop through all files in a folder and if a file from the same day exists, we could tell the user to only run the script once per day. Alternatively, we could warn the user such a file was already created for the day and ask if they really want to continue to reprocess the data and/or overwrite the same-day file.
The FileDateTime is a simple function with intuitive inputs and outputs. It’s quite useful for scheduling and determining file order, including the earliest and latest files in a set (which we often target). Naturally, you may have other applications for FileDateTime. Whatever they be, we wish you bug-free code and low frustration levels.
For more VBA tips like this one, please subscribe using the form below. We publish VBA tutorials on topics no one else has covered and we strive to help you get the most out of your macros.
