Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Adding a File Path and Filename.

Adding a File Path and Filename

by Allen Wyatt
(last updated September 7, 2013)

14

Margo wants to insert a file path and filename in an Excel worksheet. She wants to be able to insert the information in either a cell or into the header/footer. This is rather easy to do in Excel.

To insert the file path and filename into a cell, you use the CELL worksheet function in the following manner:

=CELL("filename")

This formula returns the entire path, filename, and tab name of the current worksheet, like this:

E:\My Data\Excel\[Budget.xls]Sheet1

If you want to strip out the brackets and the sheet name, you can use the following variation on the formula:

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

The LEFT function gets rid of everything from the right bracket to the end of the string, while the SUBSTITUTE function gets rid of the left bracket.

Putting a path and filename into a header or footer is easy:

  1. Select the worksheet whose header or footer you want to change.
  2. Display the Page Layout tab of the ribbon.
  3. Click the small icon at the bottom-right of the Page Setup group. Excel displays the Page Setup dialog box.
  4. Make sure the Header/Footer tab is displayed.
  5. Click on either the Custom Header or Custom Footer buttons, as desired. Word displays either the Header or Footer dialog box. (See Figure 1.)
  6. Figure 1. The Header dialog box.

  7. Position the insertion point in the Left Section, Center Section, or Right Section boxes, as desired.
  8. Click on the Insert File Path button. (It looks like a file folder with a piece of paper sticking out.) Excel inserts the following code at the insertion point:
  9.      &[Path]&[File]
    
  10. Click on OK two times to close both dialog boxes.

When you print the worksheet, Excel replaces the codes in step 5 with the path name and the file name of the workbook, respectively.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11356) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Adding a File Path and Filename.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Rechecking Spelling and Grammar

If you ever need to check the spelling or grammar of a document from scratch, it can be confusing knowing the proper steps to ...

Discover More

Changing a Heading to Body Text

When working on an outline of your document, you may want to demote a heading so that it is treated just like your body text. ...

Discover More

Deleting MRU Entries

By default Word dutifully keeps track of the different files you've worked on in the past. If you want to delete an entry ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Use Filenames that Sort Properly

When storing your Excel workbook, you need to specify a file name to be used for the workbook. Take a moment to consider how ...

Discover More

Specifying the Number of MRU Files

MRU (most recently used) files can be a great help when you work with a given set of common workbooks. Excel allows you to ...

Discover More

Full Path Names in Excel

Need to know what the full path name is for the current workbook? With a simple macro you can display the full path name in ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 1?

2016-01-11 17:14:49

Joe Lou

Thank you for the wonderful tips.


2013-09-14 08:43:36

Bryan

I'm glad I could help. You actually don't even strictly need to use Application.Volatile False, unless it's required by your organization's programming standards, or you just like to have it there for peace of mind. UDFs default to non-volatile. But it doesn't hurt anything to keep it there, either (I guess theoretically if you used the formula thousands of times it could slow it down, but the difference is probably imperceptible compared to whatever the rest of the code is doing).


2013-09-13 19:23:49

Aldo

This is fantastic! This is what was needed, a good clear explanation. No hard feeling here. You are correct that I have used Application.Volatile redundantly in the example I posted, and I should have pointed that out earlier. But I was more focused on the "FileName = ActiveWorkbook.FullName" line so I over looked that fact.

Application.ActiveWorkbook property is inherently volatile and "Application.Volatile" is not needed in this example. But having "Application.Volatile False" used correctly in certain macros can and does speed up calculations on a complex worksheet because a nonvolatile function is recalculated only when the input variables change, thus saving processing time. That is the most common way I use A.V, by creating nonvolatile functions.

And I wasn't aware of the previous posted articles either. I haven't spent much time searching around the site to seeing what was posted. Thanks for the links.


2013-09-13 14:39:06

Erik

Bryan,

Thanks for this good in-depth explanation of the Volatile function.
It will certainly help me not to miss-use it in future projects.


2013-09-13 08:34:29

Bryan

Aldo,

As I clearly stated ("The fact that *you* describe..."), I was not assuming anything about you, and was only taking at face value what you have said to me. I really could care less about your qualifications or your length of tenure. It's results that matter, and the only thing you can use to defend yourself is the logical fallacy of argument from authority (I have knowledge therefore I am right).

(And let's talk about authority: you make it sound like you were hired by MS or something. Anyone can take the MOS tests. I just looked at the Specialist level, which you claim to be -- there's nothing covered on the outline that I don't know how to do, and I notice a distinct lack of VBA required. So If I make an assumption about you that you actually are an Excel MOS, your qualifications don't apply here. The Expert level looks like it covers super basic VBA stuff -- and again, looks like I could probably pass without training -- but nothing as nuanced as when to use Application.Volatile. Please correct me if the official Microsoft descriptions of the courses have an egregious error. I will thank you for pointing these out to me, however; they would look great on my resume and I’m now going to look into them further.)

So since you refuse to explain why you should use A.V, let me explain why you shouldn't:

Application.Volitile tells Excel to make a user-defined function (UDF) volatile. This means that any time you perform a recalculation, the function will be called again. Worksheet functions that are volatile are things like TODAY and, NOW, OFFSET, and INDIRECT, for which the function value could change based on an external (non-cell linked) parameter. For example, since NOW is volatile, it updates every time you perform a recalculation, so the date/time are constantly updating. If it were not volatile, then Excel would never know to update the time, and you would be stuck in the past until you performed a full recalculation (done either manually or before saving). In automatic calculation mode, a recalculation is called any time you change ANY cell, so in our example from before, NOW updates itself anytime you make a change to the worksheet.

