file path on sheet

surfacesys · #1 (**permalink**) 01-05-05, 09:41

Is there a way to list the path of the current sheet in the footer or at the bottom of the page? ie: C:\MyDocuments\Company\Personnel\Employee.xls

Thank You,
Michael

shades · #2 (**permalink**) 01-05-05, 13:36

Of course, if you have XL2002 or XL2003 this is built in, so you don't need this code.

Otherwise:

Code:

Sub CustomFooter()
'   Inserts the file name and path into the page footer
'   for each sheet in the active workbook
    Dim sht as Worksheet
    For Each sht In ActiveWorkbook.Sheets
        sht.PageSetup.LeftFooter = ActiveWorkbook.FullName
    Next sht
End Sub

Change the placement with: RightFooter or CenterFooter

surfacesys · #3 (**permalink**) 01-05-05, 14:48

Thank you very much!

Is there a way to enter this in a formula on the spread sheet?

shades · #4 (**permalink**) 01-05-05, 16:27

=SUBSTITUTE(LEFT(CELL("FileName",A1),FIND("]",CELL("FileName",A1))-1),"[","")

==============

And if you want to play around:

FILENAME: =MID(CELL("FILENAME",A1),FIND("[",CELL("FILENAME",A1))+1,FIND("]",CELL("FILENAME",A1))-FIND("[",CELL("FILENAME",A1))-5)

SHEETNAME: =RIGHT(CELL("FILENAME",A1),LEN(CELL("FILENAME",A1) )-FIND("]",CELL("FILENAME",A1)))

shades · #5 (**permalink**) 01-05-05, 16:38

If you have the Excel add-in (free!) called Morefunc.xll, you can do this more easily. And it has many other useful functions that Excel does not:

morefunc

surfacesys · #6 (**permalink**) 01-06-05, 11:15

Thank You very, very much shades (=substitute) is exactly what I needed.

Michael

Internet Services

Web Development

Digital Marketing

Consumer Tech