Hack 91 Retrieve a Workbook s Name and Path

   

Hack 91 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 hack, 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),FIND("]",CELL("filename",$A))+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 Paste Function dialog (Shift-F3).

You can use the functions as shown in Figure 7-8. They take no arguments. The formulas in column A are shown for demonstration purposes only and have no effect on the result.

Figure 7-8. Functions and their result

In cell A4 in Figure 7-8, we also placed the standard CELL function that returns a workbook's name, file path, and active sheet name. The CELL function is a standard function that will return information about the current operating systemin other words, information on formatting, location, and contents of a workbook.