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.

 

[Skyybot]

This takes no time at all. I think we can modify your code to run fairly quickly, even with hundreds of files. What do you think?

Sub test()
Dim wb As Workbook
Application.ScreenUpdating = False
With Application
    Set wb = .Workbooks.Open("C:\Users\Public\Documents\Book2.xlsx")
    .Visible = False
End With
Debug.Print wb.BuiltinDocumentProperties("Last Author")
wb.Close
Application.ScreenUpdating = True
End Sub

 

[Skyybot]

This takes no time at all. I think we can modify your code to run fairly quickly, even with hundreds of files. What do you think?

Sub test()
Dim wb As Workbook
Application.ScreenUpdating = False
With Application
    Set wb = .Workbooks.Open("C:\Users\Public\Documents\Book2.xlsx")
    .Visible = False
End With
Debug.Print wb.BuiltinDocumentProperties("Last Author")
wb.Close
Application.ScreenUpdating = True
End Sub

Turn it into a Function?

 

[Armstrong_N14]

Turn it into a Function?

Thanks a lot. Appreciate all your help. God Speed.

 

[Armstrong_N14]

Raising the SOS Flag again for this. I wanted to get this accomplished without having the need to open the file

 

[Jaafar Tribak]

Raising the SOS Flag again for this. I wanted to get this accomplished without having the need to open the file

If the workbook you want to know who was the last one who saved it is too heavy then you may try opening it in a second temporary excel instance created on fly. This, at least, will not block the user interface and will leave your current excel application free to interact with while the heavy workbook is being opened.

 

[Georgiboy]

You can try the below shell code, if you pass it the file path and file name then it should return the last saved time/date and last saved by:

Function GetAuthorFromShell(strPath As String, strFileName As String) As Variant
    Dim objShell As Object, objFolder As Object
    Dim varPath As Variant, varFileName As Variant
    Dim ls As String, lsby As String
    
    varPath = strPath
    varFileName = strFileName

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

    With objFolder
        ls = .getdetailsof(.Items.Item(varFileName), 3)
        lsby = .getdetailsof(.Items.Item(varFileName), 10)
    End With

    GetAuthorFromShell = Array(ls, lsby)
End Function

Use it in the spreadsheet like:

=GetAuthorFromShell(A2,B2)

A2 being the filepath ending with \
B2 being the file name ending with extension. '.xlsx' for example

 

[Jaafar Tribak]

Hi Georgiboy

Thanks for the code but there are two issues to bear in mind:

Issue 1- The index of the shell extended properties vary from one OS to another. So for example, 20 may not always yield the correct value for the authors property.

I use this alternative which doesnt hardd-code the property index:

