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

[Gord Dibben]

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:

 

[autsumi]

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

 

[autsumi]

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.

 

[Gord Dibben]

It will refresh if you use the Before_Print code.

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


Gord

 

[autsumi]

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!

 

[Gord Dibben]

Thisworkbook is where the before_print code is entered.

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


Gord