This question often comes up when I am showing how to link between sheets.
“Return an Excel Worksheet Name to a Cell
In Excel it is possible to use the CELL function/formula and the MID and FIND to return the name of an Excel Worksheet in a Workbook. The formula below shows us how:
Where A1 is any non error cell on the Worksheet. If you want the full path of the Excel Workbook, simply use;
It is important to note that the above formulas will only work in a Workbook that has been saved”
Referred web site has a vba example as well.