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 8, 2017)

18

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

Arranging Workbook Windows

If you find yourself working with a number of different workbooks at the same time, you may want to arrange your desktop so ...

Discover More

Dealing with Long Formulas

If your worksheet formulas seem to go on forever, here's a handy way to make them more understandable. (All you need to do is ...

Discover More

Pulling Apart Characters in a Long String

You can easily use formulas to pull apart text stored in a cell. For instance, if you need to pull individual characters from ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Correctly Saving Delimited Files

Delimited files are often created through Excel so that your data can be exported to other programs. If the delimited data ...

Discover More

Invalid Names when Opening Workbook

Don't you hate opening a workbook and seeing error messages? If you see a message that some "invalid names" were detected in ...

Discover More

Comma-Delimited Differences for PC and Mac

When you choose to save worksheet data in CSV format, Excel gives you three choices for file formats. Those choices are ...

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. Maximum image size is 6Mpixels. 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 6 + 0?

2017-09-14 05:24:16

Bianca

My file path does not put the brackets around the filename so this wont work.
Is this a latest windows thing?

ie. my exact filename is:

C:\Users\company\Dropbox\TeacherKit\Shared\macro\Adults.xlsx


2017-09-08 17:18:55

Arun Kumar

You can also use =SUBSTITUTE(SUBSTITUTE(CELL("filename"),"[",""),"]",""), if you just want to remove [ ]


2017-09-08 17:15:18

Arun Kumar

You can also use =SUBSTITUTE(SUBSTITUTE(CELL("filename"),"[",""),"]","")

It will give same result like this =SUBSTITUTE(LEFT(CELL("filename"),FIND("]",CELL("filename"))-1),"[","")


2017-09-08 04:54:32

Philip

Bryan, to stay in Aldo's first post's philosophy ... I have been an MS Excel user since version 1.0 came out on Mac (long before there even was a Windows version), and have been programming VBA for Excel as long as it exists. I have some pretty complex VBA driven workbook applications out there, and I manage these types of applications on both Mac and Windows environments. In addition, I have been using (as a paying customer) all of the MS Office apps for as long as they exist (again on both platforms). All of that just to note that (as you mentioned) "I have authority therefore I am right" is not a valid argument in any scientific or technical discussion (or at least it shouldn't be) : I had no clue about what "volatile" meant or did (and not for lack of trying to find out, I searched for a long time trying to understand it and gave up on it in the end). I am soooooooo glad to have had the opportunity to read your post with the explanation, it makes things VERY clear. Sincere thanks from a 30+ year excel experienced user ;-)

Also, Allen, thanks for maintaining this platform here. The MS support websites are useful to find syntax information etc. but finding out why one would want to use a feature in the first place or how to make the most of it is something one can only learn from other users with similar problems to solve.


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.