Putting Last Modified Date and Time in Custom Footer

[PropHead]

<disclaimer>
I know my question has been asked and answered before. But in nearly
two hours of web searching, none of the answers I found were
satisfactory. This should be trivial, yet it doesn't appear in a
Microsoft Excel FAQ, nor do the MS Excel MVPs seem to give a
straightforward answer for someone who's *not* familiar with Visual
Basic or the inner workings of Excel. So pardon my extreme
frustration!
</disclaimer>

I need to put the last modified date and time in the footer of all my
Excel files. I have some specific requirements:

1. It must be the last *modified* date and time, not just the last time
the file was *saved*. An "open, don't change anything, close" does not
count, in my world at least, as a "modification" so I don't want the
date updated in that case.

2. I need to see the date *and* the time, in this format:

Last Modified: MM/DD/YY HH:MM AM/PM

3. This should show up *automatically* on every page, of every sheet,
of every workbook, of every Excel file I create going forward. I don't
mind going back to existing files and sticking something in them. But
I'd like to create a template or something so that all new files I
create include the timestamp.

4. I shouldn't have to have a cell in a spreadsheet "holding" the date.
There's so much metadata "hidden" in MS Office docs that I can't
believe the "last modified" date/time is not already there somewhere.

5. If I have to use Visual Basic, create a macro, or whatever, I
suppose I will, as long as someone can give me extremely detailed
instructions. But I am absolutely boggled that there isn't some
built-in function to do this. I can create a custom footer with "Page
x of y" - I'm shocked there isn't already something like "Last
Modified: date/time".

Can anyone tell me the solution I'm so desperately looking for? I've
seen similar queries all the way back to the mid 1990's asking a
similar question. Surely Microsoft could have provided something by
now, right?

Thanks *so much* in advance! I'm s-o-o-o-o-o-o frustrated!!!

Scott
(e-mail address removed)

 

[Bob Phillips]

Maybe it's me, but that seems an awfully insistent post, and somewhat
ungracious.


Bob

 

[JE McGimpsey]

Thanks *so much* in advance! I'm s-o-o-o-o-o-o frustrated!!!

Obviously. However, the solution you want isn't obvious.

Suppose you opened a workbook, made a change and printed it out without
saving first. Should the "last modified" date be the current date?

What if you then closed the workbook without saving. What should the
"last modified" date be the next time you open the workbook?

You don't want to have a cell in the spreadsheet holding the date, yet
you want the date printed on every page of every sheet. Does that mean
you want it printed in the header or footer? Or somewhere else?

What if the workbook has volatile functions (like TODAY(), NOW(), etc),
where the data automatically changes when the file is opened, even if
the user doesn't make an entry?

 

[JE McGimpsey]

Sorry - didn't see the entire long subject...

Putting the last modified date in the footer will require VBA. Putting
in every file will best be done with an add-in.

 

[PropHead]

Yeah, I think it's you ;-)

I guess my post seemed insistent because of my frustration over not
finding what felt like a simple solution to a common problem. As far
as "ungracious" goes, I would be *extremely* gracious to anyone who can
help.

What annoyed me about the so-called solutions I'd seen posted, many by
Microsoft MVPs, was that they weren't "complete" in that a non VB /
Excel macro programmer could not easily implement them. I felt like
someone had asked what time it was, and the answers explained how to
build a clock (without ever getting to telling the poster what time it
was).

Please realize that the core of my frustration is with the Microsoft
product, and not with the great people on the 'net who try so hard to
help others.

Scott
(e-mail address removed)

Maybe it's me, but that seems an awfully insistent post, and somewhat
ungracious.


Bob

<disclaimer>
I know my question has been asked and answered before. But in nearly
two hours of web searching, none of the answers I found were
satisfactory. This should be trivial, yet it doesn't appear in a
Microsoft Excel FAQ, nor do the MS Excel MVPs seem to give a
straightforward answer for someone who's *not* familiar with Visual
Basic or the inner workings of Excel. So pardon my extreme
frustration!
</disclaimer>

I need to put the last modified date and time in the footer of all my
Excel files. I have some specific requirements:

[..snip..]

Can anyone tell me the solution I'm so desperately looking for? I've
seen similar queries all the way back to the mid 1990's asking a
similar question. Surely Microsoft could have provided something by
now, right?

Thanks *so much* in advance! I'm s-o-o-o-o-o-o frustrated!!!

 

[PropHead]

See below...

Obviously. However, the solution you want isn't obvious.

The solution isn't obvious, or the *need* isn't obvious? I can't
believe I'm in a tiny minority of people who want to have a modified
date in a page footer. Am I?

Suppose you opened a workbook, made a change and printed it out without
saving first. Should the "last modified" date be the current date?

Yes. I don't care about "last saved" - I need any hard copy to reflect
when the file was "last modified" whether it was saved or not.

What if you then closed the workbook without saving. What should the
"last modified" date be the next time you open the workbook?

