File Date in Excel Footers

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?

[Derek]

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

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

[NateO]

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 ]

[NateO]

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

[KASims]

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)

[lenze]

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?

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetUp.LeftFooter = BuiltInDocumentProerties("Last Save Time")
End Sub

lenze