Table of Contents
The FileDateTime function in VBA is a useful tool for retrieving the date and time that a file was last modified. This can be particularly helpful when working with large amounts of data or when tracking changes to a specific file. To use the FileDateTime function, simply provide the file path as the argument and the function will return a date and time value. For example, the code “FileDateTime(“C:UsersJohnDocumentsExample.xlsx”)” would return the date and time that the “Example.xlsx” file was last modified. This function can be incorporated into various VBA applications to track file changes, automate file management, or simply display the last modified date and time for user reference.
Use FileDateTime Function in VBA (With Example)
You can use the FileDateTime function in VBA to return the date and time when a file was created or last modified.
Here is one common way to use this function in practice:
Sub CheckLastModify()
Dim wb_name As String
wb_name = InputBox("Please enter the workbook name:")
MsgBox FileDateTime(wb_name)
End Sub
When this macro is run, an input box will appear where a user can type in the name of an Excel workbook.
The macro will then produce a message box that contains the date and time when the particular workbook was created or last modified.
The following example shows how to use this syntax in practice.
Example: How to Use FileDateTime Function in VBA
Suppose we have an Excel workbook called My_Workbook.xlsx located in the following location:
C:UsersbobDocumentsmy_workbook.xlsx
Suppose we would like to check when this workbook was last created or modified.
We can create the following macro to do so:
Sub CheckLastModify()
Dim wb_name As String
wb_name = InputBox("Please enter the workbook name:")
MsgBox FileDateTime(wb_name)
End Sub
Once we run this macro, a box will appear where I can type in the path to the workbook in the input box:
Once I click OK, the macro will produce the following message box:
The macro tells us that the workbook was last modified on 7/28/2023 at 9:27:01 AM.
Sub CheckLastModify()
Dim wb_name As String
wb_name = InputBox("Please enter the workbook name:")
MsgBox DateValue(FileDateTime(wb_name))
End Sub
Now when you run the macro and type in the file path, the message box will only display the date the field was last modified without the time:
Note: You can find the complete documentation for the FileDateTime function in VBA .
Additional Resources
The following tutorials explain how to perform other common tasks in VBA: