If you want to get the current workbook’s full name and path without a sheet name, you can use a formula that employs several text functions to strip off the sheet name. The final result will be a text string that looks like this:
path[workbook.xlsm]
Formula
=SUBSTITUTE( LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[","")
Explanation
How the formula works
The CELL function is used with “filename” to get the full workbook name and path:
CELL("filename",A1)
The result is a full path that looks like this:
path[workbook.xlsm]sheetname
The LEFT function receives the full path as text, along with the the location of square bracket “]” (minus 1) which is calculated by the find function:
FIND("]",CELL("filename",A1))
With these arguments, LEFT effectively strips off the sheet name out of the path. However, the left square bracket “]” remains:
path[workbook.xlsm
The final function, SUBSTITUTE, removes the “]” by replacing it with an empty string (“”).
=SUBSTITUTE(path[workbook.xlsm,"[","")