Same thing (as above). Again, saving and modifying are two separate
things to me.

You don't want to have a cell in the spreadsheet holding the date, yet
you want the date printed on every page of every sheet. Does that mean
you want it printed in the header or footer? Or somewhere else?

My requirement to not use a cell in the spreadsheet is partly because I
don't want that cell getting in the way of something else. Yeah, I
know I could put it over at "Z9999" instead of "A1" but then I might
never find it again! (You should see my office!)

I want it printed in the footer, along with the full pathname to the
file.

What if the workbook has volatile functions (like TODAY(), NOW(), etc),
where the data automatically changes when the file is opened, even if
the user doesn't make an entry?

Good question. I think that in most (all?) cases, those things
"wouldn't count" because for me, "modified" really means "intentionally
modified by a human".

 

[PropHead]

Arrrgggghhhh! OK, I suppose I'll have to do the VBA thing. Can
someone tell me exactly what to do?

<stupid question alert>
What's the difference between a macro and an add-in?
</stupid question alert>

But why don't I have to create all sorts of VBA thingies to do "Page 1
of 7" in a footer? That's built-in; I can slap that in a footer in 3
seconds and a few clicks. Am what I'm asking for really that obscure?

Scott
(e-mail address removed)

 

[JE McGimpsey]

Suppose you opened a workbook, made a change and printed it out without
saving first. Should the "last modified" date be the current date?

Yes. I don't care about "last saved" - I need any hard copy to reflect
when the file was "last modified" whether it was saved or not.

What if you then closed the workbook without saving. What should the
"last modified" date be the next time you open the workbook?

Same thing (as above). Again, saving and modifying are two separate
things to me.[/QUOTE]

Yes, but you can't do the "same thing (as above)" without saving. If you
modify the file, you can print it out with the current date. But if you
don't save it, the last modified date will be whatever it was when the
file was last saved. So...


May 24 Open. Make change. Print:LMD=24 May 2005. Save. Close

May 25 Open. Make change. Print:LMD=25 May 2005. Close w/o Save

May 26 Open. Print:LMD=24 May 2005.

Is that what you want?


If that's OK, here's an add-in I just made which does that:

ftp://ftp.mcgimpsey.com/excel/dateinfooter.xla


Or you can make your own - here's the code I used.

Put this in the ThisWorkbook code module of a new workbook:

Dim clsDateInFooter As DateInFooter

Private Sub Workbook_Open()
Set clsDateInFooter = New DateInFooter
End Sub

insert a new class module and name it DateInFooter. Put this code into
that module:

Public WithEvents oApp As Application

Private Sub Class_Initialize()
Set oApp = Application
End Sub

Private Sub oApp_SheetChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
Dim prLastModified As DocumentProperty
With ActiveWorkbook.CustomDocumentProperties
On Error Resume Next
Set prLastModified = .Item("Last Modified")
On Error GoTo 0
If prLastModified Is Nothing Then
.Add _
Name:="Last Modified", _
LinkToContent:=False, _
Type:=msoPropertyTypeDate, _
Value:=Now
Else
prLastModified.Value = Now
End If
End With
End Sub

Private Sub oApp_WorkbookBeforePrint(ByVal Wb As Excel.Workbook, _
Cancel As Boolean)
Dim ws As Worksheet
On Error Resume Next
For Each ws In ActiveWindow.SelectedSheets
ws.PageSetup.LeftFooter = "Last Modified: " & _
ActiveWorkbook.CustomDocumentProperties( _
"Last Modified").Value
Next ws
On Error GoTo 0
End Sub

Save the workbook as an add-in, then install it using Tools/Add-ins.

Note that this doesn't reset the date if there's an Undo.

 

[PropHead]

Yes. I don't care about "last saved" - I need any hard copy to reflect
when the file was "last modified" whether it was saved or not.



Same thing (as above). Again, saving and modifying are two separate
things to me.

Yes, but you can't do the "same thing (as above)" without saving. If you
modify the file, you can print it out with the current date. But if you
don't save it, the last modified date will be whatever it was when the
file was last saved. So...


May 24 Open. Make change. Print:LMD=24 May 2005. Save. Close

May 25 Open. Make change. Print:LMD=25 May 2005. Close w/o Save

May 26 Open. Print:LMD=24 May 2005.

Is that what you want?[/QUOTE]

Yup! I realize it's therefore not technically "last modified" but
rather "last modified and saved", but that's what I want.

If that's OK, here's an add-in I just made which does that:

ftp://ftp.mcgimpsey.com/excel/dateinfooter.xla

BINGO! Mission accomplished!

I tried this, making changes and saving, making changes and not saving,
re-opening a test file at different times and checking the date in the
footer. The behavior is precisely what I've been looking for. And it
seems that because it's an add-in, it applies to new Excel files I open
as well.

THANK YOU SO MUCH, JOHN. I really appreciate your assistance.