File path in footer

[hoochy_1]

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.

 

[lenze]

Welcome to the board. This is a very common question. A quick search turnd up several threads, including this one

http://216.92.17.166/board/viewtopic.php?topic=25535&forum=2

 

[Dave Patton]

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]

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]

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]

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