Excel Visual Basic Last Saved Date Time Stamp
I wanted to add a last saved date and time stamp to an Excel worksheet. In some cases, I would want this information displayed in a cell. Other times, I want it displayed in the header or footer of the document (when printed).
Here is the Visual Basic script code that shows how to do either:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Calculate the date and time stamp string
Dim TS As Date
TS = Now
Dim TSS As String
TSS = FormatDateTime(TS, 1) & " " & FormatDateTime(TS, 3)
'Place time stamp into the cell labeled SaveTimestamp
'Range("SaveTimestamp").Value = TSS
'Place time stamp into the header or footer for printing
Dim WS As Worksheet
For Each WS In Worksheets
WS.PageSetup.CenterFooter = "Last modified on " & TSS
'WS.PageSetup.LeftFooter = ThisWorkbook.FullName
Next WS
End Sub
That script will update the contents of the footer for every worksheet in the workbook to display the last saved date and time like this:
Last modified on Wednesday, November 17, 2010 12:54:01 PM
There is also commented-out functionality to update a labeled cell (called SaveTimestamp in the script above) with the last saved date and time.
In order to use the script, while in an Excel workbook open the Visual Basic editor (shortcut is Alt+F11). In the VBAProject window, on the left-hand side, double-click on ThisWorkbook. Paste the script into the code window and save.
The code completion in the Visual Basic script editor is very helpful. If you wanted to make the last saved date and time appear on the left-hand side of the header (for example) that is easily accomplished by changing one line of code (CenterFooter becomes LeftHeader). I left in (and commented out) an example of setting the left footer to be the full path to the workbook file itself. You can easily modify the script for your own purposes.
I used information from both W3Schools and PC Magazine for this solution.
In: Articles · Tagged with: Excel, Timestamp, VBA, Visual Basic
Shad finds high-value intersections between business strategy and information technology. He currently resides in the Madison, WI area.
on November 19, 2010 at 11:32 pm
Permalink
This is an interesting approach. Pretty cool.
on November 23, 2010 at 10:56 am
Permalink
Thanks, Farhan! There is a lot of flexibility with this approach if one is willing to make a few modifications to the script to get the exact behavior they desire.
on February 15, 2011 at 11:32 pm
Permalink
Awesome work! Keep posting good material.
on September 2, 2011 at 9:07 am
Permalink
Quick question – I pasted this code in my spreadsheet and it worked although when I modified it to have time stamp appear in left footer, the time stamp continued to appear in the center as well. How do I get rid of the center stamp?
on October 6, 2011 at 9:25 am
Permalink
If the macro already ran as-is (before you modified it) there is a time stamp written into the center footer as text. You can just delete the text.
Or, if you changed the location of the time stamp by copying this line:
WS.PageSetup.CenterFooter = “Last modified on ” & TSS
…and changing it to this:
WS.PageSetup.LeftFooter = “Last modified on ” & TSS
You will just need to either remove or comment out (by adding a single-quote character to the beginning of the line) the code for the center footer.
on November 3, 2011 at 3:08 pm
Permalink
I would like to use this code in an Excel template to insert the current date/time in the footer every time the template is called. Using the code as is above, it will always insert the last time the template itself was saved. Can I modify to have it insert the current date/time whenver it runs?
on December 5, 2011 at 2:57 pm
Permalink
ty very much Just used this and it worked perfectly.
on December 13, 2011 at 2:21 pm
Permalink
Son of a gun, this is so heplful!