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: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2013-01-24T15:14:20+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()

    Was this answer helpful?

    3 people found this answer helpful.
    0 comments
  2. Anonymous
    2013-02-13T21:50:52+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()

     

    That's your perspective as an MVP and VBA expert (and probably other programming languages too). The average user like me does not want to write a line of code to use a very basic function for many reasons especially when that function has existed in other parts of Microsoft office for many, many years. Is it so hard for the same product team in the same company to share the code and get basic functionality consistent among the sub-products?

    Besides, you are also ignoring another major issue which is our deployment of excel disables macros by default. While this may be trivial to you technically ("just" 2-lines of code as you put it), will still require the same painful, lengthy approval process to get through the IT department.

    Try to consider the perspective of the average excel user who has little control over the software installed on her computer and really doesn't know even where to start writing a macro program.

    Was this answer helpful?

    4 people found this answer helpful.
    0 comments
  3. Anonymous
    2013-02-27T17:19:46+00:00

    This works great with local files, but I tried getting the FileDateTime from a file on a SharePoint site in my company and it failed with "Invalid procedure call or argument".

    Any other suggestions?

    Was this answer helpful?

    0 comments
  4. Anonymous
    2013-03-07T15:18:33+00:00

    This worked for me once I figured out where to put the function.  Here's the article that explained that piece: http://www.exceldigest.com/myblog/2011/04/23/how-to-create-a-user-defined-function-in-excel-2007-or-excel-2010/.  I had tried the approach in the original article (get into Visual Basic and double click "ThisWorkbook" and entered the function there), but Excel wouldn't recognize the function.  The linked article says to get into Visual Basic and go to Insert -> Module,  then enter the function in the Module.  That worked.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments