File path in footer

hoochy_1

New Member
Joined
Oct 20, 2002
Messages
3
We want our Excel files to have the same type of footers as our Word files -- they contain the path of the file from out network and the file name. We do not want to type this information into the footer because it will not update when the file name changes or the file location changes.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,960
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
On 2002-10-30 16:35, hoochy_1 wrote:
We want our Excel files to have the same type of footers as our Word files -- they contain the path of the file from out network and the file name. We do not want to type this information into the footer because it will not update when the file name changes or the file location changes.


You could try a sub in ThisWorkbook
like the following

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'
' This Sub is in ThisWorkBook
'
Dim sht As Worksheet
Dim sDate As String
For Each sht In ThisWorkbook.Sheets
sDate = Format((Now), "ddd d mmm yyyy hh:mm ")
sht.PageSetup.LeftFooter = "&8" & "" & ActiveWorkbook.FullName _
& "|" & "&A" & " " & sDate & " " & "Your Name"
Next sht
End Sub


If several people may use the spreadsheet, delete the "Your Name" part.
This message was edited by Dave patton on 2002-10-30 16:43
 

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
Use this in your code:

With ActiveSheet.PageSetup
.LeftFooter = "&""Arial,Regular""&8" &
ActiveWorkbook.FullName
End With

This places the full path and file name in the left footer and makes the font Arial 8 point.

You can do this as a regular macro and run it each time you open and name a new workbook or save it to a different path, or you can use it in a Private macro, either Before_Close or Before_Print, which will automatically change the footer every time you save the file or before you print it.
 

BigC

Well-known Member
Joined
Aug 4, 2002
Messages
851
IMHO forget the VBA solutions and putting this info in your footer as the info is static and you have to run the macro each time to update it.
Instead enter the following formula in cell A1 and include that cell in your print range (and rows to print at top)
=cell("filename",A1)
You can then have all you spreadsheet identification info (eg. pathfile, Organisation name, spreadsheet purpose, author, etc) at the top left of your spreadsheet where most spreadsheets start building their models.

HTH
 

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
Hi

You might want to look at ASAP-utilities.com ...I think they they have a function that this does this....Version 2002 Excel has this already as an option in footer and header....

good luck

pll
This message was edited by plettieri on 2002-10-30 21:23
 

Forum statistics

Threads
1,143,640
Messages
5,719,984
Members
422,256
Latest member
downeybm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top