last saved date

  • Thread starter Thread starter Linda
  • Start date Start date
L

Linda

How can I insert the "last saved date" field? I'd like to
either place it in the footer or the main part of the
document. I can do it in Word via the Insert/Field
commands but I can't seem to do it in Excel.
 
Hello Linda,

Apparently this option does not exist, as it is available in Microsoft
Word. But there is a simple idea: just share the workbook (Tools ->
Share workbook) and then you can access the history sheet, just to see
who, what, when changed. How this sounds ?
 
-----Original Message-----
Hello Linda,

Apparently this option does not exist, as it is available in Microsoft
Word. But there is a simple idea: just share the workbook (Tools ->
Share workbook) and then you can access the history sheet, just to see
who, what, when changed. How this sounds ?


~~ View and post usenet messages directly from http://www.ExcelForum.com/
Click to expand...
Thanks for the tip and I tried it but it's not what I was
after. I just wanted to incorporate the date somewhere on
the spreadsheet so that other users (including me) can
easily see, while they are viewing the printed document,
when it was last saved.
 
Linda,
Here's one way that might serve the purpose:

A1: "Current Date:"
A2: "Last Saved:"
B1: =NOW()

Press Alt+F11 to open VBA, and Ctrl+R to open the Project Explorer pane (the
one whose title bar reads 'Project - VBA Project').
In the Project Explorer, double-click "This Workbook". You should see two
side by side dropdowns ('General' and 'Declarations') sitting above a large
textbox. Paste this into the text box:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Application.Calculate
Range("B1").Copy
Range("B2").PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End Sub

Press Alt+F11 to switch back to Excel. Format column B as you want the date
to appear (or format as Time for testing) and click the Save icon. B1
should update to the current date/time, and B2 should be set B1's value just
before the file is saved. (You can hide row A if desired.)
I'm sure there are better ways, maybe someone else will post one.

HTH,
Chris
 
Linda,

You might also want to try this.

In your VB Editor Insert|Module and paste this code

Private Function LastModified()
' User Defined formula that returns last save time
Application.Volatile
LastModified = ThisWorkbook.BuiltinDocumentProperties("Last Save
time").Value
End Function

Now return to your worksheet and write this formula to A1 (or whenever you
want). Format cell as date include time for testing

=LastModified()

However, to make it more functional you have to put this code in
Workbook_beforeSave Event. Refer to dizzykid example on how to do it.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = True

If SaveAsUI = False Then
' User use Save command
Application.EnableEvents = False
ThisWorkbook.Save
Calculate
Application.EnableEvents = True
Else
' User use Save as command
Application.EnableEvents = False
fileSaveName = Application.GetSaveAsFilename
If fileSaveName <> False Then
ThisWorkbook.SaveAs fileSaveName
Calculate
End If
Application.EnableEvents = True
End If
End Sub

Now try to save your file in different interval and see how it works. And
to prove to yourself that it records the correct date and time go to
Files|Properties|General and look for the Modified date.

Best Regards,

Jon-jon
 
Back
Top