Full Path Name In Footer

Home/Functions, VBA – Macros/ Full Path Name In Footer

Full Path name in Footer

How can I put the full path name of my file in the footer without typing it?

By Neale Blackwood

Excel XP and 2003 allow you to easily insert the full path in your footer using the Page Setup, but unfortunately earlier versions don’t. In Excel 97 and 2000 you can use a macro to add the full path to the current worksheet.

To automatically insert the full path in the left footer, follow these instructions (note: this macro will overwrite whatever is in the left footer).

  1. Right click the Excel symbol to the left of the File menu.
  2. Select View Code.
  3. In the left-hand drop down box (below the toolbar) select Workbook.
  4. In the right-hand drop down box select BeforePrint

(you may have to scroll up to see it).

Excel should have automatically inserted the first and last commands of the BeforePrint event macro. You need to insert a command and make it look like this:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter= Active Workbook.FullName
End Sub

Excel may have also included a Workbook_Open macro. You can delete the two lines associated with it. This macro will run before you print or use print preview. Hence you can test it without having to use up paper by simply using print preview.

These event macros are powerful and useful. You can set up macros to run when you open a file or when you close a file, as well as the other events included in the right-hand drop down box.

Be warned that these macros can slow down operations within the file. Also be careful in their use as they can return error messages in certain circumstances. If you have used macros in the past then these event macros may allow you to extend your developmental possibilities.


Extras

This technique can allow you to insert the contents of a cell into your Header or Footer as well. If you name the cell it makes it easier to do.
If you name a cell FooterText, then this code will insert it in the Footer each time it prints.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter= [FooterText].Value
End Sub

2017-03-08T03:53:58+00:00 Functions, VBA – Macros|