View Full Version : FileDateTime(pathname)

07-25-2011, 06:15 AM


Returns a Variant (Date) that indicates the date and time when a file was created or last modified.

if a file is modified, the FileDateTime function will return the last modify date.

However, I just want to file's creation date.

How can I do that?


Kenneth Hobs
07-25-2011, 06:31 AM
This is a robust method. We can make it more simple if needed.
Option Explicit

Type FileAttributes
Name As String
Size As String
FileType As String
DateModified As Date
DateCreated As Date
DateAccessed As Date
Attributes As String
Status As String
Owner As String
Author As String
Title As String
Subject As String
Category As String
Comments As String
Keywords As String
End Type

Sub Test_GetFileAttributes()
Dim fa As FileAttributes
fa = GetFileAttributes(ThisWorkbook.FullName)
MsgBox fa.DateCreated
End Sub

'Add Tools > References > Microsoft Shell Controls and Automation
Public Function GetFileAttributes(strFilePath As String) As FileAttributes
' Shell32 objects
Dim objShell As Shell32.Shell
Dim objFolder As Shell32.Folder
Dim objFolderItem As Shell32.FolderItem

' Other objects
Dim strPath As String
Dim strFileName As String
Dim i As Integer

' If the file does not exist then quit out
If Dir(strFilePath) = "" Then Exit Function

' Parse the file name out from the folder path
strFileName = strFilePath
i = 1
Do Until i = 0
i = InStr(1, strFileName, "\", vbBinaryCompare)
strFileName = Mid(strFileName, i + 1)
strPath = Left(strFilePath, Len(strFilePath) - Len(strFileName) - 1)

' Set up the shell32 Shell object
Set objShell = New Shell

' Set the shell32 folder object
Set objFolder = objShell.Namespace(strPath)

' If we can find the folder then ...
If (Not objFolder Is Nothing) Then

' Set the shell32 file object
Set objFolderItem = objFolder.ParseName(strFileName)

' If we can find the file then get the file attributes
If (Not objFolderItem Is Nothing) Then

GetFileAttributes.Name = objFolder.GetDetailsOf(objFolderItem, 0)
GetFileAttributes.Size = objFolder.GetDetailsOf(objFolderItem, 1)
GetFileAttributes.FileType = objFolder.GetDetailsOf(objFolderItem, 2)
GetFileAttributes.DateModified = CDate(objFolder.GetDetailsOf(objFolderItem, 3))
GetFileAttributes.DateCreated = CDate(objFolder.GetDetailsOf(objFolderItem, 4))
GetFileAttributes.DateAccessed = CDate(objFolder.GetDetailsOf(objFolderItem, 5))
GetFileAttributes.Attributes = objFolder.GetDetailsOf(objFolderItem, 6)
GetFileAttributes.Status = objFolder.GetDetailsOf(objFolderItem, 7)
GetFileAttributes.Owner = objFolder.GetDetailsOf(objFolderItem, 8)
GetFileAttributes.Author = objFolder.GetDetailsOf(objFolderItem, 9)
GetFileAttributes.Title = objFolder.GetDetailsOf(objFolderItem, 10)
GetFileAttributes.Subject = objFolder.GetDetailsOf(objFolderItem, 11)
GetFileAttributes.Category = objFolder.GetDetailsOf(objFolderItem, 12)
GetFileAttributes.Comments = objFolder.GetDetailsOf(objFolderItem, 14)
GetFileAttributes.Keywords = objFolder.GetDetailsOf(objFolderItem, 40)

End If

Set objFolderItem = Nothing

End If

Set objFolder = Nothing
Set objShell = Nothing

End Function