Welcome toExcel.Tips.Net
Tips.Net Home
ExcelTips Home
Ask an Excel Question
Make a Comment
ExcelTips FAQ
ExcelTips Premium
Learn Access Now
Free Printable Forms
Beauty Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Legal Tips
Money Tips
Organizing Tips
Pest Tips
Pet Tips
Wedding Tips
Word2007 Tips
WordTips
Advertise on the
ExcelTips Site
Adding a Little Animation to Your Life
Converting a Range of URLs to Hyperlinks
Making the Formula Bar Persistent
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.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2190) applies to Microsoft Excel versions: 97 2000 2002 2003 2007
Got the Time? Understanding the ins and outs of working with times and dates can be confusing. Remove the confusion--ExcelTips: Times and Dates is an invaluable resource for learning how best to work with times and dates.