Get original creation time for all excel files in a folder

Question

I need to loop through a folder containing many excel files and extract the file name and creation time to a text file. By creation time, I mean the time the file was originally created rather than the time it was created on my system.

The following code works, but gives me the wrong time. I think FileDateTime is the wrong command, but after an hour of desperate googling I haven't been able to find the correct one.

Thanks in advance for the help!

Sub CheckFileTimes()
    Dim StrFile As String
    Dim thisBook As String
    Dim creationDate As Date
    Dim outputText As String
    Const ForReading = 1, ForWriting = 2
    Dim fso, f

'set up output file
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set f = fso.OpenTextFile("C:\TEST.txt", ForWriting, True)

'open folder and loop through
    StrFile = Dir("c:\HW\*.xls*")
    Do While Len(StrFile) > 0
'get creation date
       creationDate = FileDateTime("C:\HW\" & StrFile)
'get filename
       thisBook = StrFile
       outputText = thisBook & "," & creationDate
'write to output file
       f.writeLine outputText
'move to next file in folder
       StrFile = Dir
    Loop
    f.Close
End Sub

Answer 1

You can use DateCreated with the FileSystemObject.

A small tweak to your current code does this

I have tided up the the variables as well

Sub CheckFileTimes()
Dim StrFile As String
Dim StrCDate As Date
Dim fso As Object
Dim f As Object

'set up output file
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set f = fso.OpentextFile("C:\TEST.txt", 2, True)

'open folder and loop through
    StrFile = Dir("c:\HW\*.xls*")
    Do While Len(StrFile) > 0
    Set objFile = fso.getfile("c:\HW\" & StrFile)
'get creation date
       StrCDate = objFile.datecreated
'write to output file
       f.writeLine StrFile & "," & StrCDate
'move to next file in folder
       StrFile = Dir
    Loop
    f.Close
End Sub