Including saved path in header/footer

  • Thread starter Thread starter Hall
  • Start date Start date
H

Hall

I can have my file name be automatically included in the header/footer using
"&[File]".

How can I have the file's saved location (path) automatically included?
"&[Path]" didn't work.
 
Hi
the &[path] statement works only in later Excel versions (I think
starting in Excel 2002). Try the following:
Put the following macro in your workbook module:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In Me.Worksheets
With wkSht.PageSetup
.CenterFooter = Me.FullName
End With
Next wkSht
End Sub
 
Frank, thanks for the advice.

I think I need a little more spoonfeeding with this since I'm new to VBA.

Exactly where do I put this? I'd like my left footer to have path\filename.

And you're right, I have 2000.

Frank Kabel said:
Hi
the &[path] statement works only in later Excel versions (I think
starting in Excel 2002). Try the following:
Put the following macro in your workbook module:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In Me.Worksheets
With wkSht.PageSetup
.CenterFooter = Me.FullName
End With
Next wkSht
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany
I can have my file name be automatically included in the
header/footer using "&[File]".

How can I have the file's saved location (path) automatically
included? "&[Path]" didn't work.
Click to expand...
Click to expand...
 
Hi
this would enter the full name (path + filename). For more information
about event procesdures take a look at:
http://www.cpearson.com/excel/events.htm

In your case try the following:
- open your workbook
- hit ALT + F11 to open the VBA editor
- now locate 'ThisWorkbook' in the left explorer tree of your project
and double click on it
- paste the code I provided
- close the VBA editor
- save the workbook
- Open Printpreview to see/check the results


--
Regards
Frank Kabel
Frankfurt, Germany
Frank, thanks for the advice.

I think I need a little more spoonfeeding with this since I'm new to
VBA.

Exactly where do I put this? I'd like my left footer to have
path\filename.

And you're right, I have 2000.

Frank Kabel said:
Hi
the &[path] statement works only in later Excel versions (I think
starting in Excel 2002). Try the following:
Put the following macro in your workbook module:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In Me.Worksheets
With wkSht.PageSetup
.CenterFooter = Me.FullName
End With
Next wkSht
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany
I can have my file name be automatically included in the
header/footer using "&[File]".

How can I have the file's saved location (path) automatically
included? "&[Path]" didn't work.
Click to expand...
Click to expand...
Click to expand...
 
Check out also this Add-in
http://www.j-walk.com/ss/excel/files/addpath.htm


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




Frank Kabel said:
Hi
the &[path] statement works only in later Excel versions (I think
starting in Excel 2002). Try the following:
Put the following macro in your workbook module:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In Me.Worksheets
With wkSht.PageSetup
.CenterFooter = Me.FullName
End With
Next wkSht
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany
I can have my file name be automatically included in the
header/footer using "&[File]".

How can I have the file's saved location (path) automatically
included? "&[Path]" didn't work.
Click to expand...
Click to expand...
 
Has the file been saved yet. I guess the rest was already said, but it was
Excel 2002 that &[Path] was added -- per Jim Rech when Excel 2002 came out.

The &[Path] has been added to Excel 2002 headers and footers in VBA it is &Z

&[Path]&[File] inserts the path and file name. In VBA this is entered as &Z&F

If you have a version before Excel 2000 you might want to take a look at
my webpage
http://www.mvps.org/dmcritchie/excel/pathname.htm
--
 
Back
Top