Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Mar 2001
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Modified Date in Footer

    Would like to put the date the file was modified in the footer (or header). If this were Word, the syntax is for what I have been told is a "field code" is {savedate}. For Excel, these "field codes" have a very different syntax: &[date] for the current date. Is there a "field code" for the file-last-modified date? Is there any other means of placing the date the file was last modified into the footer?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Modified Date in Footer

    The following VBA code will palce the last save Date and Time in the right footer panel. There is no error checking, so if the file was not saved it will fail

    Sub LastSaved()
    Dim DateStamp As Date
    DateStamp = FileDateTime(ActiveWorkbook.FullName) 'Get Time& date of last Save
    With ActiveSheet.PageSetup
    .RightFooter = Str(DateStamp) 'Set Right Footer
    End With
    End Sub

    The footer entry is static, i.e. will not update automatically if the save date changes. It could of course be placed in the Workbook_BeforePrint event to ensure it is updated whenever the file is printed.

    Andrew C

  3. #3
    New Lounger
    Join Date
    Feb 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modified Date in Footer

    Sorry to ask a dumb question but how do you do this in the before print event? I am not familiar much with VBA and would realy appreciate dummy-proof instruction.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Modified Date in Footer

    Go to VB editor (alt-F11)
    In project Explorer window (usually upper left segment), dbl-click on ThisWorkbook

    Copy and paste this code to the right window pane
    <pre>Option Explicit
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim DateStamp As Date
    DateStamp = FileDateTime(ActiveWorkbook.FullName) 'Get Time& date of last Save
    With ActiveSheet.PageSetup
    .RightFooter = Str(DateStamp) 'Set Right Footer
    End With
    End Sub
    </pre>


    This will run before anything is printed and add the date stamp as the right footer to the active sheet.

    Remember: This code is NOT added to a normal module, but the ThisWorkbook object.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •