Pearson Software Consulting Services
Get/Set File Times
Neither Excel nor VBA provide a mechanism for retrieving
or changing the file times of a file. The three file times are Create Time, Last Access Time
Time (write). This page describes three VBA procedures, GetFileDateTime,
SetFileDateTime, that you can use retrieve or set the times associated with
a file. Times associated with a file are stored by the system in
Greenwich Mean Time (GMT), also called
Universal Coordinated Time (UTC). The GetFileTime and SetFileTime Windows
API functions, which are used in these procedures, retrieve and set the file
times as GMT times. Therefore it is necessary to use additional Windows API
functions to convert the FileTime value of a file's times to the local time
zone. The procedures below do this conversion for you. For procedures that
convert between GMT and Local Time in general, see the
Converting Between GMT And Local Times page.
Note: In this documentation, we will use the terms "Date" and "Time" interchangeably. For example, the term "Create Date" should be understood to refer to both the date and time of the file's creation. If we need to refer to only the file's Date (without a time component) or the file's Time (without a date component) that distinction will be made clear in the documentation, but in general, both "Date" and "Time" should be interpreted to mean both the date and the time.
It should be noted that the accuracy of the file times depends upon the File System of the drive on which the file resides. For example, on a Windows NT FAT system, the create time has a resolution of 10 milliseconds, while the last modified time has a resolution of about two seconds, and the last access time has a resolution of one full day. On NTFS file systems, the access time has a resolution of about 1 hour.
Public Type FILETIME dwLowDateTime As Long dwHighDateTime As Long End Type
The FILETIME components, when taken together, form a 64-bit Integer, a data type that is not supported in VB/VBA. The system treats the components as UNSIGNED 32-bit Longs, and VB/VBA doesn't support unsigned variables. Therefore, if you need to change a FILETIME, or if you want to view the value of a FILETIME in a readable and meaningful format, use one of the conversion routines, such as FileTimeToSerialTime, that are included in the modTimeConversions module, available as a individual bas module here or in the complete workbook here.
You can discern no meaningful information by examining the contents of a FILETIME directly. You should not use the FILETIME values to determine if one date is earlier or later than another date. Instead, you should convert the FILETIMEs to a Serial Times using FileTimeToSerialTime in the modTimeConversions module and compare the Serial Times.
Don't attempt to do arithmetic on a FILETIME value. Instead, convert it to either a Serial Date using FileTimeToSerialTime in the modTimeConversions module or a SYSTEMTIME using the function Windows API function FileTimeToSystemTime, documented on MSDN, make your adjustments to the Serial Date or SYSTEMTIME structure, and convert that back to a FILETIME using the SerialTimeToFileTime in the modTimeConversions module or with the SystemTimeToFileTime Windows API function. The SystemTimeToFileTime is declared as follows:
Private Declare Function SystemTimeToFileTime Lib "kernel32" _ (lpSystemTime As SYSTEMTIME, _ lpFileTime As FILETIME) As Long
Public Type SYSTEMTIME wYear As Integer wMonth As Integer wDayOfWeek As Integer wDay As Integer wHour As Integer wMinute As Integer wSecond As Integer wMilliseconds As Integer End TypeNote that the wDayOfWeek element of SYSTEMTIME is based on Sunday = 0 through Saturday = 6. This differs from the Weekday VB function, so you must subtract 1 from the result of the Weekday function when setting the wDayOfWeek element, or add 1 to the value of wDayOfWeek when retrieving the wDayOfWeek element.
You can convert a SYSTEMTIME Type to a Serial date
with the SystemTimeToSerialTime
function in the modTimeConversions module, or to a FILETIME structure
with the SystemTimeToFileTime
Windows API function,
System File Times
Both GetFileDateTime and SetFileDateTime can be used to read or write the Create Date, Last Access Date, and Last Modified Date. You specify which date to read or write using an enum variable called FileDateToProcess. This enum is shown below:
Public Enum FileDateToProcess FileDateCreate = 1 FileDateLastAccess = 2 FileDateLastModified = 3 End Enum
You specify one of these values to indicate which file time you want to set or retrieve.
Properly Using The Type Declarations:
Functions For Retrieving Or Setting File Times
The code for the GetFileDateTime, GetFileDateTimeAsFILETIME and SetFileDateTime is too lengthy to neatly display on this web page. Altogether, the code totals about 1500 lines. The functions definitions and example code is included below. To get the actual VBA code, download the bas modules or the complete workbook.
Public Function GetFileDateTime(FileName As String, _ WhichDateToGet As FileDateToProcess, _ Optional NoGMTConvert As Boolean = False) As Double
where FileName is the name of the file to query and WhichDateToGet is a value indicating which date (Create Date, Last Access Date, Last Modified Date) is to be retrieved. The value of WhichDateToGet is one of the elements of the FIleDateToProcess enum. The NoGMTConvert parameter indicates whether the file's time is converted from GMT (the stored value returned by the GetFileTime API function) to local time. If this value is omitted or False, the file's GMT time is converted to the local time of day. If NoGMTConvert is True, the GMT time returned by the GetFileTime API function is not converted from GMT to Local time. Because Windows converts GMT to Local time when it displays a file's time, the displayed time will be N hours earlier (or later if you are East of GMT) than the value returned by this function. It is recommended that you NOT set NoGMTConvert to TRUE. In nearly all circumstances, you will want this parameter to be False.
Public Enum FileDateToProcess FileDateCreate = 1 FileDateLastAccess = 2 FileDateLastModified = 3 End Enum
GetFileDateTime will return the VB/VBA date/time Double variable, or -1 if an error occurs. Possible errors include passing the name of a non-existent file or a invalid value for WhichDateToGet. The result time is adjusted to reflect Daylight Savings Time.
And example of GetFileDateTime is shown below: Sub TestGetFileDateTime() Dim FName As String Dim Result As Boolean Dim TheNewTime As Double Dim WhatTime As FileDateToProcess ''''''''''''''''''''''''''''' ' Set the paramaeters to ' GetFileDateTime. ''''''''''''''''''''''''''''' FName = "C:\Folder\File.txt" WhatTime = FileDateCreate TheNewTime = GetFileDateTime(FileName:=FName, WhichDateToGet:=WhatTime) If TheNewTime < 0 Then Debug.Print "An error occurred in GetFileDateTime" Else Debug.Print "File Date/Time is to: " & Format(TheNewTime, "dd-mmm-yyyy hh:mm:ss") End If End Sub
Public Function GetFileDateTimeAsFILETIME(FileName As String, _ WhichDateToGet As FileDateToProcess, FTime As FILETIME, _ Optional ConvertFromGMT As Boolean = False) As Boolean
where FileName is the name of the file whose time is to be retrieved, WhichDateToGet indicates which date to retrieve (Create, Access, or Modify), FTime is a FILETIME variable that will be populated with the requested file time, and ConvertFromGMT indicates whether the procedure should convert the file time from GMT to Local Time. Example of GetFileTimeAsFILETIME:
Dim DateSerial As Date Dim FT As FILETIME Dim FileName As String Dim ConvertFromGMT As Boolean Dim Res As Boolean FileName = "C:\Test.txt" ConvertFromGMT = True ' False = Do NOT convert from GMT to Local. True = Convert from GMT To Local Res = GetFileTimeAsFILETIME(FileName:=FileName, _ WhichDateToGet:=FileDateLastModified, _ FTime:=FT, _ ConvertFromGMT:=ConvertFromGMT) If Res = True Then '''''''''''''''''''''''''''' ' Success. Convert FT to ' a serial date for display. '''''''''''''''''''''''''''' DateSerial = FileTimeToSerialTime(FileTimeValue:=FT) Debug.Print "FILETIME Low: " & Hex(FT.dwLowDateTime) & _ " High: " & Hex(FT.dwHighDateTime) & _ " Serial: " & Format(DateSerial, "dd-mmm-yyyy hh:mm:ss") Else Debug.Print "An error occurred in GetFileTimeAsFILETIME." End If
Public Function SetFileDateTime(FileName As String, _ FileDateTime As Double, WhichDateToChange As FileDateToProcess, _ Optional NoGMTConvert As Boolean = False) As Boolean
where FileName is the name of the file to change, FileDateTime is the new time to assign to the file, in standard VB/VBA dddd.tttttt Double variable format, and WhichDateToChange indicates which date/time of the file to change. It is one of the value of the FileDateToProcess enum described above. NoGMTConvert indicates whether FileDateTime should be converted to GMT. Windows stores file times in GMT, and there in nearly all circumstances you should set NoGMTConvert to False. Since Windows displays file times in local time format, the displayed file time will be N hours later (or earlier if you are East of GMT) than the time passed in as FileDateTime.
Dim FName As String Dim Result As Boolean Dim TheNewTime As Double Dim WhatTime As FileDateToProcess Dim TheNewDate As Double ''''''''''''''''''''''''''''''' ' Craete the new date and time. ''''''''''''''''''''''''''''''' TheNewDate = DateSerial(2006, 7, 4) TheNewDate = TheNewDate + TimeSerial(12, 34, 56) FName = "C:\Test.txt" Result = SetFileDateTime(FileName:=FName, FileDateTime:=TheNewDate, _ WhichDateToChange:=WhatTime, NoGMTConvert:=False) If Result = True Then Debug.Print "File date/time successfully modified." Else Debug.Print "An error occurred with SetFileDateTime." End If
Public Function CompareFileTimes(FileName1 As String, FileName2 As String, _ WhichDate As FileDateToProcess) As Variant
Dim Res As Variant Res = CompareFileTimes(FileName1:="C:\Test1.txt", FileName2:="C:\Test2.txt", _ WhichDate:=FileDateCreate) If IsNull(Res) = True Then Debug.Print "An error occurred in CompareFileTimes" Else If Res < 0 Then Debug.Print "File1 is earlier than File2" Else Debug.Print "File1 is later than File2" End If End If
You can download the bas module files here or a complete workbook with examples here.
Created By Chip Pearson and
Pearson Software Consulting, LLC
This Page: Updated: November 06, 2013
MAIN PAGE About This Site Consulting Downloads
Page Index Search Topic Index What's New Links Legalese And Disclaimers
© Copyright 1997-2007 Charles H. Pearson