Last Save Date

[Kurch]

I want to be able to have the "Last Save" date shown in my header or footer. I'd also like to know how to have the last save date show in a cell

How can this be done

Thanks.

 

[Jim Gordon MVP]

Hi Kurch,

The first hit in this Google search seems to have the answer
<http://groups.google.com/groups?q=vba+last+saved+date&ie=ISO-8859-1&hl=en&b
tnG=Google+Search>

-Jim

 

[Kurch]

All of the replies in the Google search seem to be for Windows. I am trying to figure this out this for Excel for Mac.

 

[Bernard Rey]

Kurch wrote :

All of the replies in the Google search seem to be for Windows. I am
trying to figure this out this for Excel for Mac.

There's no difference between the two versions in this case. So the
instructions working in Excel for Windows should work on the Mac (and they
usually do).

Here is a "quick" way to do it (works on the Mac) But you'll have to
adapt it to your personal requirements.

Copy these lines in the "ThisWorkbook" codesheet:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Sheets("Sheet1").Range("A1") = Now
End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter = Range("A1").Value
End Sub

As you can imagine, the date will be stored in cell "A1" of "Sheet1" and
will be referred to when printing any sheet in the workbook

 

[JE McGimpsey]

The first hit in this Google search seems to have the answer
<http://groups.google.com/groups?q=vba+last+saved+date&ie=ISO-8859-1&hl=en&b
tnG=Google+Search>

For me, the first hit was a Visio solution...

 

[JE McGimpsey]

I want to be able to have the "Last Save" date shown in my header or footer.
I'd also like to know how to have the last save date show in a cell.

How can this be done?

You could use something like this in the ThisWorkbook code module
(CTRL-click in the workbook's title bar and choose View Code):

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Sheets("Sheet1").Range("A1") = Now
End Sub


but that will cause an incorrect value to be entered if the user cancels
the save.

A better way would be (again in the ThisWorkbook code module):

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wsSheet As Worksheet
For Each wsSheet In ActiveWindow.SelectedSheets
wsSheet.PageSetup.LeftFooter = _
FileDateTime(Me.FullName)
Next wsSheet
End Sub

Note that this could be misleading, too, since you could save the
workbook at 4:00, print it, showing a Last Save time as 4:00, then make
extensive changes and print the file with the Last Save time still
showing 4:00.

You can use FileDateTime in a User Defined Function to return the time
to a cell, too. In a regular code module (enter the VBE with OPT-F11 and
choose Insert/Module):

Public Function LastSaved() As Variant
Application.Volatile
With ThisWorkbook
If .Name = .FullName Then
LastSaved = CVErr(xlErrNA)
Else
LastSaved = CStr(FileDateTime(.FullName))
End If
End With
End Function

Usage:

=LastSaved()

Note that this will return #N/A when entered into a workbook that hasn't
been saved yet. On saving, it won't update until a calculation is
performed in the sheet, either caused by an entry or by pressing F9 or
CMD-= (or by closing and reopening the file if Calculation is set to
automatic). To be sure a printout has the right time, enter this in the
ThisWorkbook code module (instead of the above - you can only have one
Workbook_BeforePrint event macro):

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wsSheet As Worksheet
For Each wsSheet In ActiveWindow.SelectedSheets
wsSheet.Calculate
wsSheet.PageSetup.LeftFooter = _
LastSaved
Next wsSheet
End Sub

 

[Jim Gordon MVP]

Me, too tonight. Things change. The second hit was fine this time. Your
results will almost certainly vary.

-Jim