Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Time Updated in Footer
All, I am perplexed with this one ...
I am trying to put a footer in my workbook that will show the last time the file was saved (as opposed to the [date] & [time] functions that show the time printed). I have created the following code and put it in my Personal workbook, BUT, it forces me to remember to run this each time I save the file. Is there something like a [datestamp] variable I can put in my footer that will dynamically update? Any other thoughts? Thanks!! Sub TimeStampFooter() On Error GoTo handler With ActiveWorkbook Filename = .Path & "\" & .Name timestamp = FileDateTime(Filename) numsheets = .Worksheets.Count For x = 1 To numsheets Sheets(x).PageSetup.RightFooter = "&8Last Updated: " & timestamp Sheets(x).PageSetup.LeftFooter = "&8" & Filename Next x End With Exit Sub : handler MsgBox ("Error - please ensure file is saved before contiuing") Exit Sub End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Time Updated in Footer
Hi
if you want to insert the last save date in your header/footer insert the following code in your workbook module Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wkSht As Worksheet For Each wkSht In Me.Worksheets With wkSht.PageSetup .CenterFooter = Format _ (ActiveWorkbook.BuiltinDocumentProperties _ ("Last save time"),"DD.MM.YYYY") End With Next wkSht End Sub -----Original Message----- All, I am perplexed with this one ... I am trying to put a footer in my workbook that will show the last time the file was saved (as opposed to the [date] & [time] functions that show the time printed). I have created the following code and put it in my Personal workbook, BUT, it forces me to remember to run this each time I save the file. Is there something like a [datestamp] variable I can put in my footer that will dynamically update? Any other thoughts? Thanks!! Sub TimeStampFooter() On Error GoTo handler With ActiveWorkbook Filename = .Path & "\" & .Name timestamp = FileDateTime(Filename) numsheets = .Worksheets.Count For x = 1 To numsheets Sheets(x).PageSetup.RightFooter = "&8Last Updated: " & timestamp Sheets(x).PageSetup.LeftFooter = "&8" & Filename Next x End With Exit Sub : handler MsgBox ("Error - please ensure file is saved before contiuing") Exit Sub End Sub . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Time Updated in Footer
Frank,
Thanks - that's perfect. Now, though, I want my users to be able to add this code to a workbook by hitting a custom button on their toolbar. I can add code to their personal workbook - but can that code "create" more code? -----Original Message----- Hi if you want to insert the last save date in your header/footer insert the following code in your workbook module Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wkSht As Worksheet For Each wkSht In Me.Worksheets With wkSht.PageSetup .CenterFooter = Format _ (ActiveWorkbook.BuiltinDocumentProperties _ ("Last save time"),"DD.MM.YYYY") End With Next wkSht End Sub -----Original Message----- All, I am perplexed with this one ... I am trying to put a footer in my workbook that will show the last time the file was saved (as opposed to the [date] & [time] functions that show the time printed). I have created the following code and put it in my Personal workbook, BUT, it forces me to remember to run this each time I save the file. Is there something like a [datestamp] variable I can put in my footer that will dynamically update? Any other thoughts? Thanks!! Sub TimeStampFooter() On Error GoTo handler With ActiveWorkbook Filename = .Path & "\" & .Name timestamp = FileDateTime(Filename) numsheets = .Worksheets.Count For x = 1 To numsheets Sheets(x).PageSetup.RightFooter = "&8Last Updated: " & timestamp Sheets(x).PageSetup.LeftFooter = "&8" & Filename Next x End With Exit Sub : handler MsgBox ("Error - please ensure file is saved before contiuing") Exit Sub End Sub . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel: subtract updated time from earilier | Excel Worksheet Functions | |||
Time or row cell updated | Excel Worksheet Functions | |||
date and time updated automatically | Excel Worksheet Functions | |||
Current date and time updated automatically | Excel Discussion (Misc queries) | |||
How do I print the last date a worksheet was updated in a footer? | Excel Discussion (Misc queries) |