There are few instances when we want to store the File Name or Current Workbook Name in a particular Cell. This is very easy. This can be done by using Cell() function as shown below:

Syntax: =CELL(“filename”)
Returned Value: C:\Users\Vish\Desktop\[Test.xlsx]Sheet1

Note: The problem with this is that it gives the complete path including Drive and folders with Sheet Name.

  • To Get the Path: (Only Path, Excluding File Name)

    Syntax:  =MID(CELL(“filename”),1,FIND(“[",CELL("filename”))-1)

    Returned Value:  C:\Users\Vish\Desktop\

  • To Get the Workbook Name: (Only File Name)

    Syntax:  =MID(CELL("filename”),FIND("[",CELL("filename”))+1,FIND("]”,CELL(“filename”))-FIND(“[",CELL("f[",CELL("filename”))-1)

    Returned Value: Test.xlsx

  • To Get the WorkSheet Name:

    Syntax: =MID(CELL("filename”),FIND("]lename”))+1,255)

    Returned Value: Sheet1

Read this Also:

cover3d_0-89071700_1484285537__1_

Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!