How do I create a function to display the 'General' File Creation Date?

[zohair12976]

Apparently there are two types of Creation Dates for a File, and both of them can be viewed in the file properties.

Statistical Creation Date: shows what date and time the 'original' file was created - stays the same even if the file in question is a copy of the original.
General Creation Date: shows when the file was first created on 'this' computer - this is different from the statistical creation date if the current file is a copy of the original.

I was able to dig up a VBA code to create a function that displays the Statistical Creation Date

Function StatisticalFileCreationDate() As Date
StatisticalFileCreationDate = ActiveWorkbook.BuiltinDocumentProperties("Creation Date")
End Function

How do I create a function that can display the General File Creation Date?

The following code I found displays the General Date in a message box (If I remove 'Private' from 'Private Sub ShowFileInfo()' near the bottom]. I don't want a popup message box. I need a function that displays the date in a cell. Is there any way I could modify this code to do that?

(code shown with indents here - http://spreadsheetpage.com/index.php/tip/determining_when_a_file_was_created/)

'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

 

[hiker95]

zohair12976,

See reply #2 at the below link will help you:

http://www.mrexcel.com/forum/excel-...-created-using-visual-basic-applications.html

 

[Jerry Sullivan]

Hi all.

zohair12976, in the thread Hiker95 referenced, the code in Post #2 by dk should provide the General Date you want.

Building on that, it sounds like you were asking how to convert a macro that displays the result in a MsgBox into a User Defined Function (UDF) that you could place in a worksheet cell to display the result.

Here's one way that will give you a UDF similar to your StatisticalFileCreationDate()

Public Function GeneralFileCreationDate() As Date
 Dim oFS As Object
 Dim strFilename As String

 'Put your filename here
 strFilename = ActiveWorkbook.FullName

 'This creates an instance of the MS Scripting Runtime FileSystemObject class
 Set oFS = CreateObject("Scripting.FileSystemObject")
 GeneralFileCreationDate = oFS.GetFile(strFilename).DateCreated
 
 Set oFS = Nothing
End Function

 

[zohair12976]

Gentlemen - you are legends!

Thank you very much!

 

[hiker95]

zohair12976,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.