inserting LastSavedDate/Time (Win 2000, Excel 2002 SP-2)

Topic

New Reply

Hi,

I need code to insert the last saved date and time in a spread sheet.
Can someone help me?

Thanks!!!

bjorn

Reply | Quote

Replies

Check out this post

Reply | Quote

You can create a function that will return the last saved date and time of the active workbook. If you put this function in a standard module in your personal macro workbook Personal.xls, it will always be available. Here is the code:

Function SaveDateTime() As Date
SaveDateTime = ActiveWorkbook.BuiltinDocumentProperties(“Last Save Time”)
End Function

You can use it in a cell in a formula:

=PERSONAL.XLS!SaveDateTime()

Don’t forget to format the cell as a date, or time, or both. The formula will return #VALUE if the workbook has never been saved.

Remark: if you use a non-english version of Excel, you may have to replace “Last Save Time” by the local equivalent (and Personal.xls may have another name too.)

Reply | Quote

[indent]

---

Remark: if you use a non-english version of Excel, you may have to replace “Last Save Time” by the local equivalent

---

[/indent]Or use 12 as the argument:

Function SaveDateTime() As Date
    SaveDateTime = ActiveWorkbook.BuiltinDocumentProperties(12)
End Function

Reply | Quote

Thanks, I noted that in the post you referred to (after I had posted my own reply). Good idea.

Reply | Quote

Hi Hans, I’m trying to use the function you set out for inserting the last saved date/time in a cell, which I have currently in a module in the spreadsheet as:

Function SaveDateTime() As Date
SaveDateTime = ActiveWorkbook.BuiltinDocumentProperties(12)
End Function

I don’t want it in personal.xls because it’s moved around between machines. Then, in a cell formatted as a date, I have =SaveDateTime().

However, no matter what I do, it returns the date as 1 September 2002, 1:30 am!! I’m quite willing to believe I did edit it then (it’s several years old), but it’s an ongoing spreadsheet and was last saved today.

What am I doing wrong?!

Reply | Quote

Hi Beryl,

It might be that property #12 is the Last Save Time in Excel 2000/2002, but something else in Excel 97. You’re probably getting the Creation Date instead of the Last Save Time. Try using the name of the property instead of its index number:

ActiveWorkbook.BuiltinDocumentProperties(“Last Save Time”)

Reply | Quote

Hi Hans, thanks for your reply, but I did use ‘Last Save Time’ originally, and only changed it to #12 when that didn’t work, thinking the same thing you did, that it wasn’t called that in XL97!

I’ll try Steve’s suggestion and see if that can me the information I need, which is the date/time that the spreadsheet was *last* updated, when I open it *this* time!

Reply | Quote

Hi Hans, thanks for your reply, but I did use ‘Last Save Time’ originally, and only changed it to #12 when that didn’t work, thinking the same thing you did, that it wasn’t called that in XL97!

I’ll try Steve’s suggestion and see if that can me the information I need, which is the date/time that the spreadsheet was *last* updated, when I open it *this* time!

Reply | Quote

Hi Hans/Steve, just to let you know I’ve found a way to do it – a combination of both of your suggestions, although neither worked on their own! Although the BeforeSave updated SaveDateTime, it refused to display in the cell (=SaveDateTime()), which continued to read 1 Sep 2002 no matter what, even after closing and reopening.

I now have the SaveDateTime function Hans suggested in a module, and these two macros in ThisWorkbook – needless to say A2 is where I wanted to display the last saved date/time!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ThisWorkbook.BuiltinDocumentProperties(“Last Save Time”).Value = Now
End Sub

Private Sub Workbook_Open()
Range(“a2”).Value = SaveDateTime
End Sub

So, many thanks to both of you

Reply | Quote

Hi Hans/Steve, just to let you know I’ve found a way to do it – a combination of both of your suggestions, although neither worked on their own! Although the BeforeSave updated SaveDateTime, it refused to display in the cell (=SaveDateTime()), which continued to read 1 Sep 2002 no matter what, even after closing and reopening.

I now have the SaveDateTime function Hans suggested in a module, and these two macros in ThisWorkbook – needless to say A2 is where I wanted to display the last saved date/time!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ThisWorkbook.BuiltinDocumentProperties(“Last Save Time”).Value = Now
End Sub

