BuiltinDocumentProperties - formatting

[scotthannaford1973]

Hi

I'm using the following code to create a basic log of when a workbook is saved - and by who. Works fine, does what I want... but the format of the DocumentProperties is coming as surname, first name, organisation - as per our AD set up; am I able to easily change this so it shows as surname and first name only? TIA!

Sub LastSaveAudit() Sheets("Closed Folder Audit").Select Range("AN4:AO870").Select Selection.Cut ActiveWindow.LargeScroll Down:=0 Range("AN5").Select ActiveSheet.Paste Sheets("Closed Folder Audit").Range("AN4").Value = Format(Now, "dd-mmm-yy h-mm-ss") Sheets("Closed Folder Audit").Range("AO4").Value = ActiveWorkbook.BuiltinDocumentProperties("Last Author") End Sub

 

[RoryA]

Use InStr to find the position of the opening bracket, combined with Left to return everything before that.

 

[RobP]

Hi,

assuming your authors ALL have comma separators between their names, perhaps this might work for you:

Sub LastSaveAudit()

Sheets("Closed Folder Audit").Select
Range("AN4:AO870").Select
Selection.Cut
ActiveWindow.LargeScroll Down:=0
Range("AN5").Select
ActiveSheet.Paste

Sheets("Closed Folder Audit").Range("AN4").Value = Format(Now, "dd-mmm-yy h-mm-ss")

authorname = ActiveWorkbook.BuiltinDocumentProperties("Last Author")
mycount = InStr(authorname, ",") 'finds comma seperator
myname = Left(authorname, InStr(mycount + 2, authorname, " ") - 1) 'starts to count 2 letters after the "," and looks for next " " space.

Sheets("Closed Folder Audit").Range("AO4").Value = myname


End Sub

 

[scotthannaford1973]

Hi,

assuming your authors ALL have comma separators between their names, perhaps this might work for you:

Sub LastSaveAudit()

Sheets("Closed Folder Audit").Select
Range("AN4:AO870").Select
Selection.Cut
ActiveWindow.LargeScroll Down:=0
Range("AN5").Select
ActiveSheet.Paste

Sheets("Closed Folder Audit").Range("AN4").Value = Format(Now, "dd-mmm-yy h-mm-ss")

authorname = ActiveWorkbook.BuiltinDocumentProperties("Last Author")
mycount = InStr(authorname, ",") 'finds comma seperator
myname = Left(authorname, InStr(mycount + 2, authorname, " ") - 1) 'starts to count 2 letters after the "," and looks for next " " space.

Sheets("Closed Folder Audit").Range("AO4").Value = myname


End Sub

thanks, that looks good

 

[RobP]

great, thanks for the feedback..

Rob