Get the file name of the current workbook

Get file name of current workbook thumb

Get file name of current workbook

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]

Get Workbook Name Page Setup

The Page Setup window will open.  Click on the Header/Footer tab, then select either Custom Header or Custom Footer.

Get Workbook Name Page Setup menu

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.

Get Workbook Name Footer

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.

 

CELL Function

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

Get Workbook Name CELL options

For our current purposes select filename, then finish the function as shown below.

Get Workbook Name CELL function

Now you can use the SEARCH, LEN, MID, RIGHT and LEFT functions to strip out the information you need.

File path:

=LEFT(CELL("filename"),SEARCH("[",CELL("filename"))-1)

File name:

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

Sheet name:

=RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename")))

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.

Get Workbook Name 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

Sheet name:

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.

Get Workbook Name Using UDF

 

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.

Get Workbook Name QAT

Click OK to close the window.  The file path and file name are now easily accessible to copy.

Get Workbook Name QAT display

 

 

What next?

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.

Tab Hopper Thumb







Save

Save

Save

Save

Leave a Reply

Your email address will not be published. Required fields are marked *