CELL (LEFT, RIGHT, MID, FIND, LEN)

Workbook info using functions (and no VBA). Copy the formulas into your workbook (If the workbook is new and has not been saved these formulas will not work - there's no information for them to return!)

The file path and name
=CELL("filename",A1)

The file path
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

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

The sheet name
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1))

The following VBA gets the same information. "Debug.Print" sends the result to the Immediate window.

Sub Demo_FilePathAndName()
    With ThisWorkbook
        Debug.Print .Name
        Debug.Print .Path
        Debug.Print .FullName
    End With
End Sub

If you want data relating to the active workbook, change ThisWorkbook to ActiveWorkbook.

Published: 12-Mar-2003
Updated: 21-Dec-2003 (VBA code)
Last edited: 05-Jun-2005 19:16