Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 6th 05, 11:39 PM
John_Ostar
 
Posts: n/a
Default How do I add the file last modified date into an Excel header?

It is easy to show the date a document was last modified in MS-Word by
inserting the field: savedate into the footer. A very common requirement. But
there does not appear to be any way, or at least no easy way to do this in
Excel. I CAN'T BELIEVE IT! They still haven't added this. Excel forces you to
put in the current date. YUK! Why would I want that in my print out? I want
to know when it was last modified, not when I printed it. ARG!!!

  #2   Report Post  
Old October 6th 05, 11:45 PM
Gary''s Student
 
Posts: n/a
Default

From an earlier posting

Hi
use the following UDF:

Function DocProps(prop As String)
application.volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

and enter in a cell
=DOCPROPS("last save time")
(format cell as date)

--
Regards
Frank Kabel
Frankfurt, Germany

--
Gary''s Student


"John_Ostar" wrote:

It is easy to show the date a document was last modified in MS-Word by
inserting the field: savedate into the footer. A very common requirement. But
there does not appear to be any way, or at least no easy way to do this in
Excel. I CAN'T BELIEVE IT! They still haven't added this. Excel forces you to
put in the current date. YUK! Why would I want that in my print out? I want
to know when it was last modified, not when I printed it. ARG!!!

  #3   Report Post  
Old October 10th 05, 10:11 PM
John_Ostar
 
Posts: n/a
Default

Thanks Gary's Student. I ended up writting my own. What do you think?

JohnO

' Author: John Ostar
' Last Modified: 10/10/2005
'Description:
'This VBA code will put the Workbook's Last Modified Date and Time into the
' printed page footer of all sheets, including charts, of the open
workbook.
' It is needed because MS-Excel does not have a field similar to
MS-Word's
' LastSaveDate that can just be easily added to a custom footer.
' This code will run everytime the workbook is saved.
'Installation: Open the desired Workbook. Click Tools-Macro-Visual Basic
Editor.
' In Project Explorer, double click on ThisWorkbook and paste this code
into it.
' Close the Visual Basic Editor and Save the Workbook.
'Note: It will be necessary to set Tools-Macro-Security to Low or Medium

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Get the active workbook's last modified date property.
dtMyLastSaveDate = ActiveWorkbook.BuiltinDocumentProperties("Last Save
Time")

'Put value into center footer of every sheet in the workbook
For Each wsheet In Sheets
'the default date format is m/d/yy h:m:s AM/PM
'alternate format is m/d/yy h:mm am/pm
dtMyLastSaveDate = Format(dtMyLastSaveDate, "m/d/yy h:m am/pm")
wsheet.PageSetup.CenterFooter = "Last Modified: " & dtMyLastSaveDate
Next wsheet
End Sub


"Gary''s Student" wrote:

From an earlier posting

Hi
use the following UDF:

Function DocProps(prop As String)
application.volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

and enter in a cell
=DOCPROPS("last save time")
(format cell as date)

--
Regards
Frank Kabel
Frankfurt, Germany

--
Gary''s Student


"John_Ostar" wrote:

It is easy to show the date a document was last modified in MS-Word by
inserting the field: savedate into the footer. A very common requirement. But
there does not appear to be any way, or at least no easy way to do this in
Excel. I CAN'T BELIEVE IT! They still haven't added this. Excel forces you to
put in the current date. YUK! Why would I want that in my print out? I want
to know when it was last modified, not when I printed it. ARG!!!

  #4   Report Post  
Old October 10th 05, 10:20 PM
John_Ostar
 
Posts: n/a
Default

Opps. One error in my comments left over from an earlier attempt.
'This code will run everytime the workbook is PRINTED (not saved).
Sorry.

"John_Ostar" wrote:

Thanks Gary's Student. I ended up writting my own. What do you think?

JohnO

' Author: John Ostar
' Last Modified: 10/10/2005
'Description:
'This VBA code will put the Workbook's Last Modified Date and Time into the
' printed page footer of all sheets, including charts, of the open
workbook.
' It is needed because MS-Excel does not have a field similar to
MS-Word's
' LastSaveDate that can just be easily added to a custom footer.
' This code will run everytime the workbook is saved.
'Installation: Open the desired Workbook. Click Tools-Macro-Visual Basic
Editor.
' In Project Explorer, double click on ThisWorkbook and paste this code
into it.
' Close the Visual Basic Editor and Save the Workbook.
'Note: It will be necessary to set Tools-Macro-Security to Low or Medium

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Get the active workbook's last modified date property.
dtMyLastSaveDate = ActiveWorkbook.BuiltinDocumentProperties("Last Save
Time")

'Put value into center footer of every sheet in the workbook
For Each wsheet In Sheets
'the default date format is m/d/yy h:m:s AM/PM
'alternate format is m/d/yy h:mm am/pm
dtMyLastSaveDate = Format(dtMyLastSaveDate, "m/d/yy h:m am/pm")
wsheet.PageSetup.CenterFooter = "Last Modified: " & dtMyLastSaveDate
Next wsheet
End Sub


"Gary''s Student" wrote:

From an earlier posting

Hi
use the following UDF:

Function DocProps(prop As String)
application.volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

and enter in a cell
=DOCPROPS("last save time")
(format cell as date)

--
Regards
Frank Kabel
Frankfurt, Germany

--
Gary''s Student


"John_Ostar" wrote:

It is easy to show the date a document was last modified in MS-Word by
inserting the field: savedate into the footer. A very common requirement. But
there does not appear to be any way, or at least no easy way to do this in
Excel. I CAN'T BELIEVE IT! They still haven't added this. Excel forces you to
put in the current date. YUK! Why would I want that in my print out? I want
to know when it was last modified, not when I printed it. ARG!!!



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Modified Date D Excel Discussion (Misc queries) 12 February 12th 07 01:39 PM
How do I display the 'Date Modified' in an Excel worksheet header DC Solah Excel Discussion (Misc queries) 3 July 10th 06 06:03 PM
insert the date the file was last modified Hoff Excel Discussion (Misc queries) 8 November 21st 05 01:31 PM
Time Date Last Modified LDL Excel Discussion (Misc queries) 3 May 3rd 05 10:20 PM
Read Text File into Excel Using VBA Willie T Excel Discussion (Misc queries) 13 January 8th 05 12:37 AM


All times are GMT +1. The time now is 05:14 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017