Non volatile functions only recalculate when their precedent cells (the cells used in formulas) have changed. So if you have a formula in cell A1 of =B1, and your formula in cell A2 is =B2, when you change the value of B2, only A2 recalculates and not A1. It is important to avoid volatile functions if you can help it, so that you don't have lots of cells recalculating any time you make a change to *any* cell. This can be done with helper cells (i.e. only putting the date in one cell and having every cell refer to that one; the date cell will recalculate constantly, but the rest will only recalculate when the date changes) or by rewriting formulas to avoid volatile functions entirely. The more volatile functions you have in a worksheet, the more calculations will have to be done at *every* cell change, and the slower your worksheet will run. This is my explanation behind my statement of "The fact that you describe controlling large worksheets leads me to believe that you don't know what it does."

Now let's look at what your function does. ActiveWorkbook.FullName gives you, as it sounds, the full path and filename of the active workbook, i.e. the one you are looking at at the time. (Side note here: since you used ActiveWorkbook.FileName instead of Application.Caller.Parent.Parent.FileName, if you have two workbooks open and change a cell in the second workbook, your function in the first workbook will change to reflect the file path of the second book. Try it out if you don't believe me.) Now if you want to know if your function should be volatile, you have to ask yourself the question: Does the function depend on external inputs? In this case, the answer is yes (the file name is not a range value passed as a variable), but you have to ask yourself the important follow-up question: Does the external dependency have a value that changes? Since your file name is not something that changes as you use the worksheet, the answer is clearly no. Therefore, you do not need to and should not use Application.Volatile in your FileName function. And again, your file name has ABSOLUTELY NOTHING TO DO WITH (1) using A.V "all the time"; (2) "control[ing] macros in very large worksheets"; and least of all (3) "programming Excel since Win3.x days which was Office 4", and none of these things should be deciding factors for using it.

Now you might be saying: Bryan, but what about if I do a "save as"? Won't the file name change then? Why yes it will. But here's the thing: a full recalculation is performed whenever you reopen the workbook, so your file will be in sync the next time you open it. And there's the other kicker: your workbook recalculates *before* a save, not after, so even with A.V in the program, you will still have the wrong file name until you edit another cell. If you are worried about having the most up-to-date file name for a save 'n' print, *adding the A.V line isn't good enough*. If you are in a situation where you routinely use Save As, Application.Volatile could be a safety measure, but it's still less than ideal because of all the times you are recalculating when you don't need to. A better solution would be to update the Workbook_Print event and add the line "Application.CalculateFull".

Therefore, Aldo, there is no circumstance I can find for which you should use Application.Volatile in your function. I reiterate my prior statement: "Aldo, take out Application.Volatile. You've picked up one of Allen's bad habits."

(Perhaps you also want an explanation of the second sentence? Check out this page, where I explained to Allen that his own article on A.V was incorrect, and he used my comments to completely change the article: http://excelribbon.tips.net/T012654_Identifying_Digit-Only_Part_Numbers_Excluding_Special_Characters.html)

Smug passive-aggressive smile right back atcha :)


2013-09-12 16:43:34

Aldo

Bryan, I appreciate that fact that you don't who I am, what I do or know, and that is hard to show sometimes with texting/blogging/whatever. Many people get their hairs on-end because of it.

So I will tell you (and others) this ... I am Microsoft Certified Specialist in Excel. I did the work and went to MS and did the tests. I know exactly what "Application.Volatile" is and does. I have been using and programming Excel since Win3.x days which was Office 4.

I am part of this group because I always like to know what others are doing, find out about little tricks and work-arounds that others are using, and to help where I can to make this site the best it can be.

The best thing for you to do instead of "assuming" what others know, is to write an explanation as to why you feel strongly about "Application.Volatile" and it use. That way everyone here can learn about it and use it or not.

Happy coding :-)


2013-09-11 11:55:06

Bryan

There are uses for A.V, but you macro does not demonstrate one of them. "Using it all the time" means nothing. The fact that you describe controlling large worksheets leads me to believe that you don't know what it does.


2013-09-10 15:43:53

Aldo

Bryan ... I use it "Application.Volatile" all the time to control my macros in very large worksheets I create. It has it's purpose.


2013-09-10 11:49:14

Bryan

Aldo, take out Application.Volatile. You've picked up one of Allen's bad habits.


2013-09-09 18:15:27

Steve

I agree with John, always add a cell range. This applies to Windows machines as well.

If you don't add a cell reference, and you have multiple files open, all files with this formula will display the path of the file last calculated.

Many years ago I had a problem using a cell with data in it. This may not apply anymore, but I have a habit of using a cell that is likely to be empty, like AZ7 (I have a macro that inserts the formula).


2013-09-09 11:47:08

Matt

In the Header/Footer sections Alt + ___ will yield:

P=pathfile
A=tab name (I like to insert a "" between the pathfile & tab name)
D=date
M=time
T=page #
U=# of pages
F=font dialog box
L, C or R=jump to Left, Center or Right sections.


2013-09-09 11:38:21

Aldo

I use this macro when I need a file name string in my projects. I actually have a Add-In file that has many macros I use all the time, this being one of them.


Public Function FileName() As String
Application.Volatile
FileName = ActiveWorkbook.FullName
End Function


2013-09-09 09:12:24

Bryan

CELL works the same way on the PC version. This is definitely an unexpected outcome, so users should take note.


2013-09-07 11:46:01

John

The CELL function has a second, optional but important argument that specifies which cell is referenced. If omitted, the function will refer to the most recently updated cell of the workbook which may not be on the worksheet of interest.

To play it safe, specify a cell on the worksheet for which the path, filename and tab name are desired. For example, =CELL("filename",A1) if inquiring about the worksheet on which the CELL function appears.

At least this is how CELL works on my 2008 Mac version of Excel.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.