Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modified Date | Excel Discussion (Misc queries) | |||
How do I display the 'Date Modified' in an Excel worksheet header | Excel Discussion (Misc queries) | |||
insert the date the file was last modified | Excel Discussion (Misc queries) | |||
Time Date Last Modified | Excel Discussion (Misc queries) | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |