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.

 

[Jaafar Tribak]

That's great - I only just recently stumbled across it. I had already learnt how to read the file structure from ActiveVB (link), but had no idea how to identify and extract out the compressed data without using Shell.

As for the Date Modified data, I found the following in the core.xml in the docProps folder:

<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/"
xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<dc:creator>Dan_W</dc:creator><cp:lastModifiedBy>Dan_W</cp:lastModifiedBy><dcterms:created xsi:type="dcterms:W3CDTF">2023-10-08T12:07:13Z</dcterms:created>
<dcterms:modified xsi:type="dcterms:W3CDTF">2023-10-08T12:07:59Z</dcterms:modified></cp:coreProperties>

Is that what you were after?

This is from a 10mb workbook which, for completeness, I should report back - your API method managed to get the Last Saved By data in 0.5 whereas the shell/rename method took 7.5 - so as expected, the larger the file, the (excruciatingly) slower the result! I've just managed to take a look at your code - it's great. It would never have occurred to me to approach using IAccessible - @sancarn will doubtless be interested.

Yes but I can't find the Date_ X info in the core.xml file. The Date Create\Modified etc properties are in the xl\sharedStrings.xml file.

The hack I used applies brute force to get the data I want . It is a hack but it was my last resort and it seems to get the job done (so far) for getting all the extended properties of workbooks. In fact, it should work fine on any file with extended properties.

 

[Armstrong_N14]

I am totally lost with the way codes are written, I know very little on VBA coding. Just wanna say thank you for all the time and effort you put into this, and will not ask any more of it. Take care and have a great day.

 

[Dan_W]

I am totally lost with the way codes are written, I know very little on VBA coding. Just wanna say thank you for all the time and effort you put into this, and will not ask any more of it. Take care and have a great day.


View attachment 99949

Hi. So the solutions still aren't working? Odd. Do you have a sample workbook (from which you can't extract the Last Saved By details) that you can share for testing?

 

[Jaafar Tribak]

@Armstrong_N14

Did you download the LastSavedBy.xlsm workbook I linked in post#43 ? did that not work either ?
Or did you just copy the code to a new workbook ?

 

[Armstrong_N14]

@Armstrong_N14

Did you download the LastSavedBy.xlsm workbook I linked in post#43 ? did that not work either ?
Or did you just copy the code to a new workbook ?

I downloaded the workbook, just tweaked the file path and file name

 

[Jaafar Tribak]

I downloaded the workbook, just tweaked the file path and file name

@Armstrong_N14

Can you please, replace the first helper function *ExtendedPropertyFromFile* with the one below and tell us what is the output string you get in the VBE immediate window. Thanks.

I just added a Debug.Print line in a strategic position.

Private Function ExtendedPropertyFromFile(ByVal FilePathName As String, ByVal PropertyName As String) As String

    Const CHILDID_SELF = 0&, NAVDIR_FIRSTCHILD = 7&
    Const WM_SYSCOMMAND = &H112, SC_CLOSE = &HF060&
    Dim oAccContextMenu As IAccessible, oAccChild As IAccessible
    Dim hwnd As LongPtr, lChildCount As Long
    Dim sFileName As String, bPropertyFound As Boolean
    Dim sOutputString As String, sngTimer As Single
     
    ExtendedPropertyFromFile = "Extended Property: [" & PropertyName & "] doesn't exist."
    Call DestroyWindow(GetProp(Application.hwnd, "Hidden"))
    If InvokeShellContextMenu(FilePathName) Then
        sFileName = Split(FilePathName, Application.PathSeparator) _
                    (UBound(Split(FilePathName, Application.PathSeparator)))
        sngTimer = Timer
        Application.EnableCancelKey = xlDisabled
        On Error Resume Next
        Do
            'DoEvents
            If Timer - sngTimer >= 5! Then GoTo Xit
            'Language dependent.
            hwnd = FindWindow("#32770", sFileName & " Properties")
            Call SetParent(hwnd, GetProp(Application.hwnd, "Hidden"))
        Loop Until hwnd
        Set oAccContextMenu = HwndToAcc(hwnd)
        If Not oAccContextMenu Is Nothing Then
            Set oAccChild = oAccContextMenu.accNavigate(NAVDIR_FIRSTCHILD, CHILDID_SELF)
            For lChildCount = 0& To 2&
                Set oAccChild = oAccChild.accNavigate(NAVDIR_FIRSTCHILD, CHILDID_SELF)
            Next lChildCount
            If Not oAccChild Is Nothing Then
                For lChildCount = 1& To oAccChild.accChildCount
                    Debug.Print lChildCount, oAccChild.accName(lChildCount), Len(oAccChild.accName(lChildCount))
                    If LCase(oAccChild.accName(lChildCount)) = LCase(PropertyName) Then
                        sOutputString = CleanUpString _
                                ((Trim(Replace((oAccChild.accDescription(lChildCount)), "Value:", ""))))
                        If Len(sOutputString) Then
                            ExtendedPropertyFromFile = sOutputString
                        End If
                        'Exit For
                    End If
                Next
            End If
        End If
    End If
Xit:
    If GetLastError Then
        ExtendedPropertyFromFile = "Error: " & FormatErrorCode(GetLastError)
    End If
    Call SendMessage(hwnd, WM_SYSCOMMAND, SC_CLOSE, ByVal 0&)
    Call DestroyWindow(GetProp(Application.hwnd, "Hidden"))

End Function

 

[Armstrong_N14]

 

[Jaafar Tribak]

Can you post here the output string list you get in the Immediate Window after running the test.

To display the immediate window in the VB Editor, press CTRL Key + G

Thanks.

 

[Armstrong_N14]

 

[Jaafar Tribak]

View attachment 99963

Nothing!

Is that *after* replacing the old ExtendedPropertyFromFile routine with the new one I posted in post#56 ?