McGimpsey & Associates: macoffice : Print Path for all files

This site will look better if you upgrade to a browser that supports web standards.

Print path in footer for all files

On a previous page, I demonstrated how to automatically print the full path and filename in a header or footer. Here I demonstrate how to do so for both new files and all old files.

New Files

To have all new files print their path and filename in the header or footer, put the Workbook_BeforePrint() macro in a newly created workbook. You can format that workbook the way you want, with the number of sheets, page setup, etc. Save that workbook as a template named, for Macs, "Workbook" (no quotes, no extension), in the Microsoft Office N:Office:Startup:Excel folder. For Windows, save the workbook as a template named "Book.xlt" (no quotes) i the XLStart directory (for which the path varies, depending on version).

All new workbooks (that aren't from another template) will then contain that macro and will update their path and filename on Print or Print Preview.

All Workbooks

You can print the path and filename for all workbooks using a class event. Create a new workbook (see quick link for an add-in implementing this approach). In the ThisWorkbook module, enter:

    Option Explicit
    Dim clsPrintFooter As New PrintFooterClass

    Private Sub Workbook_Open()
        Set clsPrintFooter.MyPrintApp = Application
    End Sub

Choose Insert/Class Module, and name the module PrintFooterClass using the Properties Window:

Enter this in the PrintFooterClass module:

    Option Explicit
    Public WithEvents MyPrintApp As Application

    Private Sub MyPrintApp_WorkbookBeforePrint(ByVal Wb As Excel.Workbook, Cancel As Boolean)
        Dim wsSheet As Worksheet
        For Each wsSheet In ActiveWindow.SelectedSheets
            wsSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName
        Next wsSheet
    End Sub

Save this workbook in the Start:Excel folder (Mac) or the XLStart folder (Win), then close and reopen XL. From now on when you print a file (or choose Print Preview), the footer will update. If you save it as an add-in (or download printfooter.xla - .zip or .sit file) the file won't be visible while you're using XL.

Modifications

Rather than LeftFooter, you can substitute CenterFooter, RightFooter, LeftHeader, CenterHeader, or RightHeader. The headers and footers are limited to 255 characters so very long path and filenames may require tweaking.

This page last updated Tuesday, 10 February 2004

McGimpsey & Associates

Home

MacOffice

Print path in footer for all files

New Files

All Workbooks

Modifications

Quick Links