Last Saved Date and Printed Date in Footer

On 2002-07-12 08:35, jluecht wrote:
How can you show the last saved date in the footer? The only option that I can find is &[Date], which doesn't help, since this is simply today's date.
What I would like the footer to display is the last saved or modified date, and if the file is printed, I would like to include the printed date. Any suggestions??
I have searched the postings and did not find this particular question.
Thanks for your help!

Click to expand...

Ok first every workbook has event procedures. The one you need is Workbook_BeforePrint() It is in the help file. The headers and footers may look dynamic to us but they really use a similar approach in that each time before they print they re-calculate the date and time.
So open the workbook, press F11, press Ctrl-R, and highlight your workbook's ThisWorkbook and press F7. Then cut and paste my code and give it a try.
I have been working on this one for a while, so if anyone has sudgestions...

Rocky-

Just wanted to make a comment about your code. It works nice. I have several uses for it already! If it only didn't hesitate when running. I also noticed that it doesn't work with a macro I have for printing. I guess I'll have to tweak my macro!

Well thank you!
Yes I should be more careful when publishing code. Add the following at the begining:
Application.ScreenUpdating = False and all will be quiet while it runs. This macro is not the best I am sure, but as you point out it works. Sometimes it will drive a user a little nuts if they try to set a header or footer...
As for you macro it shouldn't be too hard as they each work seperatly. Study the code and maybe be you can intergrate them in to a single working macro.

All the best!
Rocky...

Please pardon my ignorance Rocky, but I tried to follow your instructions verbatim, and had absolutely no luck. Please enlighten...what am I missing/not understanding? I am a complete novice when it comes to Excel macros.
Thanks

OK, not a problem. I am neither a teacher nor an expert, but I will hang with you until you have it working. Like I say this piece of code is a work in progress. The snippet was cut directly from one of my workbooks. The other guy got it to work, but he sounds pretty experienced to me.
Excel is a dynamic environment. Unless you install a fresh copy (but don't do that) the actual keystroke sequence might vary. Please follow these steps and tell me where you have trouble.
1) Start Excel clean so you have Book1
2) Type Hello World in A1. You have to have something to print.
3) Press alt-F11 you should see the Visual Basic window, yes/no?
4) In the left upper corner should be a smaller window Project - VBAProject if not press ctrl-R yes/no?
5) This looks a little like a directory tree. The last item in the tree should be ThisWorkbook Yes/no?
6) Double click ThisWorkbook and a new window should appear to the right titled Book1 - ThisWorkbook (Code) Yes/no?
7) Cut this code and paste it in the window.
Are we OK? yes/no
Save the workbook. You will get an error if you execute this code on a workbook that has not been saved.
9) Close the VBA Editor (press alt-F11 again. Then Print or Preview Have we got it this time? Yes/no?
I truly hope this helps!
This message was edited by Rocky E on 2002-07-12 20:24

Hi Rocky,

Your code is EXACTLY what I was struggling with on my own (beginner at Visual Basic, and a first-timer on this site).

I added some font style formatting (that was challenging...trying to sort out the &'s and the "'s!) and also the file path (see my code below).

It works just dandy, but when I run the macro, it only goes on the active sheet.

What I want: when I run the macro, I want the header and footer info to go on ALL worksheets, existing AND any that I add in the future. It would be kind of like a default template for any worksheet in the workbook in which I run the macro.

Here's my code, based on the one you posted:

Sub HeaderFooterInfo()
For Each wk In Worksheets
With ActiveSheet.PageSetup
.LeftHeader = "&""Arial,Italic""&06" & "Last Modified on " & ActiveWorkbook.BuiltinDocumentProperties.Item("Last Save Time")
.CenterHeader = "&""Arial,Italic""&06" & "Page &P of &N"
.RightHeader = "&""Arial,Italic""&06" & "Printed on &D &T"
.LeftFooter = "&""Arial,Italic""&06 &D"
.CenterFooter = "&""Arial,Italic""&06" & ActiveWorkbook.FullName
.RightFooter = "&""Arial,Italic""&06 &A"
End With
Next
Application.ScreenUpdating = True
End Sub

Thanks for all your help!

Lorraine in Texas

Hi Lorraine in Texas,

Well very good, but please re-read all the posts. The "Sub HeaderFooterInfo()" tells me that you didn't store it in the beforeprint. I order to effect all worksheets this procedure has to be stored in ThisWorkbook. Somebody may know another way, but that is how mine works.

Hi Rocky,

Still here, still struggling.
I went back through the posts and followed your enumerated instructions to jluecht to a 't'.

As well, I changed my code to look identical to yours (aside from the actual text content for the headers and footers).

When I open book1 and open the visual basic (alt-F11), the personal.xls shows on the header, and the personal.xls ThisWorkbook is the last one on the Project Explorer list. My understanding is that personal.xls is similar to normal.dot for WORD, in that it acts like a template for ALL Excel worksheets. That is where my code is.

When I save, close visual basic, and do a print preview or a print, there are no headers or footers.

What am I doing wrong?

I'd like to have this thing (if I can ever get it to work) to be under command control (either in a drop down menu or a command key, ie ctrl-xx) so that I could choose whether or not to execute the macro. If that is the case, doesn't it need to be a separate module (ie not in ThisWorkbook) of personal.xls?

Here's my code that is not working:


Private Sub Workbook_BeforePrint(Cancel As Boolean)
For Each wk In Worksheets
With ActiveSheet.PageSetup
.LeftHeader = "&""Arial,Italic""&06" & "Last Modified on " & ActiveWorkbook.BuiltinDocumentProperties.Item("Last Save Time")
.CenterHeader = "&""Arial,Italic""&06" & "Page &P of &N"
.RightHeader = "&""Arial,Italic""&06" & "Printed on &D &T"
.LeftFooter = "&""Arial,Italic""&06 &D"
.CenterFooter = "&""Arial,Italic""&06" & ActiveWorkbook.FullName
.RightFooter = "&""Arial,Italic""&06 &A"
End With
Next
Application.ScreenUpdating = True
End Sub

Thanks again for your help. I appreciate your patience with us novices!

My 2 cents, hope it helps.<pre>Private Sub Workbook_BeforePrint(Cancel As Boolean)

Application.ScreenUpdating = False
On Error GoTo NotSaved

For Each wsSheet In ThisWorkbook.Worksheets
With wsSheet.PageSetup
.LeftFooter = "Last Modified on " & ThisWorkbook.BuiltinDocumentProperties.Item("Last Save Time")
.RightFooter = "Printed on &D &T"
End With
Next
Application.ScreenUpdating = True

Exit Sub

NotSaved:
Application.ScreenUpdating = True
MsgBox "Before_Print code cannot run until Book has been saved."
Cancel = True
End Sub</pre>

_________________
Win 98SE - Office 2K
This message was edited by dsnbld on 2002-07-23 18:15