Help getting the parent folder name

    Can some one help me identify the partent folder name of the current open workbook via a formula.


    eg: c:\My documents\Clients\Analysis\Reports\Rep1.xls


    I want to get the parent folder name i.e
    'Reports':(

    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.

    Thanks Hans and Dennis,


    It is not quite what I need. I have resorted to the UDF for now, but I was trying to avoid it so that users would not have to enable macros just for this function. (I have no other macros in this workbook.)


    Also I am not looking for the workbook or path. I am looking for the LAST folder in the path.
    eg: IN C:>My documents>Clients>Subfolder 1>Subfolder 2>Subfolder 3>File.xls


    I want to return just 'Subfolder 3'.


    Not so easy!:lol:


    BTW: How do you post a backslash? It doesn't come out:mad:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login