The FileDateTime function is a built-in VBA (Visual Basic for Applications) function that is used to retrieve the date and time that a specific file was last modified. It is commonly used in macro programming to automate tasks involving file management.
VBA FileDateTime Function – Purpose, Syntax and Arguments
Purpose:
The purpose of the FileDateTime function is to provide a quick and efficient way to retrieve the date and time that a file was last modified. This allows for easier management and organization of files, especially when dealing with a large number of files.
Syntax:
FileDateTime(path)
Arguments:
- path: This is a required argument that specifies the path to the file for which you want to retrieve the date and time. It can be a full file path or a relative file path.
Example:
Suppose we have a macro that deals with a large number of Excel files, and we want to organize them based on their last modified dates. We can use the FileDateTime function to retrieve the date and time of each file and sort them accordingly.
Option Explicit Sub FileManagement() Dim path As String Dim file As String Dim dateModified As Date 'specify the folder path here path = "C:\Users\Username\Documents\Files\" 'loop through all files in the specified folder file = Dir(path & "*.xlsx") Do While file <> "" dateModified = FileDateTime(path & file) 'code to sort files based on dateModified goes here file = Dir() Loop End Sub
Remarks:
- The FileDateTime function is only used to retrieve the last modified date and time of a file. It cannot be used to set or modify the date and time of a file.
- If the path provided is invalid or the file does not exist, the function will return an empty string.
- The date and time returned are based on the local time of the computer where the file resides.
Important Notes:
- The FileDateTime function is not available in VBA for Mac.
- The function may return incorrect results if the file is located on a network share with a different time zone.
Understanding VBA FileDateTime Function with Examples
Getting the Creation Date of a File
Sub GetCreationDate() Dim filePath As String Dim fileDate As Date ' Specifying the file path filePath = "C:\Users\John\Desktop\TestFile.txt" ' Using the FileDateTime function to get the creation date of the file fileDate = FileDateTime(filePath) ' Displaying the creation date MsgBox "The creation date of the file is: " & fileDate End Sub
The above example shows how the FileDateTime function can be used to get the creation date of a specified file. This function takes in the file path as an argument and returns the creation date of the file as a date value. In this example, we have stored the file path in a variable named “filePath” and then used the FileDateTime function to retrieve the creation date of the file. The result is then displayed in a message box using the MsgBox function.
Getting the Last Modified Date of a File
Sub GetLastModifiedDate() Dim filePath As String Dim fileDate As Date ' Specifying the file path filePath = "C:\Users\John\Desktop\TestFile.txt" ' Using the FileDateTime function to get the last modified date of the file fileDate = FileDateTime(filePath) ' Displaying the last modified date MsgBox "The last modified date of the file is: " & fileDate End Sub
In this example, we have used the FileDateTime function to get the last modified date of a file. The only difference from the previous example is that we have used the same function to get the last modified date instead of the creation date. This function can also be used to get the last accessed date of a file by passing in the file path as an argument.
Separating the Date and Time
Sub SeparateDateTime() Dim filePath As String Dim fileDate As String Dim fileTime As String ' Specifying the file path filePath = "C:\Users\John\Desktop\TestFile.txt" ' Using the FileDateTime function to get the creation date and time of the file fileDate = Format(FileDateTime(filePath), "mm/dd/yyyy") fileTime = Format(FileDateTime(filePath), "h:mm:ss AM/PM") ' Displaying the separated date and time MsgBox "The file was created on " & fileDate & " at " & fileTime End Sub
The FileDateTime function can also be used to separate the date and time values of a file. In this example, we have used the Format function to specify the format in which we want to display the date and time. The “mm/dd/yyyy” format is used to display the date in a month/day/year format and the “h:mm:ss AM/PM” format is used to display the time with the AM/PM indicator. The result is then displayed in a message box.
Checking if a File Exists
Sub CheckFileExists() Dim filePath As String Dim fileExists As Boolean ' Specifying the file path filePath = "C:\Users\John\Desktop\TestFile.txt" ' Checking if the file exists fileExists = FileExists(filePath) ' Displaying the result If fileExists Then MsgBox "The file exists" Else MsgBox "The file does not exist" End If End Sub
The FileDateTime function can also be used to check if a file exists in a specified location. This can be done by using the FileExists function, which returns a Boolean value. If the file path provided to this function exists, it returns True, otherwise it returns False. In this example, we have stored the result of the FileExists function in a Boolean variable named “fileExists” and then used an If statement to display the result in a message box.
Using a Variable as the File Path
Sub UseVariableFilePath()
Dim filePath As String
Dim fileDate As Date
' Asking the user to enter the file path
filePath = InputBox("Enter the file path")
' Using the FileDateTime function with the variable file path
fileDate = FileDateTime(filePath)
' Displaying the creation date
MsgBox "The creation date of the file is: " & fileDate
End Sub
The FileDateTime function can also be used with a variable as the file path instead of directly specifying it. This can be useful when the file path is not known beforehand and needs to be assigned by the user. In this example, we have used the InputBox function to ask the user to enter the file path and then used the FileDateTime function to retrieve the creation date of the specified file path.
Handling Errors
Sub HandleErrors() Dim filePath As String Dim fileDate As Date On Error GoTo ErrorHandler ' Specifying the file path filePath = "C:\Users\John\Desktop\TestFile.txt" ' Using the FileDateTime function to get the creation date of the file fileDate = FileDateTime(filePath) ' Displaying the creation date MsgBox "The creation date of the file is: " & fileDate Exit Sub ErrorHandler: MsgBox "Error: " & Err.Number & " - " & Err.Description Resume Next End Sub
Like any other function in VBA, the FileDateTime function can also produce errors. It is important to handle these errors to avoid any disruptions or unwanted results in the code. In this example, we have used the On Error statement to handle any errors that may occur in the code. The error handling code is enclosed within the On Error statement and the Resume Next statement is used to continue the code execution after the error has been handled. This helps in making the code more robust and prevents it from crashing.
Summary
The FileDateTime function in VBA is a useful tool for retrieving the creation date, last modified date, and last accessed date of a file. It can also be used to separate the date and time values and check if a file exists. This function is easy to use and can be applied in various scenarios. The examples provided above demonstrate the different ways in which the FileDateTime function can be used in VBA code.
It is important to keep in mind that the FileDateTime function may not work for certain files and file paths which contain special characters or exceed the maximum character limit. In such cases, it is recommended to use other methods or functions provided by VBA to work with file properties.
We hope this blog post has helped in understanding the FileDateTime function in VBA. With the examples provided and a better understanding of how the function works, you can utilize this tool to better manage and work with files in your VBA projects.
