WORKBOOKPATH

WORKBOOKPATH()
Returns the folder path of the active workbook.

REMARKS
Columns=1

'rgeCellRange - (Optional) A cell in the workbook you want the name of.

Public Function WORKBOOKPATH(Optional ByVal rgeCellRange As Range = Nothing) As String 
Dim sfullpath As String 
    Application.Volatile 
    If rgeCellRange Is Nothing Then 
        sfullpath = ThisWorkbook.FullName 
    Else 
        sfullpath = rgeCellRange.Parent.Parent.FullName 
    End If 

    If (VBA.InStr(sfullpath, "\") = 0) Then 
        sfullpath = "'no path'" 
    Else 
        sfullpath = Left(sfullpath, InStrRev(sfullpath, "\")) 
    End If 

    WORKBOOKPATH = sfullpath 
End Function 

You can use the following formula
=LEFT(CELL(Filename",A1),FIND("[",CELL("Filename",A1))-2)&"\""
You can use the following formula
=LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1)"
=SUBSTITUTE( LEFT(CELL("filename",A1), FIND("]",CELL("filename",A1))-1),"[","")"

If you want the folder path of a different open workbook, you can use the optional argument to reference a cell in that open workbook

MORE FUNCTIONS
WORKBOOKFILENAME Returns the file name of the active workbook.
WORKBOOKPROPERTIES Returns a particular workbook property of the active workbook.
WORKSHEETNAME Returns the name of the worksheet in a given position.