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

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

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

Solution

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