Share via

Create Hierarchical Folder Structure using recursive Function in Excel VBA

  • Article

Introduction:

How to create a folder structure (tree structure) defined in excel sheet using Excel VBA code. Though there are functions which are created over internet, I thought of trying to create this using recursive Function instead of loop.

What is recursive Function:

As the name suggests, name of this function is given based on its nature "recursion". This function is called by itself. Therefore, on a high level you can see that it may work like loop. 

How it is working:

Basically it is based on the following two rules:

Rule 1. First keep drilling down to child nodes and keep creating the directories
Rule 2. Then drill up finding parent node and once parent node found follow the Rule 1 
**Rule 3. **Repeat above two till you find the last column i.e the first column from where you started (Column B in below pic)

 

Code to create above folder structure in your given parent Directory

 

Public currentRange As Range
Public startRange As Range
 
Sub CreateFolderStructure(rootFolderPath As String, currentRange As  Range)
   Dim folderName As String
   Dim currentFolderPath As String
    On Error  GoTo err
    If currentRange.Value <> "" Then 'Don't try to create anything if it is blank
        currentFolderPath = rootFolderPath & "\" & currentRange.Value
        MkDir (currentFolderPath)
    Else
        currentFolderPath = rootFolderPath
    End If
    If currentRange.Offset(1, 1).Value <> "" Then  ' Check if Child exists
        rootFolderPath = rootFolderPath & "\" & currentRange.Value
        Set currentRange = currentRange.Offset(1, 1)
    ElseIf currentRange.Offset(1, 0).Value <> "" Then  ' Check sibling exists
        Set currentRange = currentRange.Offset(1, 0)
    ElseIf currentRange.Offset(1, -1).Value <> "" Then  ' Check if any ancestors exists
        rootFolderPath = getParentPath(rootFolderPath)
        Set currentRange = currentRange.Offset(1, -1)
    ElseIf currentRange.Column <> startRange.Column Then  ' Check if you have reached in the last column backward
        rootFolderPath = getParentPath(rootFolderPath)
        Set currentRange = currentRange.Offset(1, -1)
    Else: End
   End If
   CreateFolderStructure rootFolderPath, currentRange 'calling itself -- recursion is happening here
err:
   If err.Number <> 0 Then MsgBox err.Description ' just come error handling
End Sub
 
'Function to get the Parent Node path
Function getParentPath(currentFolderPath As String) As  String
    Dim reverseStr As String
    Dim firstPos As Integer
        reverseStr = VBA.StrReverse(currentFolderPath)
        firstPos = InStr(1, reverseStr, "\", vbTextCompare)
        getParentPath = VBA.StrReverse(VBA.Mid(reverseStr, firstPos + 1, Len(reverseStr)))
End Function
 
' Function to call the above recursive function
Sub callCreateFolderStructureFunction()
    Dim currentRange As Range
        Set currentRange = mainSheet.Range("B10")
        Set startRange = currentRange
        CreateFolderStructure "C:\Users\vmishra\Documents\VISHWA", currentRange
End Sub

Thanks for reading this article. Kindly provide your feedback and thoughts and ideas. 

I have created an Excel tool to create Hierarchical folder structure and uploaded in the Gallery. You can download it, use it and provide your feedback.
Do not forget to rate in order to improve this tool.