Discussion Locked

XL97 - Add the "last edited" time in a c

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

A user wants that in a specific cell in a XL97 workbook, the date and time when the spreadsheet was saved for the last time appears automatically when the file is open.
I found some posts around and I now have the following VBA code. One problem persists, somtimes it works and somtimes not and I can't find why. Could you guys test this code and let me know if it works ... ? Don't forget to format cell A2 as dat & time. Personaly I had to choose custom > dd/mm/yyyy hh:mm:ss

Private Sub auto_open()
If ActiveWorkbook.Saved = True Then
Worksheets(1).Range("A2").Value = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")
End If
End Sub

5 replies

by chouf

I tried different things and I'm at using the following code :

Function LastSaved()
Application.Volatile (True)
With ActiveWorkbook
LastSaved = FileDateTime(.Path & "\" & .Name)
End With
End Function

But this doesn't give me the result expected, it works in the sense that I don't get any error message... ;-) but unfortunately, it displays the last time the workbook has been accessed, not saved. So if I open the file, do nothing and close it, the next time I'll re-open it, the value displayed will be the one from the last time the file has been accessed, and not modified. I have a W2K SP3 machine. I think it's just some little details that have to be changed. Would you know what else I could try ?

by dklippert

From Chip Pearso (www.cpearson.com):
You can set the time value of the "Last Save Time" property. You only get an error if the property has never been set. Excel does not
maintain the "Last Save Time" property itself -- you've got to do it on your own.

I have the following code in an Class module which gets Application level events to set the property whenever any workbook is saved.

Private Sub App_WorkbookBeforeSave(ByVal WB As Excel.Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
WB.BuiltinDocumentProperties("Last Save Time").Value = Now
End Sub

Also, the following code will return either a Built-in or Custom document property, if one exists, or a blank string.

Public Function GetProperty(P As String) As Variant
Dim S As Variant
On Error Resume Next
S = ActiveWorkbook.CustomDocumentProperties(P)
If S <> "" Then
GetProperty = S
Exit Function
End If
On Error GoTo EndMacro
GetProperty = ActiveWorkbook.BuiltinDocumentProperties(P)
Exit Function
EndMacro:
GetProperty = ""
End Function

(http://tinyurl.com/3s15)

by chouf

you could try

-open the vb editor with alt+F11
-next look for the thisworkbook icon on th left pane and double click it
-now in the code pane paste this code

Option Explicit

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

-this will put the value of now in the cell on your sheet every time the file is saved
-chang eit to your needs
-save and close the editor

This discussion is closed to new replies.