Insert the date an Excel Workbook was last modified.

Anonymous
2010-11-17T00:13:29+00:00

Is there a way to insert, into a cell, the date that a workbook was last modified?

Perhaps by getting the modified date from the file's properties into a cell.

Thanks in advance.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2010-11-17T00:29:39+00:00

Hi,

To modify a workbook you have to save it so we can use the before_save event

ALY+F11 to open vb editor. Double click 'ThisWorkbook' and paste this code in on the right. Change the sheet and range to suit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Sheets("Sheet1").Range("A1").Value = Now

End Sub

If this post answers your question, please mark it as the Answer.

Mike H

Was this answer helpful?

100+ people found this answer helpful.
0 comments

86 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2015-08-04T18:24:31+00:00

    While responders are trying to be helpful, the issue is that Word provides an NON-MACRO way to put the date/time the document was saved:  Insert tab --> Header, click at the location you want the Saved Date, which will show the "Header & Footer Tools" tab, with the "Design" tab below it.  You can then click on the "Document Info" drop-down and select "Field..."

     While it may take several steps to get there, it is still much more accessible than diving down into programming macros.  It is already in Word and PowerPoint, which indicates the value of having it.

    And I'll add that not having to re-learn how to use the Office members is what makes them "sticky" over open-source alternatives; employers don't have to train (or re-train) employees.  What they learn in Word should transfer to Excel, PowerPoint, Publisher -- you get the idea.  This improves productivity for tasks that aren't performed frequently, but a boss may ask his assistant to perform:  "Make all my spreadsheets, documents, presentations look uniform, no matter who produced them, INCLUDING WHEN THEY WERE LAST MODIFIED; I want to know what has changed over time to show our progress."

    <sarcasm> Then again, maybe that doesn't matter. </sarcasm>

    Was this answer helpful?

    0 comments
  2. Anonymous
    2015-08-04T13:44:48+00:00

    Hi,

    there is an issue when using Chris function with XLS files, as Excel changes the last modified date temporarilywhen XLS files are opened :

    https://support.microsoft.com/en-us/kb/826741#/en-us/kb/826741

    Excel does not alter XLSX and XLSM file dates.

    So, these methods have issues :

    • Scripting.FileSystemObject's method GetFile(Thisworkbook.FullName)
    • Thisworkbook.dateLastModified

    They will show incorrect dates if you apply them on XLS files.

    I suggest to prefer

    ThisWorkbook.BuiltinDocumentProperties("Last Save Time").Value

    as this value is not temporarily altered by Excel.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2015-07-29T06:47:10+00:00

    This help page support get last modified for thisworkbook.

    how with another no active workbook???

    Was this answer helpful?

    0 comments
  4. Anonymous
    2015-06-15T14:14:02+00:00

    Hi AdyImp,

    I had the same result and tried Pierre69F's suggestion by using the following code:

    Private Function LastSaveDate()

    Application.Volatile

    LastSaveDate = ""

    On Error Resume Next

    LastSaveDate = ThisWorkbook.BuiltinDocumentProperties("Last Save Time").Value

    ' In a cell enter: =lastsavedate()

    ' This macro must be stored in a module; not in a sheet

    End Function

    Worked a treat.

    Also make sure your cell is formatted to display the date serial number in the format your prefer.

    Cheers

    Antshark

    Was this answer helpful?

    0 comments