Dynamic File - Last Saved by

[Armstrong_N14]

Good Day, Everyone!
Hope this SOS finds you all in good health.
I can't seem to put the right syntax for the builtinproperties code to show the last author of a specific file due to the twist that the file that needs to show who saved it last is a different file from where the builtinproperties code is being written.

Example:
Checker.xlsm is the file that has a table that shows the breakdown of files with it's last saved time stamp and last author, in each file in the list, there is a corresponding cell that shows the path of the file where it it saved. I was able to show the last saved time stamp but not the last author(last saved by)


Sample code:

Funtion Lastauthor()

Lastauthor=Thisworkbook. Builtinproperties("Last Author")

End function

I'd like to put the file path on the Thisworkbook portion of the code but I can't seem to make it to work.

Please help me. Thanks a lot in advance.

 

[Georgiboy]

@Jaafar Tribak

Would it be possible to edit the code you proposed under Issue1 of post 27 to work with "Owner" or would that still return the wrong value at your end?

I get a different result if I run it on a file on my network as opposed to a file on my hard drive. I still get the last saved by but i get "MTL\Georgiboy" on network files and "Georgiboy" on local files.

 

[Jaafar Tribak]

@Jaafar Tribak

Would it be possible to edit the code you proposed under Issue1 of post 27 to work with "Owner" or would that still return the wrong value at your end?

I get a different result if I run it on a file on my network as opposed to a file on my hard drive. I still get the last saved by but i get "MTL\Georgiboy" on network files and "Georgiboy" on local files.

I get the same with owner

MsgBox GetProperty(ThisWorkbook.FullName, "Owner") MsgBox GetAuthorFromShell(ThisWorkbook.Path, ThisWorkbook.Name)(1)
Both above calls return "DESKTOP-DA4MDCD\hp" which is the incorrect *Last Saved By* Property value.

BTW, the workbook is on my hard drive and is not a shared workbook. same happens with all other workbooks shared or otherwise.

Regards.

 

[Georgiboy]

Strange, I get the owner attribute with both. Then when I save the file in question I then get my name instead.

Mine shows as my company "MTL" with a \ then my pc name "gclark" so I get "MTL\gclark" from owner in both codes.

It is interesting that you get the same format "DESKTOP-DA4MDCD\hp", it is as if the owner is "hp"

 

[Jaafar Tribak]

Strange, I get the owner attribute with both. Then when I save the file in question I then get my name instead.

Mine shows as my company "MTL" with a \ then my pc name "gclark" so I get "MTL\gclark" from owner in both codes.

It is interesting that you get the same format "DESKTOP-DA4MDCD\hp", it is as if the owner is "hp"

Yes. It is confusing. Unlike the Last Author builtin Document property which wroks consistently for getting the name of the user who last saved the file.

If I find the time, I will further look into this.

 

[Georgiboy]

That would be great @Jaafar Tribak, thanks.

 

[Dan_W]

So for example, 20 may not always yield the correct value for the authors property.

Could you not accomplish this using the Extended Property key for Author ("System.Author") or does it yield a different result depending on OS? I don't see that there is a property key for Last Saved By, which I'm guessing is an MS Docs exclusive property.

Function GetExtendedProperty(ByVal FileName As String, ByVal TargetProperty As String)
    If LenB(Dir(FileName)) Then
        Const ssfDESKTOP As Variant = 0
        Dim Result As Variant
        Result = CreateObject("Shell.Application").Namespace(ssfDESKTOP).ParseName(FileName).ExtendedProperty(TargetProperty)
        If IsArray(Result) Then GetExtendedProperty = Join(Result, ";")
    End If
End Function

To use the function above:

FileAuthor = GetExtendedProperty("D:\FileName.xlsm", "System.Author")

 

[Jaafar Tribak]

Could you not accomplish this using the Extended Property key for Author ("System.Author") or does it yield a different result depending on OS?

That did work for me for getting the Author(s) extended property. Seems to work w/o hard-coding the index of the property being queried.

I don't see that there is a property key for Last Saved By, which I'm guessing is an MS Docs exclusive property.

The *Last Saved By* property is also stored in the core.xml file

Don't know of a programmatic way of getting to the data in the xml files of a workbook w/o first having to compress the file which is more complcated than opening the file as read only in a separate excel instance and reading the sought property.

Thanks.

 

[Armstrong_N14]

I can't make it to work.
But still, thank you all for the tons of help.

 

[Jaafar Tribak]

I can't make it to work.
But still, thank you all for the tons of help.

Which code(s) have you tried ? and when you say "I can't make it work" , what exactly happens ?

 

[Armstrong_N14]

Which code(s) have you tried ? and when you say "I can't make it work" , what exactly happens ?

This works for Owner of the file - but what I want was "the Last Touch".
The rest, I get 0 as the value - either I substituted some of the values incorrectly or I dunno. time for me to give up.

but again, thank you all guys for looking into this. stay safe