Private Sub Workbook_Open()
Range(“a2”).Value = SaveDateTime
End Sub

So, many thanks to both of you

Reply | Quote

Hi Beryl,

It might be that property #12 is the Last Save Time in Excel 2000/2002, but something else in Excel 97. You’re probably getting the Creation Date instead of the Last Save Time. Try using the name of the property instead of its index number:

ActiveWorkbook.BuiltinDocumentProperties(“Last Save Time”)

Reply | Quote

Hans’ supposition is incorrect. In XL97 BuiltinDocumentProperties(“Last Save Time”) is #12.

“Late save time” however, is one of the properties that XL does not keep current (at least in XL97). If you chose to use this property you must assign the value whenever you save the file (add this code to a beforesave event for any file you want to use the property with:

ThisWorkbook.BuiltinDocumentProperties(“Last Save Time”).Value = Now

Then when you call the property it will be current.
If you never run a line like this, (in XL97, at least) you will get an error since the property is “empty”
If you ran a line like this on “1 September 2002, 1:30 am” and never ran it again, it will continue to read this time.

Perhaps some of the versions keep it “up-to-date”, and others do not. If you use it with the earlier versions (which I know do not update it) the date will not be changed (automaticlly) when someone saves it. You will have to use code like above.

An alternative is to use the filesystem object. This has a “problem” though that you can only determine the “modification data” when the file is closed. If the file is open it reads the current date/time (I think it is a Windows issue, since explorer does the same thing).

Steve

Reply | Quote

Hi Steve,

Thanks for checking in Excel 97. In Excel 2002, Last Save Time is updated automatically; I don’t think I ever used this property when I had Excel 97, so I didn’t know that it was different there.

Excel is a bit strange in that it sets the Last Modified date to Now when a workbook is opened, and restores the original date if the workbook is closed without saving it. Word and PowerPoint don’t do this, they change the Last Modified date only when a document or presentation is saved.

Reply | Quote

Hi Steve,

Thanks for checking in Excel 97. In Excel 2002, Last Save Time is updated automatically; I don’t think I ever used this property when I had Excel 97, so I didn’t know that it was different there.

Excel is a bit strange in that it sets the Last Modified date to Now when a workbook is opened, and restores the original date if the workbook is closed without saving it. Word and PowerPoint don’t do this, they change the Last Modified date only when a document or presentation is saved.

Reply | Quote

Hans’ supposition is incorrect. In XL97 BuiltinDocumentProperties(“Last Save Time”) is #12.

“Late save time” however, is one of the properties that XL does not keep current (at least in XL97). If you chose to use this property you must assign the value whenever you save the file (add this code to a beforesave event for any file you want to use the property with:

ThisWorkbook.BuiltinDocumentProperties(“Last Save Time”).Value = Now

Then when you call the property it will be current.
If you never run a line like this, (in XL97, at least) you will get an error since the property is “empty”
If you ran a line like this on “1 September 2002, 1:30 am” and never ran it again, it will continue to read this time.

Perhaps some of the versions keep it “up-to-date”, and others do not. If you use it with the earlier versions (which I know do not update it) the date will not be changed (automaticlly) when someone saves it. You will have to use code like above.

An alternative is to use the filesystem object. This has a “problem” though that you can only determine the “modification data” when the file is closed. If the file is open it reads the current date/time (I think it is a Windows issue, since explorer does the same thing).

Steve

Reply | Quote

Hi Hans, I’m trying to use the function you set out for inserting the last saved date/time in a cell, which I have currently in a module in the spreadsheet as:

Function SaveDateTime() As Date
SaveDateTime = ActiveWorkbook.BuiltinDocumentProperties(12)
End Function

I don’t want it in personal.xls because it’s moved around between machines. Then, in a cell formatted as a date, I have =SaveDateTime().

However, no matter what I do, it returns the date as 1 September 2002, 1:30 am!! I’m quite willing to believe I did edit it then (it’s several years old), but it’s an ongoing spreadsheet and was last saved today.

What am I doing wrong?!

Reply | Quote