I am trying to have a cell in my excel file that automatically updates to the last saved date. I have tried inserting a module I found on-line, but it doesn't seem to work. Ideas?
I am trying to have a cell in my excel file that automatically updates to the last saved date. I have tried inserting a module I found on-line, but it doesn't seem to work. Ideas?
This is a UDF (User Defined Function) which you should copy to a standard code module in your workbook (Alt+F11 to open the VB Editor. Right-click on the workbook name in the Project Explorer window at the top left. Select Insert and Module. Paste the code in the blank panel on the right.)
Function DocProp(Prop As String)
Application.Volatile
DocProp = ThisWorkbook.BuiltinDocumentProperties(Prop)
End Function
Use the function like any of Excel's built-in function. Write, for example
=DocProp("last save time")
in any cell. (Use the correct name for any other built-in property you wish to display.) In this instance the cell will show a number, like 43221.763433. This is Excel's code for a date/time. Format the cell to show whatever you want to show, and why not date and time? (Can also show oinly date or only time or any part of both or either.) You can specify any valid format in Format > Cells.
Use this code to list all built-in properties by their names.
Sub ListProperties()
' List built-in properties in the Immediate Window
Dim Dprop As DocumentProperty
For Each Dprop In ActiveWorkbook.BuiltinDocumentProperties
Debug.Print Dprop.Name
Next Dprop
End Sub
Of course, you can also view the document properties in other ways. You may like to look at the Statistics tab of the document properties accessible from the File menu.