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
    2016-10-21T13:01:03+00:00

    >>>

    As far as I know, the only way to get the saved date is via some non-simple vba macro that must be inserted into your worksheet

    <<<

    It isn't "non-simple" at all. It is quite simple, just two lines of code.

    Function LastSaveDate()

        Application.Volatile True

        LastSaveDate = FileDateTime(ThisWorkbook.FullName)

    End Function

    You can call this from a cell with

    =LastSaveDate()

    Awesome sauce!!  Thank you!!!

    Was this answer helpful?

    0 comments
  2. Anonymous
    2016-10-04T11:09:36+00:00

    If you are using the code:

    Private Sub Worksheet_Change(ByVal Target As Range)

        Range("A1").Value = Now

    End Sub 

    then that causes a recursive loop.  When the Change event changes a cell it triggers the change event again which changes a cell and causes the change event again . . .

    In my experience, Excel has some type of safeguard that usually catches and stops this loop eventually - but perhaps you home computer doesn't have enough memory to get to that point and it errors off.  Generally when a subroutine is called, it puts information on the situation on the "stack" so when the routine ends it can walk back up through the calling tree.  So when you repeatedly fire this event it appears you are running out of available stack space. 

    The fix:

    Private Sub Worksheet_Change(ByVal Target As Range)

      On Error GoTo ErrHandler

       Application.EnableEvents = False

        Range("A1").Value = Now

    ErrHandler:

      Application.EnableEvents = True

    End Sub

    -- 

    Regards,

    Tom Ogilvy

    Was this answer helpful?

    0 comments