If you want to get the workbook path (directory) only, you can do so with a formula that uses the LEFT and the FIND function. The result will look something like this:
Drive:\path\to\file\ //Windows Drive:path:to:file: //Mac
Formula
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)
Explanation
How the formula works
The cell function is used to get the full file name and path:
CELL("filename",A1)
The result looks like this:
path[workbook.xlsm]sheet
The full file name and path are fed into the LEFT function, which is used to extract just the full directory path.
The number of characters to extract by locating the the position of the left square bracket “[” with FIND and subtracting 1:
FIND("[",CELL("Filename",A1))-1