How to get the name of the parent folder from a file path?

Question

Using VBA, is it possible to get the name of the folder that contains a file?

Input: C:\Windows\System32\notepad.exe

Output: System32

Answer 1

This will return the parent folder name:

Public Function GetParentFolderName(ByVal path As String) As String
    Dim result As String
    With fso = CreateObject("Scripting.FileSystemObject")
        result = .GetParentFolderName(path)
        GetParentFolderName = Mid(result, InStrRev(result, "\") + 1)
    End With
End Function

Usage:

Sub GetParentFolderNameTest()
    Debug.Print GetParentFolderName("C:\Windows\System32\notepad.exe")
End Sub

Output:

System32

Answer 2

You can do simpler (no ref needed ):

Function ParentFolder(ByVal path As String)
        vArray = Split(path, "\")
        size = UBound(vArray)
        ParentFolder= vArray(size - 1)
End Function

Then call

ParentFolder( "what\ever\path\to\a\file.ext" )

If you need the parent folder of a folder like, replace the last line by

ParentFolder = vArray(size)

for example, the parent folder of the current application (excel workbook)

ParentFolder( ActiveWorkbook.path )

Answer 3

This is the code I wrote to answer a very similar problem, when this post didn't have a satisfactory answer for me.

Function parentFolderName(ByVal path As String)
Dim fso
    Set fso = CreateObject("Scripting.FileSystemObject")
    parentFolderName = Dir(fso.GetParentFolderName(path), vbDirectory)
End Function

calling the function as so:

Debug.Print parentFolderName("C:\Windows\System32\notepad.exe")

This will print "System32", and if the directory does not exist it returns an empty String "". This will work whether the path is to a file or a folder/directory, as the Dir function "Returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive" (from the docs: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dir-function)

I feel this is a little simpler than all of the string manipulation that could easily introduce errors.