How to get the "Last Saved By" property for workbook file

Question

In Windows Explorer, if I right-click a file and choose Properties to get the file's Properties window, and then select the Details tab, there is a property listed there called "Last Saved By". It seems to be the Windows account name that was logged in when the file was last modified.

I've looked in FileSystemObject but I don't see that the File object has such a property.

How do I get that property in VBA? Is there a Windows API for it?

UPDATE:

There are 3 attempts in this thread to do it with Shell's GetDetailsOf. I appreciate the effort but it seems pretty clear to me after trying them all (especially the code sample by OssieMac) that the text stored in the file system's "Last Saved By" field is not to be found in GetDetailsOf.

Scratching my head. How does Windows Explorer do it??

Answer 1

Try this - the code uses the BuiltinDocumentProperties class:

Option Explicit

Sub Test()
    MsgBox LastAuthor
End Sub

Function LastAuthor() As String
    LastAuthor = ThisWorkbook.BuiltinDocumentProperties("Last Author")
End Function

EDIT

Using extended file properties - Microsoft reports that the magic number for Author is 9. However, this number changes over time with Windows releases and since Vista has been 20 - see this link. Further to some testing, you might also try 10 for Windows 10.

Option Explicit

Sub Test()

    Dim varPath As Variant
    Dim varFileName As Variant

    varPath = "C:\Users\foo\bar\" '<~~ ensure final \
    varFileName = "lol.xlsx"


    'depending on OS version, try 9, 10 and 20
    Debug.Print GetAuthorFromShell(varPath, varFileName, 9)
    Debug.Print GetAuthorFromShell(varPath, varFileName, 10)
    Debug.Print GetAuthorFromShell(varPath, varFileName, 20)

End Sub

Function GetAuthorFromShell(varPath As Variant, varFileName As Variant, intProperty As Integer) As String

    Dim objShell As Object
    Dim objFolder As Object
    Dim strAuthor As String

    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objShell.Namespace(varPath) 

    With objFolder
        strAuthor = .getdetailsof(.Items.Item(varFileName), intProperty)
    End With

    GetAuthorFromShell = strAuthor

End Function

Answer 2

This data is called Extended File Properties and stored in the NTFS metadata. You can read them using this script:

Dim arrHeaders(266)
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace("C:\Test.xtx")
For i = 0 to 265
 arrHeaders(i) = objFolder.GetDetailsOf(objFolder.Items, i)
Next
For Each strFileName in objFolder.Items
 For i = 0 to 265
 Wscript.Echo i & vbtab & arrHeaders(i) _
 & ": " & objFolder.GetDetailsOf(strFileName, i)
 Next
Next

Note, that different OS versions has different amount of extended file properties. Windows 2000 allowed 35, Windows Vista extended property count to the 266.

References:

1) Borrowing from Windows Explorer in PowerShell part 2: extended properties

2) Retrieving Extended File Properties

Answer 3

I, too was quite disappointed to find this to be true. Pity no response came.