There are times in Excel when we may want to display or use the file name of the current workbook, or maybe the name of a specific worksheet. Whilst there are a variety of options none of them are particularly obvious. This post will consider some of the possible options:
- Header/Footer section
- CELL Function
- VBA User Defined Function
- Using the Quick Access Toolbar
The Header/Footer section
When I first started using Excel, I think it was Excel 97, the Header and Footer sections were much more prominent feature than they are in more recent versions (or maybe it’s just that we print less documents these days). The Header and Footer sections are areas at the top and bottom of an Excel document which exist only when printed, they are often used for page numbing or section names.
How often do you lose workbooks? Perhaps you have a print out from a workbook in front of you, but you just cannot find the original file. You can use the Header/Footer sections to print the file path, file name and sheet name onto the document.
Click: Page Layout -> Page Setup -> [Expand Setting Button]
The Page Setup window will open. Click on the Header/Footer tab, then select either Custom Header or Custom Footer.
Within the Header or Footer window there are 3 buttons which you can use to add file information.
- Insert File Path
- Insert File Name
- Insert Sheet Name
Select a section and click these icons. The code for that icon is then displayed in the selected section.
Once finished, click OK. Then click OK again to close the Page Setup window.
When you next print the document, it will display the file path, file name and sheet name.
The Header/Footer section is great and all, but what if you want to use it in a formula? or in dynamic named range? or display it on the worksheet? In this circumstance, we can use the CELL Function.
The CELL function has just two arguments:
- Info_Type: A text string indicating the type of information you want to return
- Reference (optional): The Cell reference about which you want the information. If the reference is omitted it will use the last changed cell as it’s default.
Select a cell and start typing the CELL function. The options for the Info_Type will appear
For our current purposes select filename, then finish the function as shown below.
Now you can use the SEARCH, LEN, MID, RIGHT and LEFT functions to strip out the information you need.
There is a big caveat with the CELL function: it’s a volatile function, so it can cause calculations to be slow. Check out my post on volatile functions if you’re not sure what they are.
VBA User Defined Function
If you want to avoid volatile functions, this is where VBA can come in useful. We can create our own functions which are non-volatile.
Open the VB Editor (Press Alt + F11). Then click Insert -> Module.
Enter the code below into the new Module. Return back to Excel and save the file as a Macro Enabled Workbook.
File path & file name:
Function FullFileName(rng As Range) As String FullFileName = rng.Parent.Parent.FullName End Function
File path only:
Function FilePath(rng As Range) As String FilePath = rng.Parent.Parent.Path End Function
Function SheetName(rng As Range) As String SheetName = rng.Parent.Name End Function
The screen shot below shows the result of using each User Defined Function in Excel.
Using the Quick Access Toolbar
Maybe you do not need something to be on the workbook itself, but rather you just need to know the file path to copy into an e-mail. We can easily add this information into the Quick Access Toolbar (QAT). This will make the file name easily available for whenever we need it.
Click: File -> Options > Quick Access Toolbar
In the Choose commands from drop down select All Commands, then scroll down to Document Location. Click the Add button to move it into your QAT.
Click OK to close the window. The file path and file name are now easily accessible to copy.
Get Excel news, tips & tricks straight to you inbox. Helping you to save time and achieve more with Excel.
You will also receive the Tab Hopper Add-in for FREE.