Display in a Cell the Date the file last saved.

[ushankar]

Can someone please provide the VBA codes.

In Excel 2007; every time the file is saved, i want the saved date copied to the designated cell in the same file.

Thank You.

 

[SirJB7]

Hi, ushankar!


You can't have stored the date&time of last saving within a cell, because d&t is updated by the operating system and that's after many internal operations, which not include storing a value within a cell from Excel. But you can retrieve it at next open time (or when you want) using the function FileDateTime.

You can use this statement:

Worksheets("X").Range("A1").Value=FileDateTime(file_path)


What you can do from Excel is get a fairly accurate approach to that value, with the event Workbook_BeforeSave (from the VBA editor, double click on ThisWorkbook, Workbook, BeforeSave from the right drop down box).

You can place these statements there:

-----

Application.EnableEvents=False

Worksheets("X").Range("A1").Value=Now()

Application.EnableEvents=True

-----

The Application.EnableEvents are not necessary if you don't have any Worksheet or Workbook Change events with code that might interrupt saving process, but leaving them you're sure they won't interfere.


Regards!

 

[ushankar]

Hi SirJB7

the codes will force the user to save the file every time is opened and closed. I want to save the file only whenever something is changed in the file. Is there is a way to this using formula please?

 

[SirJB7]

Hi, ushankar!

Storing a value on a cell after always marks the workbook as not saved...

First approach will update the workbook, so before quitting the user will be prompted to save it or not.

Second approach won't.

Regards!