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.

 

[Micron]

Not sure I understand the the issue. You want to get the last author property value from a different workbook than where the code is located? Or it is that the last author doesn't provide the name you need from a Save As workbook? For the former, the workbook has to be open (but not visible) as far as I know:
Workbooks("workbook name here").BuiltinDocumentProperties("Last Author")

 

[Armstrong_N14]

Not sure I understand the the issue. You want to get the last author property value from a different workbook than where the code is located? Or it is that the last author doesn't provide the name you need from a Save As workbook? For the former, the workbook has to be open (but not visible) as far as I know:
Workbooks("workbook name here").BuiltinDocumentProperties("Last Author")

Appreciate you taking the time to check out my problem. Hope the below help clarify the issue. I wanted to create an individual function for each file so that the file and the path can be dynamic. I intend to put the cell value in each function as the file path like Workbooks(Range("A2").value).BuiltinDocumentProperties("Last Author") but its not working.

	(this is the Checker.xlsm file)
	A	B	C	D
1​	File Path	Files	Last Saved	Last Saved by
2​	C:\Documents\PTC\August 2023	Attendance - August MTD.xlsm	Saturday, August 19, 2023​
3​	C:\Documents\PTC\August 2023	Performance Dashboard - August.xlsm	Saturday, August 19, 2023​

 

[Skyybot]

Sub getAuthor()
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(1) 'You may need to change wbWorksheets(1)
Set fPath = sht.Range("A2").Value: Set fName = sht.Range("B2").Value
Set twb = Workbooks.Open(fPath & "\" & fName)
fAuth = twb.BuiltinDocumentProperties("Last Author")
sht.Range("D2") = fAuth
twb.Close
End Sub

 

[Armstrong_N14]

Sub getAuthor()
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(1) 'You may need to change wbWorksheets(1)
Set fPath = sht.Range("A2").Value: Set fName = sht.Range("B2").Value
Set twb = Workbooks.Open(fPath & "\" & fName)
fAuth = twb.BuiltinDocumentProperties("Last Author")
sht.Range("D2") = fAuth
twb.Close
End Sub

Thanks for sharing this code. I will work on this and see if I can make it to work. ppreciate the help. I hope I discovered this excel family sooner!

 

[Skyybot]

Thanks for sharing this code. I will work on this and see if I can make it to work. ppreciate the help. I hope I discovered this excel family sooner!

Sorry, that was a typo on my part. Remove the Set keyword before fPath and fName.

 

[Armstrong_N14]

Sorry, that was a typo on my part. Remove the Set keyword before fPath and fName.

I feel dumber posting every error I come across, but here it is anyway. I figured with the code that the file needs to be opened every time it fetches the data needed (last saved by) I would like to know if it is possible to do away with that.

Thank you again for responding to my SOS.

 

[Skyybot]

I feel dumber posting every error I come across, but here it is anyway. I figured with the code that the file needs to be opened every time it fetches the data needed (last saved by) I would like to know if it is possible to do away with that.

Thank you again for responding to my SOS.

What line did the debugger stop at?

 

[Armstrong_N14]

What line did the debugger stop at?

here:

twb = Workbooks.Open(fPath & "\" & fName)

 

[Skyybot]

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