Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: File Date in Excel Footers

  1. #1
    Guest

    Default

    How can I get the file save date to print automatically in the footer of a worksheet? Since I can do this in Word, I'm sure it should be able to be done in Excel 97. Any ideas?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Try going to View, Header & Footer...,Custom Footer, and click the icon that looks like calendar pages. (This is Excel 2000 but I think 97 is very similar)
    regards
    Derek

  3. #3
    Guest

    Default

    Derek....thanks, but that puts the current date/time, not the date/time the file was last saved.

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can run a macro like this:

    Sub myFoot()
    detl = FileDateTime(ActiveWorkbook.FullName)
    ActiveSheet.PageSetup.LeftFooter = detl
    End Sub

    You can change LeftFooter to CenterFooter or Right Footer.

    You'd need to refresh this before Printing.

    HTH. Cheers, Nate

    [ This Message was edited by: NateO on 2002-02-28 12:31 ]

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Place the above code in a normal module.
    Now on the sheet in question, right-click on the tab and select "view code." Paste the following code in there.


    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    application.run("myFoot")
    End Sub


    This page footer should refresh automatically before you print.

    Cheers, Nate

  6. #6
    New Member
    Join Date
    Jun 2009
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: File Date in Excel Footers

    The code works for me if I manually run the macro, but it did not automatically refresh before I printed.

    I right-clicked on the tab, clicked view code, and entered:

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Application.Run ("FileSaveDate")
    End Sub

    (Note: I changed the name of the macro to FileSaveDate)

    As I said above, if I manuall run the macro and then print, it works. But it did not automatically refresh when I printed. What am I missing and/or doing wrong? (I am running Excel 2002 SP3)

  7. #7
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: File Date in Excel Footers

    It's past Nate's bedtime. I think he meant to say place the code in the ThisWorkBook module. RightClick the Excel icon to the left of the word "File" on the menu bar and choose "View Code". Nate, why not the whole thing in the WB module?
    Code:
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    ActiveSheet.PageSetUp.LeftFooter = BuiltInDocumentProerties("Last Save Time")
    End Sub
    lenze
    If you have to tell your boss you're good with Excel, you're NOT!!
    All I know about Excel I owe to my ignorance!
    Scotch: Because you don't solve great Excel problems over white wine

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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