Retrieve a Workbook's Name and Path
Every now and then you might want a cell to return the name of a workbook, or even the workbook's filename and path. With this tutorial, it's easy to retrieve a workbook's name and path.
The three user-defined functions we explain in this section place the name of a workbook into a cell, or the workbook's filename and path into a cell. The first two examples, MyName and MyFullName, do not take any arguments. The last one, SheetName, is used in place of nesting the MID and other functions inside the CELL function to get the sheet name, a process that commonly would require the following unwieldy formula:
=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,255)
As you can see, this requires quite a bit of typing for such a simple result, which is why we initially developed the SheetName custom function. To use this user-defined function, press Alt/Option-F11, select Insert → Module, and paste in the following code:
Function MyName( ) As String MyName = ThisWorkbook.Name End Function Function MyFullName( ) As String MyFullName = ThisWorkbook.FullName End Function Function SheetName(rAnyCell) Application.Volatile SheetName = rAnyCell.Parent.Name End Function
Save the function and close the window. The function will appear under User Defined in the Insert Function dialog (Shift-F3).
In this tutorial:
- Retrieve a Workbook's Name and Path
- Excel's Three-Criteria Limit for Conditional Formatting
- Run Procedures on Protected Worksheets
- Distribute Macros
- Automatically Add Date/Time to a Cell upon Entry
- Create a List of Workbook Hyperlinks
- Find a Number Between Two Numbers
- Name a Workbook with the Text in a Cell
- Sort Worksheets
- Password-Protect a Worksheet from Viewing