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.

 

[Armstrong_N14]

You have to use the Set keyword before twb as it references an Object.

Will continue working on the code. Thanks a ton. 'appreciate the help!

 

[Skyybot]

Will continue working on the code. Thanks a ton. 'appreciate the help!

Happy to help.

 

[Armstrong_N14]

Thanks a lot, I finally got it to work, made some changes and found a helpful article on microsoft website (Redirecting)

Function DocProps(prop As String)

Dim twb As Workbook, wb As Workbook, sht As Worksheet, fPath As String, fName As String, fAuth As String
Set wb = ThisWorkbook: Set sht = wb.Worksheets("Sheet1") 'You may need to change wbWorksheets(1)
fPath = sht.Range("A2").Value: fName = sht.Range("B2").Value
Set twb = Workbooks.Add(fPath & "\" & fName)

Application.Volatile

On Error GoTo err_value
DocProps = twb.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

 

[Skyybot]

Thanks a lot, I finally got it to work, made some changes and found a helpful article on microsoft website (Redirecting)

Function DocProps(prop As String)

Dim twb As Workbook, wb As Workbook, sht As Worksheet, fPath As String, fName As String, fAuth As String
Set wb = ThisWorkbook: Set sht = wb.Worksheets("Sheet1") 'You may need to change wbWorksheets(1)
fPath = sht.Range("A2").Value: fName = sht.Range("B2").Value
Set twb = Workbooks.Add(fPath & "\" & fName)

Application.Volatile

On Error GoTo err_value
DocProps = twb.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

Nice. Done without having to open Workbook.

 

[Armstrong_N14]

Nice. Done without having to open Workbook.

Just got disappointed, I figured it late that its showing the the last author (as the code indicates), yet what I really wanted is to show who saved it last (last touch).
sad

 

[Armstrong_N14]

The code above is working(incorrectly), I think it still fetches the data of the open workbook where it was written as it shows my name to every file that I have on the table (which should not be the case as my other coworker has saved the file last). Any insights please?

 

[Skyybot]

The code above is working(incorrectly), I think it still fetches the data of the open workbook where it was written as it shows my name to every file that I have on the table (which should not be the case as my other coworker has saved the file last). Any insights please?

Have you stepped through your code with the Locals window open so you can check your Variables?

 

[Armstrong_N14]

Have you stepped through your code with the Locals window open so you can check your Variables?

just now, and it appears that the file needs to be open for it to be fetched properly. guess its a dead end (mean the file really needs to be opened)

 

[Skyybot]

Well, if it works... Any specific reason you're opposed to opening the file?

 

[Armstrong_N14]

Well, if it works... Any specific reason you're opposed to opening the file?

file size and time issues. all I need is visibility who saved it last.