Access file properties of excel document by using ActiveWorksheet and not filepath VBA

Question

I need to access the creation time of a excel sheet with VBA. I can't use the "normal" way by using: ActiveWorkbook.BuiltinDocumentProperties("Creation Date") Since this returns "Content Created" and not "Date created". Content created returns the time of which the template was created and I need the time the current file was created.

This method works:

Sub ShowFileInfo(filespec)
Dim fs, f, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filespec)
s = "Created: " & f.DateCreated
MsgBox s
End Sub

But I need it to use the filespec of the ActiveWorkbook without having to specify the exact document since I will process a large range of different files that all have the same source template. My attempt is something as shown below but I feel like I ahve tried every option now but I can't seem to get the filespecs of the ActiveWorkbook right

Dim file, fs
Set fs = CreateObject("Scripting.FileSystemObject")
file = fs.GetFile(ActiveWorkbook.FullName)
MsgBox file.DateCreated

Answer 1

Because You do not set file object. You need Set keyword to set file as object and then you can return object property. Another thing is, declaring a variable type is always good practice. So, try below full code:

Sub fCreated()
Dim strFile As Object, fs As Object
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set strFile = fs.GetFile(ActiveWorkbook.FullName)
    MsgBox strFile.DateCreated

'Clear memory
Set fs = Nothing
Set file = Nothing
End Sub

Answer 2

I m not a 100% sure of what you are trying to aim for, but i do see a difference in both ways of getting the Date and time.

Have you tried making it a function? And just returning a string or value this way?

For example:

Sub Spec() 'Your main code
Dim Time_Date As String

Time_Date = ShowFileInfo(ActiveWorkbook.FullName)

MsgBox Time_Date

End Sub

Function ShowFileInfo(filespec) As String 'Function to get your Time_Date
Dim fs, f, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filespec)

ShowFileInfo = f.DateCreated

End Function

This way you could put the function in a loop, and aslong as you can get your full worksheet names in a loop i would imagine you can get a whole array of document creation dates.