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
    2013-10-06T07:41:11+00:00

    I used this VB code and found useful.

    Let me explain step by step. May be this is useful for others.

    1. open up your work sheet.
    2. Press Alt + F11. You will find a Visual Basic scree opening up.
    3. Go to 'Insert' menu and select 'Module'. you will find a screen opening up
    4. Copy the below code and paste in the window.

    Function LastSaveDate()

    Application.Volatile True

    LastSaveDate = FileDateTime(ThisWorkbook.FullName)

    End Function

    1. Save this file and close the VB window.
    2. in the cell where you want to insert the last saved date type

       = LastSaveDate()

    and save the exel file.

    It works right without any problem.

    Was this answer helpful?

    9 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-03-08T20:45:49+00:00

    Where are you putting the code? Do not put it in the ThisWorkbook module or in one of the sheet modules. It needs to go into a regular code module. In VBA, go to the Insert menu and choose "Module". This will insert a new code module named Module1 into your project. Put the code in that module.

    Was this answer helpful?

    3 people found this answer helpful.
    0 comments
  4. 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