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)&"\""

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
You can use the user defined WORKBOOKNAME function to return the name of the active workbook
You can use the user defined WORKBOOKFULLPATH function to return the full path and name of the active workbook