Help getting the parent folder name

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

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