If you want to get the workbook name only (i.e. the file name without path or sheet name) you can do so with a rather long formula that uses the MID function along with the FIND function.
Formula
=MID(CELL("filename",A1),FIND("[",CELL
("filename",A1))+1,FIND("]",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]sheetname
At the highest level, the MID function simply extracts the file name from the path and sheet. The starting position is calculated with FIND:
FIND("[",CELL("filename",A1))+1
The number of characters to extract is also calculated with FIND:
FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1