How do I print sheet , with actual file save date in footer?

  • Thread starter Thread starter Guest
  • Start date Start date
Add this UDF and macro to your workbook.

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

=DocProps("last author")
or
=DocProps("last save time")
or
-DocProps("creation date")

Sub PathInFooter()
ActiveSheet.PageSetup.RightFooter = DocProps("last save time")
End Sub

NOTE: the code could be included in a Before_Print routine.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.RightFooter = DocProps("last save time")
End Sub


Gord Dibben MS Excel MVP

On Fri, 30 Jun 2006 08:28:01 -0700, irfy <[email protected]> wrote:
 
Gord Dibben said:
Add this UDF and macro to your workbook.

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

=DocProps("last author")
or
=DocProps("last save time")
or
-DocProps("creation date")

Sub PathInFooter()
ActiveSheet.PageSetup.RightFooter = DocProps("last save time")
End Sub

NOTE: the code could be included in a Before_Print routine.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.RightFooter = DocProps("last save time")
End Sub


Gord Dibben MS Excel MVP
Click to expand...
 
Very helpful tip thank you! For some reason having trouble getting the date
in the footer to auto refresh without rerunning the macro. Thank you.
 
It will refresh if you use the Before_Print code.

Otherwise you have to run the PathInFooter Sub each time you print.


Gord
 
Thank you! I also figured out that if you paste the UDF in "This Workbook"
instead of a "Module", then it auto refreshes. Thanks again. Great tip!
 
Thisworkbook is where the before_print code is entered.

The UDF and the macro should go into a general module.


Gord
 
Back
Top