Date in header indicating the last save.

Topic

New Reply

Do you know of a procedure that will place the date in a header to indicate the last date an Excel document is saved instead of the date it was printed. Any ideas would be appreciated.

Reply | Quote

Replies

WOI,

If you’re using Excel 2010 Click on View Tab –> Page Layout
Scroll to bottom of one page and click on “Click to add footer”
Click on the Design Tab it it doesn’t show automatically
Click on Current Date add space and Click on Current Time if desired. :cheers:

Only problem w/this is it will update everytime the file is opened so you can’t see when it was last saved!
Most likely you’ll need a macro that executes on the Before Save event and places the current date/time in the footer as text. If I get a chance I’ll work one up. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

WOI,

Ok, here’s a macro that will do what I think you want. This macro will change the header of the Excel file it resides in to the current date if the workbook is Saved. If you close the workbook w/o saving the date will remain unchanged. Make sure to put the following code in the This Workbook Module in the VBE. :cheers:

Code:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
        Cancel As Boolean)
        
    Cancel = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = "Last Update " & Format(Now(), "mm/dd/yyyy")
    End With
    
End Sub

Note you can change it to add the time or move the code to be in the left of center section of the header, that’s why I retained those elements in the code.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Alternatively, use scripting.

dim objFso as object, objFile as object

Set objFso = CreateObject(“Scripting.FileSystemObject”)
Set objFile = objFso.GetFile(“YourFile.xls”)
Debug.Print objfile.DateLastModified

Reply | Quote

It worked….Thank you!!

Reply | Quote

It worked….Thank you!!!

Reply | Quote