Insert last saved date into header

[Amy]

I am using Excel 2003. I created a Macro with the following custom function:

Function Last_Saved_Date()
Last_Saved_Date = Application.Caller.Parent.Parent. _
BuiltinDocumentProperties("Last Save Time").Value
End Function

Then in a cell in the worksheet, I have it as "=Last_Saved_Date()" and I
make sure to format the cell in a date format.

Doing this, I am able to see the last saved date inside the worksheet.

My questions are the following:

1. How do I have the header print at the top of each page, the last saved
date of the file? May I use the contents of my Last Saved Date cell?
2. Is there a more efficient way to do this in the header without using this
macro?

Thank you for your help,
Amy

 

[Paul C]

This will populate the header of each sheet with the last saved date each
time the workbook is opened. This does need to go in a module and not on an
individual sheet.

Sub Auto_Open
Dim Last_Saved as Date
wscount=Activeworkbook.Worksheets.Count
Last_Saved= Application.Caller.Parent.Parent. _
BuiltinDocumentProperties("Last Save Time").Value
For A=1 to wscount
With Sheets(A).PageSetup
.LeftHeader = Last_Saved
End With
Next A

End Sub

 

[JLatham]

Amy, to add to what Paul C gave you (a good way to do it, by the way). While
you could use the contents of the cell where you put the user defined
function, it would just require more code to get it from the cell to the
header. So it turns out to be not really needed, and the way Paul C gave you
is more efficient.

Also, unless my thinking is muddled this morning, remember that the entry
you get is going to be exactly that - the last saved date/time. Which means
it's not going to be the date/time that the current user saves the file at
the end of their session when it is next opened. If you (also) call the
routine during the workbook's _Open() event, it would be updated to the true
last time saved.

 

[Amy]

Let me see if I understand this correctly. What you mention below of what I
won't get is actually what I do want. I'm sorry if I did not ask my question
more clearly. let me try again using an example.

I have a file named Peppers.xls. I open the file, make some changes, save
it, and close it. The file properties show 6/16/09 xxtime after I close the
file.

Now, I edit the Peppers.xls file the next day, save my changes, and exit
Excel again. Now, the file properties show 6/17/09 xxtime after closing the
file.

How do I get the "6/17/09" information in to the header? If I use Paul's
version (yup, much more eloquent than my bullied attempt), will I get this as
the last time the file was saved? Each time I edit the file, I would like
that new date to show up in the header.

Hope that makes sense and thanks for your help!
- Amy

 

[Amy]

Let me see if I understand this correctly. What you mention below of what I
won't get is actually what I do want. I'm sorry if I did not ask my question
more clearly. let me try again using an example.

I have a file named Peppers.xls. I open the file, make some changes, save
it, and close it. The file properties show 6/16/09 xxtime after I close the
file.

Now, I edit the Peppers.xls file the next day, save my changes, and exit
Excel again. Now, the file properties show 6/17/09 xxtime after closing the
file.

How do I get the "6/17/09" information in to the header? If I use Paul's
version (yup, much more eloquent than my bullied attempt), will I get this as
the last time the file was saved? Each time I edit the file, I would like
that new date to show up in the header.

Hope that makes sense and thanks for your help!
- Amy

 

[Gord Dibben]

Amy

You don't really need a UDF to return the date/time to a header/footer.

Sub Last_Saved_Footer()
'Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In ThisWorkbook.Worksheets
wkSht.PageSetup.RightFooter = "&8Last Saved : " & _
Format(ThisWorkbook.BuiltinDocumentProperties("Last Save Time"), _
"yyyy-mmm-dd hh:mm:ss")
Next wkSht
End Sub


Gord Dibben MS Excel MVP

 

[JLatham]

Actually, since Paul's method uses the _Open() event, I believe it'll show
you what you want. Best way to make sure is to put the code in the workbook
and save, close and open a few times to be sure is shows what you want.

 

[Amy]

Thank you. I just tried to execute that but I get a "Run-time error '424':
Object required" message.

Do you know what I might have done wrong? Thanks.

 

[JLatham]

Probably not doing anything wrong. A couple of small hiccups in the original
code. I hadn't tested it as the general process looked valid.

Try this code in its place. Note that it will return an error until the
workbook has been saved to HDD at least once (until then there is no such
thing as "last saved time").

Sub Auto_Open()
Dim Last_Saved As String
Last_Saved = "Last save time"
For A = 1 To ThisWorkbook.Worksheets.Count
With Sheets(A).PageSetup
.LeftHeader = _
ThisWorkbook.BuiltinDocumentProperties(Last_Saved)
End With
Next A

End Sub

 

[JLatham]

Or see Gord Dibben's posting above.

Thank you. I just tried to execute that but I get a "Run-time error '424':
Object required" message.

Do you know what I might have done wrong? Thanks.