This is very easy with code. It's actually pretty easy with formulae as well:
To display the full title of the Workbook in a cell - including the Sheet name:
=CELL("FILENAME",F10)
eg.[color green] H:\MSOffice\Macros\[Accessing document properties.xls]Sheet1[/color]
To display the Path Including Workbook Name:
=MID(CELL("FILENAME",F10), 1,FIND("]",CELL("FILENAME",F10)))
eg. [color green]H:\MSOffice\Macros\[TestFile.xls][/color]
To display the Path Excluding the Workbook Name:
=MID(CELL("FILENAME",F11), 1,FIND("[",CELL("FILENAME",F11))-1)
eg. [color green]H:\MSOffice\Macros\[/color]
To display the Filename:
=MID(CELL("FILENAME",F10),FIND("[",CELL("FILENAME",F10))+1,FIND("]",CELL("FILENAME",F10))-FIND("[",CELL("FILENAME",F10))-1)
eg. [color green]Accessing document properties.xls[/color]
To display the Sheet name:
=RIGHT(CELL("FILENAME",F10),LEN(CELL("FILENAME",F10))-FIND("]",CELL("FILENAME",F10)))
eg. [color green]Sheet1[/color]
Just enter the formula as above - very useful for referencing in code and linked formulae. The "F10" is just an arbitrary cell reference - it doesn't matter as long as it is consistent in the formula
[color red]Please note that these functions will not work until the workbook has been saved.[/color]
Credit for this must go to Julian Milano (Excel L list)
