WORKBOOKFILENAME

Returns the name of the active workbook.

REMARKS

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

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

    Application.Volatile 
    If rgeCellRange Is Nothing Then 
        WORKBOOKNAME = ActiveWorkbook.Name 
    Else 
        WORKBOOKNAME = rgeCellRange.Parent.Parent.Name 
    End If 
End Function 

There will not be a file extension if the workbook has not been saved.
You can use the following formula

=MID(CELL(filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1) 

If you want the name of a different open workbook, you can use the optional argument to reference a cell in that open workbook
In PowerPak this function is called FILENAME
You can use the user defined WORKBOOKPATH function to return the folder path of the active workbook
You can use the user defined WORKBOOKFULLPATH function to return the full path and name of the active workbook

For instructions on how to add this function to a workbook refer to the page under Inserting Functions