WORKBOOKPATHFILENAME

WORKBOOKPATHFILENAME()
Returns the folder path and file name of the active workbook.

REMARKS
This has 2 functions

WORKBOOKPATHFILENAME

Returns the full path and name of the active workbook.

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

Public Function WORKBOOKPATHFILENAME(Optional ByVal rgeCellRange As Range = Nothing) As String 

    Application.Volatile 
    If rgeCellRange Is Nothing Then 
        WORKBOOKFULLPATH = ThisWorkbook.FullName 
    Else 
        WORKBOOKFULLPATH = rgeCellRange.Parent.Parent.FullName 
    End If 

End Function 

If you want the full path and name of a different open workbook, you can use the optional argument to reference a cell in that open workbook
You can use the following formula
=LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1)"

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

You can use the user defined WORKBOOKNAME function to return the name of the active workbook
You can use the user defined WORKBOOKPATH function to return the folder path of the active workbook

WORKBOOKPATHFILENAME_SHEET

Returns the folder path, file name and the worksheet name.

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

Public Function WORKBOOKPATHFILENAME_SHEET() As String 
End Function 

You can use the built-in worksheet function CELL("filename") to return something similar.