Excel Developer Tip

Excel page

Tip archives

Determining When a File Was Created

You probably know that you find out when a file was created by right-clicking the file name in the Windows Explorer, clicking Properties on the context menu.

If you're in Excel, you can determine the creation date of the active workbook by selecting Properties from the File menu. The file creation date appears twice in the Properties dialog box: on the General tab, and on the Statistics tab. In many cases these two dates are different!

• The file creation date and time on the Statistics tab is when the file was originally created.
• The file creation date and time on the General tab is when the file was first saved on your computer.

You can use the VBA statement below to examine the actual file creation date and time (the date and time shown in the Statistics tab):

  MsgBox ActiveWorkbook.BuiltinDocumentProperties.Item _
      ("Creation date").Value

If you'd like to determine the date and time that the file was saved on your computer, you can use the routines listed below. The result is the same date and time that is displayed in the General tab of the Properties dialog box. 

You'll need to copy all of the code below to a module. The ShowFile subroutine displays the file creation date and time for the active workbook. You can easily customize this subroutine to show the creation date for any file.

VBA Code

'32 bit Windows declarations
Public Declare Function FindFirstFile Lib "kernel32" Alias "FindFirstFileA" _
    (ByVal lpFileName As String, lpFindFileData As WIN32_FIND_DATA) As Long
Public Declare Function FileTimeToSystemTime Lib "kernel32" _
    (lpFileTime As FILETIME, lpSystemTime As SYSTEMTIME) As Long
Declare Function FileTimeToLocalFileTime Lib "kernel32" _
    (lpFileTime As FILETIME, lpLocalFileTime As FILETIME) As Long

Public Type FILETIME
    dwLowDateTime As Long
    dwHighDateTime As Long
End Type

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 Long
End Type

Public Type WIN32_FIND_DATA
    dwFileAttributes As Long
    ftCreationTime As FILETIME
    ftLastAccessTime As FILETIME
    ftLastWriteTime As FILETIME
    nFileSizeHigh As Long
    nFileSizeLow As Long
    dwReserved0 As Long
    dwReserved1 As Long
    cFileName As String * 260
    cAlternate As String * 14
End Type

Private Function FileDate(FT As FILETIME) As String
'   convert the FILETIME to LOCALTIME, then to SYSTEMTIME type
    Dim ST As SYSTEMTIME
    Dim LT As FILETIME
    Dim t As Long
    Dim ds As Double
    Dim ts As Double
    t = FileTimeToLocalFileTime(FT, LT)
    t = FileTimeToSystemTime(LT, ST)
    If t Then
        ds = DateSerial(ST.wYear, ST.wMonth, ST.wDay)
        ts = TimeSerial(ST.wHour, ST.wMinute, ST.wSecond)
        ds = ds + ts
        If ds > 0 Then
            FileDate = Format$(ds, "mm/dd/yy hh:mm:ss")
        Else
            FileDate = "(no date)"
        End If
    End If
End Function

Private Sub ShowFileInfo()
'   This subroutine demonstrates the technique
    Dim hFile As Long
    Dim WFD As WIN32_FIND_DATA
    Dim FullName As String
    Dim Created As String
    Dim LastWrite As String
     
'   FullName is the path and filename
'   Substitute any valid file and path
    FullName = ActiveWorkbook.FullName
    hFile = FindFirstFile(FullName, WFD)
    
    If hFile > 0 Then
        Created = FileDate(WFD.ftCreationTime)
        MsgBox "File Created: " & Created, vbInformation, FullName
    Else
        MsgBox "File not found.", vbCritical, FullName
    End If
End Sub