July 15, 2011, 3:38 p.m.
posted by donnie
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:
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
You can use the functions as shown in Figure. They take no arguments. The formulas in column A are shown for demonstration purposes only and have no effect on the result.