Function GetProperty(ByVal PathFileName As String, ByVal Prop As String) As String

    Dim sPath As Variant, sFileName As Variant, i As Long
    Dim objFolder As Object
    
    If Len(Dir(PathFileName)) Then
        sFileName = Split(PathFileName, "\")(UBound(Split(PathFileName, "\")))
        sPath = Replace(PathFileName, sFileName, "")
        Set objFolder = CreateObject("Shell.Application").Namespace(sPath)
        Do
            DoEvents
            If objFolder.getdetailsof(sFileName, i) = Prop Then
                GetProperty = objFolder.getdetailsof(objFolder.Items.Item(sFileName), i)
                Exit Do
            End If
            i = i + 1&
        Loop
    End If
    
End Function

Usage example:

Sub Test1()
    MsgBox GetProperty(PathFileName:="C:\test\foo.xlsm", Prop:="Authors")
End Sub

Issue 2- As shown above, the shell namespace doesn't actually retrieve the *Last Saved By* info. It returns the *Authors*.
To be honest, I couldn't find a method for returning the actual *Last Saved By* other than by opening the workbook and querying the BuiltinDocumentProperties.

The problem with that is that if the workbook is too heavy, its opening ties up the application. Like I meantioned in my previous post, temporarly opening the workbook in a hidden second excel instance may be a better option.

Below is the code I use. It is a gross hack and it is slow if the size of the workbook being opened is big but, at least, it doesn't block excel during the opening of the workbook.

In a Standard Module:

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function CreateThread Lib "kernel32" (lpThreadAttributes As Any, ByVal dwStackSize As LongPtr, ByVal lpStartAddress As LongPtr, lpParameter As Any, ByVal dwCreationFlags As Long, lpThreadId As Long) As LongPtr
    Private Declare PtrSafe Function CloseHandle Lib "kernel32" (ByVal hObject As LongPtr) As Long
    Private Declare PtrSafe Function TerminateThread Lib "kernel32" (ByVal hThread As LongPtr, ByVal dwExitCode As Long) As Long
    Private Declare PtrSafe Function GetExitCodeThread Lib "kernel32" (ByVal hThread As LongPtr, lpExitCode As Long) As Long
    Private hThread As LongPtr
#Else
    Private Declare Function CreateThread Lib "kernel32" (lpThreadAttributes As Any, ByVal dwStackSize As Long, ByVal lpStartAddress As Long, lpParameter As Any, ByVal dwCreationFlags As Long, lpThreadId As Long) As Long
    Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
    Private Declare Function TerminateThread Lib "kernel32" (ByVal hThread As Long, ByVal dwExitCode As Long) As Long
    Private Declare Function GetExitCodeThread Lib "kernel32" (ByVal hThread As Long, lpExitCode As Long) As Long
    Private hThread As Long
#End If

Private bDone As Boolean
Private oXl As Variant, oWb As Variant
Private sPathFileName As String
Private lXitCode As Long

Public Function GetLastSavedBy(ByVal PathFileName As String) As String
    If Len(Dir(PathFileName)) Then
        sPathFileName = PathFileName
        bDone = False
        Call SaveSetting("AppName", "Section", "Key", sPathFileName)
        hThread = CreateThread(ByVal 0&, ByVal 0&, AddressOf AsyncThread, ByVal 0&, ByVal 0&, ByVal 0&)
        Call GetExitCodeThread(hThread, lXitCode)
        Call CloseHandle(hThread)
        Application.EnableCancelKey = xlDisabled
        Do
            DoEvents
        Loop Until bDone
        Application.EnableCancelKey = xlInterrupt
        GetLastSavedBy = GetSetting("AppName", "Section", "Key")
        Call DeleteSetting("AppName", "Section", "Key")
        hThread = 0&
    Else
        GetLastSavedBy = "Invalid PathFileName."
    End If
End Function

Private Sub AsyncThread()
    Call RetrieveBuiltInProp(Property:="Last Author")
End Sub

Private Sub RetrieveBuiltInProp(ByVal Property As String)
    On Error Resume Next
    Set oXl = CreateObject("Excel.Application")
    With oXl
        .EnableEvents = False
        Set oWb = .Workbooks.Open(GetSetting("AppName", "Section", "Key"), False, True)
        Call SaveSetting("AppName", "Section", "Key", oWb.BuiltinDocumentProperties(Property))
    End With
    oWb.Saved = True
    oWb.Close
    Set oWb = Nothing
    oXl.Quit
    Set oXl = Nothing
    bDone = True
    Call TerminateThread(hThread, lXitCode)
End Sub

Usage Example:

Sub Test2()
    MsgBox GetLastSavedBy(PathFileName:="C:\test\foo.xlsm")
End SubEnd Sub

 

[Armstrong_N14]

Thank you guys for taking a look at this problem. I will work on the codes. I appreciate your time.

 

[Georgiboy]

Hi @Jaafar Tribak,

Thanks for the comments, I agree with issue 1 the numbers can change between systems but for the most part remain the same. However, the way I have used the code in the past is on my machine only, so once i have the numbers correct, all is well. As the TS has not stipulated that the code is to be distributed between different OS, the code may still be suitable. I tested your code and swapped 'Author' for 'Owner' but it did not return anything.

For issue 2 however, I have to disagree. The number 10 in the line below is returning the 'Owner' and not the 'Author', the owner attribute changes when the file is saved (I tested this) and therefore returns tha last person to save the file.

lsby = .getdetailsof(.Items.Item(varFileName), 10)

The list of numbers where the attributes are stored below where 10 denotes the 'Owner' attribute, and as you state 20 is 'Author':
[0] = Name
[1] = Size
[2] = Item type
[3] = Date modified
[4] = Date created
[5] = Date accessed
[6] = Attributes
[7] = Offline status
[8] = Availability
[9] = Perceived type
[10] = Owner
[11] = Kind
[12] = Date taken
[13] = Contributing artists
[14] = Album
[15] = Year
[16] = Genre
[17] = Conductors
[18] = Tags
[19] = Rating
[20] = Authors
[21] = Title
[22] = Subject
[23] = Categories
[24] = Comments
[25] = Copyright
[26] = #
[27] = Length
[28] = Bit rate
[29] = Protected
[30] = Camera model
[31] = Dimensions
[32] = Camera maker
[33] = Company
[34] = File description
[35] = Program name
[36] = Duration
[37] = Is online
[38] = Is recurring
[39] = Location
[40] = Optional attendee addresses
[41] = Optional attendees
[42] = Organizer address
[43] = Organizer name
[44] = Reminder time
[45] = Required attendee addresses
[46] = Required attendees
[47] = Resources
[48] = Meeting status
[49] = Free/busy status
[50] = Total size
[51] = Account name

There was also someone else looking fro roughly the same code a while back, the code I supplied using the same numeric logic worked fine for them. They were actively testing the code and were happy with the result:
VBA question

I would personally do everthing I can to avoid opening the files as if you have quite a number of large files it just would not be practical, new instance of Excel or not. The method I have suggested is instant compared to opening large large files.

 

[Jaafar Tribak]

For issue 2 however, I have to disagree. The number 10 in the line below is returning the 'Owner' and not the 'Author', the owner attribute changes when the file is saved (I tested this) and therefore returns tha last person to save the file.

@Georgiboy

Thanks for the feedback.

On my end, the owner [10] attribute doesn't return the actual *Last Saved by* info

For example,
The actual *Last Saved By* Property in the Details tab (from the file Shell context menu) for a file located in a shared folder shows : "jaafar tribak"

But when I pass the same file to the GetAuthorFromShell function, I get: "DESKTOP-DA4MDCD\hp"

Same happens, when applying it to non-shared files.

I would personally do everthing I can to avoid opening the files as if you have quite a number of large files it just would not be practical, new instance of Excel or not. The method I have suggested is instant compared to opening large large files.

I couldn't agree more.
I posted that alternative code just for reference.

Regards.