Pathname in headings, footers, and cells

Location:   http://www.mvps.org/dmcritchie/excel/pathname.htm
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

This page contains some VBA macros.  If you need assistance to install a or use a macro please refer to instructions on my  my Getting Started with Macros page, or for more complicated situtations to my Formula  page.

Filename in a Cell (#cell)

HELP --> Cell worksheet function

Cell display
=(formula used)

=CELL("filename",A1)       [doesn't make any difference what cell, as long as it is in the worksheet of interest]

Even though the syntax allows omitting a cellname reference, you must include it; otherwise, for instance, if you select multiple sheets, all of the grouped sheets will get the information of the activesheet or as updated during a recalculation instead of the sheet where the formula resides.

The crippled form =cell("filename")  and other CELL Worksheet Function without a cell reference such as =cell("address") will use the active cell found on the active sheet as the cell reference when entered or recalculated later, which can be useful if you want to see the sheetname and cell address that were last updated, though this is not reliable because a recalculation will change the value to the activesheet or the first sheet in a grouping at the time of recalculation.  A recalculation occurs when the workbook is opened.  See HELP, Cell worksheet function; for more information. 

To see for yourself that the crippled form =cell("filename") is not the current sheet:  Enter your formula on two different sheets, on the second optionally use Ctrl+Alt+F9 to do a full recalculation of everything then return to the first sheet and look at your use.  If you have multiple workbooks open you could even be referring to another workbook, which you can also test yourself.  To be applicable to the current page you must use something like  =cell("filename",A1)

« The CELL formulas with "filename" will not work until the file has been saved (#VALUE! error).

If you rename sheet you will not see result until forced with a full recalculation Ctrl+Alt+F9. Reopening of workbook forces full recalculation.



D:\driveM\excel\TAXES\1996FEDT.XLS [Sheet1]
=SUBSTITUTE(SUBSTITUTE(CELL("filename",A1),"[",""),"]"," [") & "]"
Formula will produce incorrect result if the sheetname contains "[" or "]" characters -- Harlan Grove, fortunately PC's (not Macs) do not allow the following characters in a filename: < > ? [ ] : | or *

d:\drivem\excel\taxes\1996fedt.xls [Sheet1]
=SUBSTITUTE(LEFT(LOWER(CELL("filename",A1)),FIND("]",CELL("filename",A1))-1),"[","") & " [" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,28) & "]"


« The CELL formulas with "filename" will not work until the file has been saved (#VALUE! error).

Function SheetName() As String
SheetName = Application.Caller.Parent.Name 'in XL97
End Function

INFO (#info)

directory, memavail, memused, numfile, origin, osversion, recalc, release, system, totmem -- for more information see Slow Response(#memory)


MsgBox Application.StartupPath -- location of XLSTART, i.e. for personal.xls

Code in VBA   (#vba)

Description VBA codeExample
Full Name:Application.ActiveWorkbook.FullName D:\TestFolder\test.xls
Path:Application.ActiveWorkbook.Path C:\MSOffice\Excel
Sheetname: application.activesheet.nameSheet1

Coding Example using a VBA macro:  Leo Heuser =fname("filename")

The following is from a Nick Manton (1998-07-18) posting it applies to XL97 and above macro usage.  Two additional items added from postings D.McRitchie & C.Pearson (1999-12-17)

Application.Caller.Address                    'Cell address
Application.Caller.Parent.Name                'Worksheet Name      i.e. BigSheet
Application.Caller.Parent.Codename            'Worksheet codename  i.e. Sheet3
Application.Caller.Parent.Index               'Worksheet index number  i.e.   1 as in Worksheets(1)
Application.Caller.Parent.Parent.Name         'Workbook Name
Application.Caller.Parent.Parent.Parent.Name  'Application Name

Changes in Excel XP   (#xp)

Since most of this page is written because of shortcomings in Excel, it should be noted before continuing  that Excel XP supports the full path and filename, so you no longer need a macro for most of the  things that appear later unless you want to format the date yourself for instance.  (Nick Hodge 2002-02-27)    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     

Extract filename from full filename in a listing (#extract)

To extract the filename from the full pathname, you would need VBA.  From a posting by Harald Staff (2001-06-25). 

This works in Excel 2000 only (uses Reverse String Search):
  Function NameOnly(FullPath As String) As String
    NameOnly = Mid(FullPath, InStrRev(FullPath, "\") + 1)
  End Function

This works in previous versions too:
  Function NameOnly97(FullPath As String) As String
  NameOnly97 = FullPath
  Do Until InStr(NameOnly97, "\") = 0
    NameOnly97 = Mid(NameOnly97, 2)
  End Function

  Sub test()
    MsgBox NameOnly("c:\windows\explorer.exe")
    MsgBox NameOnly97("c:\windows\explorer.exe")
  End Sub

How do I get Excel to display the filename and path in the footer?

Excel 2002 has added &[Path] for custom headers and footers, but for earlier versions of Excel you still must use a macro.

When using macros for headers and footers is easy to forget about changing font attributes such as pointsize.  I like to use a smaller font in my footers, except possibly for page numbers. Actually I prefer to use the Excel facilities for page numbers in footers, but you could include that also in your macro as shown below.
  F1 (VBE HELP) --> answer wizard -> Formatting Codes for Headers and Footers

Activesheet.PageSetup.LeftFooter = "&8" & _
    LCase(ActiveWorkbook.FullName) & " &A "

Activesheet.PageSetup.CenterFooter = "Page  &P  of  &N"   'like to use 2 spaces between

Following are three examples, install macro of your choice in a Module sheet.

Sub PrintWithFileNameInFooter()
  With ActiveSheet
    .PageSetup.LeftFooter = Application.ActiveWorkbook.FullName  
  End With
End Sub

Sub insertFooter()
ActiveSheet.PageSetup.LeftFooter = = Application.ActiveWorkbook.FullName  
End Sub
The following is what I use, it prints the full pathname and sheetname less obtrusively with a smaller font.  There are two spaces between each word/number on the page number.  The left footer is placed regardless of current content.  The right footer is placed only if there is no current right footer.  The use of WITH to supply the prefixed wording is considered much more efficient in macros; and so the .LeftFooter is actually ActiveSheet.PageSetup.LeftFooter and the break character (underscore) is actually a blank and underscore to indicate continuation of a single line.  I choose not to have it run automatically which I feel might inadvertently wipe out something important in someone else's sheet.
Sub PutFileNameInFooter()
  'Documented in  http://www.mvps.org/dmcritchie/excel/pathname.htm
  'alternative -- lowercase for pathname  Sheetname as is within square brackets
  'also using an 8 point font,  lettercase of Sheetname is unchanged in this
  'example because you have control and can change it within the workbookbook
  'Use of WITH provide a prefix for names that begin with a period
  With ActiveSheet.PageSetup
    .LeftFooter = "&8" & _
       LCase(Application.ActiveWorkbook.FullName) & "  &A "
    If .RightFooter = "" Then .RightFooter = "Page  &P  of  &N"
  End With
End Sub
Above are three sample macros.  I would suggest removing the PrintOut since it also prints out the spreadsheet and I like to make further changes in the footer.

One thing to be said about including .PRINTOUT is that if you move your workbook or worksheet there will be an automatic update of the footer.  Hopefully, someday, Microsoft will fix up the headers and footers in Excel for more flexibility, exact margins, pathname, color logos, subtotals, and continued subheadings.

Left/Center/Right Footer/Header   (#left)

You may use LeftFooter, CenterFooter, RightFooter, LeftHeader, CenterHeader, or RightHeader where LeftFooter has been used above.

See VBE Help --> Index --> Find --> Formatting Codes for Headers and Footers

Generate Custom footers for all sheets in workbook (#allfooters)

This macro will place custom footers on all sheets in the workbook. 
    Sub allfooters()
      Dim s As Worksheet
      Application.ScreenUpdating = False
      For Each s In ActiveWorkbook.Worksheets
       With s.PageSetup
        .LeftFooter = "&8" & LCase(Application.ActiveWorkbook.FullName) _
            & "  [&A]"
        .CenterFooter = "Page  &P  of  &N"
        .RightFooter = "&8 &D  &T"
       End With
      Next s
     Application.ScreenUpdating = True
  End Sub

Inserting cell data into header/footer (#celldata)

Activesheet.PageSetup.CenterFooter=Range ("A1").value

The following would only change the first sheet in the workbook:
Sheets(1).PageSetup.CenterFooter=Sheets(1).Range ("A1").FormulaR1C1

Including a Date in a footer (#date)

If you don't like the format in Excel for footers &[Date] &Time] which appears like 3/1/98 11:19 PM. The default for NOW() is 3/1/98 23:19

And a comment about US Dates and VBA (#US_dates)

Using the &[Date] &[Time] results in 3/2/98 12:08 AM.   Now() if unformatted yields 3/2/98 0:08 -- Defaults shown apply to U.S.A.

If you want don't want to see an earlier date like 01-Jan-1998 in your footer you want to make sure you are invoking the VBA at print time.

Using Workbook_BeforePrint to automate printing of heading/footing (#beforeprint)

Bill Manville (Oxford England) offers a solution for Excel 97 users to automatically include changing the footer when printing is initiated using a Workbook_BeforePrint, event procedure (Modified below to show date). The old solution used a customized button and attached macro to invoke both the code and the print. (#thisworkbook)
You would install in ThisWorkbook instead of a standard code module.  ThisWorkbook is found at the end of all of your object modules and has this specific name.  To find use (Alt+F11, Ctrl+R) look at the project, look at "Microsoft Excel Objects" (as opposed to "Modules" and "References"), expand objects and look for ThisWorkbook at the end.

*   The quickest way to get to “This Workbook”  is to right-click on the Excel icon at the left side of the menu bar, then use “View Code”.

   Private Sub Workbook_BeforePrint(Cancel As Boolean)
     ActiveSheet.PageSetup.LeftFooter = Application.ActiveWorkbook.FullName & "  &A"  
     ActiveSheet.PageSetup.RightFooter = Format(now(),"dd-mmm-yyyy") 'UK dates
   End Sub
An Improvement to modify the footers for Grouped Worksheets.
   Private Sub Workbook_BeforePrint(Cancel As Boolean)
   Dim wkSht As Worksheet
    For Each wkSht In ActiveWindow.SelectedSheets
      wkSht.PageSetup.LeftFooter = _
        "&8" & LCase(Application.ActiveWorkbook.FullName) & "  &A "
    Next wkSht
   End Sub

Including the above and additional information: (#modify to suit your preferences)

ActiveWorkbook.Fullname does not work in Print Preview (Excel 2000), &F can be use to get the bookname (filename) instead.  With Excel 2002 you can use &Z for the full pathname.

Option Explicit
  Private Sub Workbook_BeforePrint(Cancel As Boolean)
   'Documented in  http://www.mvps.org/dmcritchie/excel/pathname.htm
   'Install in ThisWorkbook in the Project Library of your workbook
   'Alt+F11 (switch to VBE), Ctrl+R (object explorer),
   'find/install in "ThisWorkbook" under "Microsoft Excel Objects"
   Dim wkSht As Worksheet
   For Each wkSht In ActiveWindow.SelectedSheets
     With wkSht.PageSetup
        on error resume next  'for Excel 2000 print preview failure  
        .LeftFooter = "&8" & LCase("&F ") & " &A "  
        .LeftFooter = "&8" & LCase(Application.ActiveWorkbook.FullName) _
              & "  &A " 
        .CenterFooter = "Page  &P  of  &N"
      ' .RightFooter = "&8 &D &T"
        .RightFooter = "&8 " & _
           Application.UserName & ", " _
            & Format(Now(), "yyyy-mm-dd") & "  &T"
     End With
   Next wkSht
  End Sub

Something like   .RightFooter = "&8 &D &T"  would print date (&D) with the user's own Regional settings.  The prefix of &8 reduces the fontsize to 8 points, to make sure it doesn't blend in with data.  The use of &A includes the sheetname.  and will change even if you no longer had the macro, unlike the pathname.  You also have .LeftHeader, .CenterHeader, and .RightHeader available.  a format of  "mmmm dd, yyyy"  would print long dates (US format).

Use   &[Page]+X    to adjust page numbers
With Excel 2000 the above no longer works, instead use Page Setup, Page tab, first page number: change the word Auto to the number for your first page.  I presume this provides better reuse of your customized headers/footers.

Excel 2002 does provides &[Path] for use in headers and footers.

Print Area must be limited for certain sheets (#printarea)

The situation is that there are some sheets with formulas in all of rows 2 through 3000, but the rows that should be included should only extend down to last entry in Column A.  The PrintArea limits the print area it does not extend it so all 256 columns (columns.count) can be included.

If you think this situation actually applies, you should consider why there are formulas on rows that down't need them.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
  With ActiveSheet
    on Error Resume Next   
    .PageSetup.LeftFooter =  " &F &A"  'works in print preview
    .PageSetup.LeftFooter =  ThisWorkbook.FullName  & "  &A"
    .PageSetup.RightFooter = "&8 &D &T"
    If LCase(ActiveSheet.Name) = "sheet28" Then
      .PageSetup.PrintArea = .Cells(1, 1).Resize( _
        .Range("A" & Rows.Count).End(xlUp).Row, Columns.count).Address
    End If
 End With

And another comment about Control Panel (#settings)

Well, you could use VBA to correct the problem if you want, but there is a much easier way if you always want a particular date format.  Go into the Windows Control Panel and open up Regional Settings, go to date, and modify the Short date style. Excel will now always print the date in the footer to that style.  If you're using Win 3.1 still, I'm sure there's a way to do it, I just can't remember at the moment.

A non macro solution for changing date and/or time in footer

You can change the formats used throughout your system by changing your Control settings.
Desktop Start --  Settings --  Control Panel --  Regional Settings --  Date

I reset my own from mm/dd/yy to mm/dd/yyyy -- in the Short Date area

Using a Custom Button to activate a footer (#toolbar)

The following macro will print in 8 point with the full pathname in lowercase, The sheetname will print exactly the same as you named it in your workbook.  This is the one that I prefer.
Sub PutFileNameInFooter()
  With ActiveSheet
    .PageSetup.LeftFooter = "&8" & LCase(Application.ActiveWorkbook.FullName) & "  &A %quot; 
  End With
End Sub
Include the desired code in a Module sheet, create one if you don't have one.
Insert --> Macro --> Module

To invoke Tools --> Macro --> click on macro line --> Run

To save time you may install a custom button, to invoke the macro.

Install the Custom Button   (#button)

For Excel 7.0a you may add a custom button on your toolbar
      View --> Toolbars --> [customize] --> custom -->
Drag a button to the toolbar --> assign an existing macro

I have assigned one custom button to include a filename with full pathname in the left footer.  Since the toolbars are in effect for all my Excel usage the macro is installed as follows:   personal.xls!PutFileNameInFooter

For this macro I choose the yellow smilely face.  Additional information on creating a custom button is available.

Excel 97 users have an alternative (#alternative)

See topic Using Workbook_BeforePrint to automate printing of heading/footing (#beforeprint)

Bill Manville (Oxford England) offers a solution to automatically include changing the footer when printing is initiated using a Workbook_BeforePrint, event procedure e.g.

   Private Sub Workbook_BeforePrint(Cancel As Boolean)
     ActiveSheet.PageSetup.LeftFooter = Application.ActiveWorkbook.FullName
   End Sub
A later XL97 version also from Bill Manville on Jul 31, 1998 lists
Private Sub Workbook_BeforePrint(Cancel As Boolean)
  Dim oSheet As Object
  For Each oSheet In ThisWorkbook.Windows(1).SelectedSheets
    oSheet.PageSetup.LeftHeader = ThisWorkbook.FullName
End Sub
additional lines you could add for a specially formatted date
    ActiveSheet.PageSetup.RightFooter = Format(now(),"dd-mmm-yy")
last saved date
   ActiveSheet.PageSetup.RightHeader = "Saved date " _
      & FileDateTime(ActiveWorkbook.FullName)
In earlier versions you had to have a macro that sets up the footer and then does the printout.  You would use this in place of File / Print:
   Sub SetFooterAndPrint()
     ActiveSheet.PageSetup.LeftFooter = Application.ActiveWorkbook.FullName
   End Sub

How to put the full pathname on the title bar (#titlebar)

Using Workbook_WindowActivate in  “ThisWorkbook”(posted 2005-03-24), works better than an Auto_Open version (GrahamTooley, 1998-03-25) because you can have several windows open at the same time, so the Auto_Open version would be pointing to the wrong place.  Not a problem with the following Workbook_WindowActivate.
   Private Sub Workbook_WindowActivate(ByVal Wn As Window)
     ActiveWindow.Caption = ActiveWorkbook.FullName
   End Sub

But I don't really need additional overhead the following will tell me where I am if I really want to know, as would file, properties.

   Sub WhereAmI()
       MsgBox ActiveWorkbook.FullName
   End Sub

Display Sheet Statistics from Tom Ogilvy contains a lot more sheet information and could substituted for the above.

A special note on Auto_Open (from a note of Stephen Bullen in Excel-L).  If you receive a spreadsheet of dubious origin that might have macro viruses open the spreadsheet with the Shift key engaged to override Auto_Open, continue to hold it down while selecting sheet or module this will ignore them for the time being while you examine content.

Removing default Header and/or Footer (#remove)

Q: How to change the default header and/or footer to 'none'?
A: Create a workbook in your Excel startup directory called Book.xlt for a template for all new workbooks (those created without using another template). Create one called Sheet.xlt containing a single worksheet for a default template for all new worksheets.   Chip Pearson

Problems   (#problems)

Related items in a Cell -- Date and Time (#relatedcell)

=NOW()     places the current date and time current date and time when sheet or cell is recalculated
12/16/97 23:06

Ctrl+: (semicolon) places today's date permanently in a cell.  Unchanged by recalculations.

Ctrl+Shift+: (colon) places current time permanently in a cel.  Likewise, unchanged by recalculations.

Method to place the last saved date into a cell using the Workbook_BeforeSave event, suggested by Chip Pearson, Apr8, 1998.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
    Sheets("Sheet1").Range("A1").Value = Now()
End Sub

Some Information for WORD Users (#wordinfo)

For Word users there is a parameter that may not be so obvious but by pulling down the (?) to the appropriate line you will see that you can include a \p parameter to obtain the full pathname.

      Insert --> Field --> Document Information --> filename
modify the insert field to appear as below:
      filename \p

Related information -- Sheets (#sheets)

List of worksheet names   also see Build Table of Contents (ref
Sub Macro12()
  [a1] = "Sheetname..."
For q = 1 To Worksheets.Count
  [a1].Offset(q, 0).Value = "'" & Worksheets(q).Name
Next q
End Sub
Also see Get All A1 Cells in buildtoc(#GetallA1Cells).

Sorting worksheets alphabetically (sorting the tab names)  also see Build Table of Contents (ref SortAllSheets)

Sub SortSheets()
  'Bill Manville  1997/09/19 -- http://groups.google.com/groups?oi=djq&ic=1&selm=an_AN=273647350
  ' also see Q105069 XL: Macros to Sort Tabs in the Active Workbook
  Dim I As Long, J As Long
  For I = 1 To Sheets.Count
    For J = 1 To I - 1
      If UCase(Sheets(I).Name) < UCase(Sheets(J).Name) Then
        Sheets(I).Move before:=Sheets(J)
        Exit For
      End If
    Next J
  Next I
End Sub

Save file by name in cell A1 (#renamefile)

   ActiveWorkbook.SaveAs FileName:=ActiveCell.Value   'active cell, or
   ActiveWorkbook.SaveAs FileName:=Range("A1").value  'value in A1

Save file using a dialog box   (#savefile)

expression.GetSaveAsFilename(InitialFilename, FileFilter, FilterIndex, Title, ButtonText)

The following example was posted by Harold Staff 2001-09-25

Sub SaveMe()
   Dim Savepath As Variant
   Savepath = Application.GetSaveAsFilename("C:\Code\A.xls", _
     "Beckys Workbooks (*.xls), *.xls", , "Pick a nice place")
   If Savepath = False Then Exit Sub 'cancelled!
   ActiveWorkbook.SaveAs Savepath
End Sub
John Walkenbach created a GetDirectory function, as might be used to select a directory for backups in his Tip 29, Selecting a Directory.

Making a directory, Patrick Molloy, programming, 2001-09-26

    Dim sPath As String
    sPath = "C:\Testing1"
    MkDir sPath
    ThisWorkbook.SaveAs sPath & "\" & "Myfile.xls"

Repeating a RANGE across the top for Column Headings

Sub Macro43()
    'Multiple paste of header information   D.McRitchie 2001-03-01 programming
    Dim iMax As Long, i As Long  'Long would work now anyway
    iMax = Range("AX1").Column    ' Ax1:  COLUMN()    shows 50
    'Range("A1").Offset(0, 9).Paste
    For i = 9 To iMax Step 8
      Range("A1").Offset(0, i).PasteSpecial
    Next i
End Sub
This page contains some VBA macros.  If you need assistance to install or to use a macro please refer refer to my  GetFormula  page.

Since this page is primarily on Headers and Footings you might also want to look at
    HELP (in VBE) --> headers --> Formatting Codes for Headers and Footers

Related Information on this Site (#tsite)

Related Information in newsgroups (Google Usenet Archives) (#newsgroups)

Related Information on Other Sites (#osites)

Related Information, Tutorials (#tutorials)

Microsoft Knowledge Data Base (MS KB)

[Return to Top]

This page was introduced with opening of this site on January 1, 1998. 

[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved