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

  • 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:


    or for the filename without extension


    To get only the path:



  • 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!