Last Saved Date in a Footer

If you are familiar with Word, you know that it includes a rich set of fields that are used to place dynamic information in a document. One of the things that can be placed in a document is the date that a file was last saved. It would be nice if such a capability were built into Excel, but such is not the case. Excel has no "fields," and it has no way to put dynamic information of this type into headers or footers.

There is a way around this limitation, but the solution requires the use of a macro. The reason is because you are accessing system information—information outside of Excel itself—and that information can only be retrieved using a programming language such as VBA. One approach is to add some code that runs whenever a workbook is saved. The code would update the desired header or footer with the current date.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
  Cancel As Boolean)
    Dim sht As Worksheet

    For Each sht In Sheets
        sht.PageSetup.CenterFooter = _
          "Last Saved: " & Format(Date, "mmmm d, yyyy")
    Next
End Sub

This macro, which should be stored in the ThisWorkbook object for the workbook you want to affect, steps through each worksheet in the workbook and changes the center portion of the footer. If today is September 12, 2009, then after running, the center footers will all be set to "Last Saved: September 12, 2009". Notice that the name of the macro (Workbook_BeforeSave) indicates that the macro will be run just before the workbook is actually saved.

There is another approach you can use. This one involves requesting from Windows the actual date and time a file was saved.

Private Sub Workbook_Open()
    Dim sTemp As String
    Dim sht As Worksheet

    sTemp = FileDateTime(ActiveWorkbook.FullName)
    sTemp = "Last Saved: " & sTemp
    For Each sht In Sheets
        sht.PageSetup.RightFooter = sTemp
    Next sht
End Sub

This macro is designed to run whenever a workbook is first opened—it is saved as the Workbook_Open procedure of the ThisWorkbook object. The workhorse of the macro is the line that calls the FileDateTime function. This function can be used to determine the date and time any file was saved. It requires a full path name of a file, which is supplied by the FullName property of the ActiveWorkbook object. This date and time is then, in this case, placed in the right footer of all the worksheets in the workbook.