Tip of the week from 08 Sept 2001

Posted by Dan Weil on October 11, 2001 4:37 AM

Dear Mr. Excel and colleaques. I send again this message, since I did get no followups for the former. In Tip of the week from 08 Sept 2001, in reply to Donna, you gave instruction for writing "BeforePrint" event handler which will add full path of the file in the header (or footer. I tried it and it worked nice, thank you. I inserted this event handler in my Personal.xls workbbook, hoping that this macro will be available and will function for all opened other workbooks, but it did not.
Is there a way to apply the "BeforePrint" event handler for all opened workbooks?

Posted by Dank on October 11, 2001 4:59 AM

It's not as straightforward as just using the tip in your Personal.xls file. Use these steps to get the path in every worksheet printout.

1.In the VB Editor expand Personal.xls so that you can see all of its components.

2.Click Insert, Class Module and enter this code:-
Public WithEvents XLApp As Application

Private Sub XLApp_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
With Wb.ActiveSheet.PageSetup
.LeftFooter = Wb.Path & "\" & Wb.Name
.RightFooter = Format(Now, "dd/mm/yy hh:mm")
End With
End Sub

The LeftFooter and RightFooter parts can be changed to used your needs.

3.Still in Personal.xls add a standard module (or use an existing one and enter this procedure:

Dim x As New cCreateApp

Sub SetMe()
Set x.XLApp = Application
End Sub

4. Finally, double click the ThisWorkbook icon in the project explorer and enter this:-

Private Sub Workbook_Open()
SetMe
End Sub

Now save personal.xls, close Excel and re-open. You'll now find the full path in the left hand and date and time in the right hand side of every page you print out.

Regards,
Daniel.

Posted by Dank on October 11, 2001 5:01 AM

Important point re above

You must rename the class module to cCreateApp.

Regards,
Dan.