Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Last saved time stamp

0

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? 

Answer
Discuss

Answers

1

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.

Discuss

Discussion

Variatus:  I am only qualified to fly at 10,000 feet, your answer started at 30,000 feet and lost me.  If there is a simpler way to handle it then please let me know.  If not, I will live without it.  Thank you for the attempt.   
mrandall (rep: 4) May 2, '18 at 1:04 pm
My answer started at 5,000 ft and I'm sure you are fully qualified - just afraid of heights lol: Please try again. Follow the step-by-step instructions in the first paragraph to install the function. (It's just the same as what you have done before). Then follow the second paragraph to use the function. Actually, just copy paste the function in any cell of any worksheet.
Forget about the rest. Instead, if you have any problem, ask here.
Variatus (rep: 4889) May 2, '18 at 10:01 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login