Help getting the parent folder name

Try

=CELL("FILENAME")

Maybe the old Excel4 Get.Document(76) and Get.Document(88) might be interesting for you. You can use it (indirectly) as a worksheetfunction by going to Insert >> Name >> Define; type in an appropriate name (e.g. NameOfFile) and type the macro formula =GET.DOCUMENT(76) in the "Refers to:" edit area. In the Excel spreadsheet you can then use =NameOfFile as a worksheetfunction. No VBA required.

=CELL("FileName") gives the path + workbook name + sheet name
=GET.DOCUMENT(76) gives the workbook name + sheet name
=GET.DOCUMENT(88) should give the workbook name alone

Or you can use:

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

or for the filename without extension

=MID(CELL("Filename"),FIND("[",CELL("Filename"))+1,FIND(".",CELL("Filename"))-FIND("[",CELL("Filename"))-1)

To get only the path:

=LEFT(CELL("Filename");LEN(CELL("Filename"))-LEN(NameOfFile))

Hans

Hi LAS,

With all respect to the built in worksheetsfunctions here is a User Defined Functions that will give You a solution:

Function ParentF()
Dim vaArr As Variant
vaArr = Split(ActiveWorkbook.Path, "\")
ParentF = vaArr(UBound(vaArr) - 1)
End Function

This will give the name of the subfolder that the active workbook is saved in.

You enter it like: =PARENTF()

BTW, I would use =GET.DOCUMENT(2) to get the path for the active workbook.