In my current job in Mergers & Acquisitions, I have to prepare many complex folder structures for targets’ data rooms. It’s a tedious but straightforward process that can take up to an hour, as each Request For Information letter is almost the same but contains slight differences. For example, we may need detailed information of the shareholder structure and various related documents for a specific company we are looking to acquire. On the other hand, for a smaller, one-person organization, such a request is irrelevant.

I’ve struggled to create said data room folder structures for about a month now and decided it’s time to roll up my sleeves and figure a more robust way to make them.

It turns out it’s much easier than anticipated. It took me around 30 minutes to prototype the below code, and I decided it may help some of you by giving you an idea of how VBA can help with improved efficiency.

Getting Started

Let’s open Excel’s Visual Basic for Applications (VBA) Editor (Developer tab -> Visual Basic, or Alt+F11), and add a new UserForm to the PERSONAL.XLSB file.

This is your Personal Macro Workbook that gets loaded with every instance of Excel. This means that if you want a macro to be visible for all excel files, you need to place it here. Let’s adjust the size of the User Form and add three CommandButtons and a TextBox. The idea is to ask the user to select a parent folder where the macro will generate the folder structure. This is then shown in the Selected Path TextBox for reference.

Once we click the Create Directories button, it will take our current selection in Excel and use any valid paths within this selection to generate corresponding folders. For more context, here is what I want the selection to look like. It’s a list of folders that I want to replicate within the parent folder. It is essential to list sub-folders below parent folders. Otherwise, the VBA function that creates a new folder (mkDir) won’t work.

Writing the Code

When we have ‘designed’ our user form, it’s time to start writing some code. If you double click on any item (a CommandButton in this instance), it will open the code editor and add the Click() event by default. You can always use the two drop-downs above the code editor to generate various event handlers for any object from your user form.

OK, let’s look at the code itself.

The Cancel Button

Private Sub cancelBtn_Click()
    Unload Me
End Sub

The cancelBtn code is usually the first thing I write, as it makes testing my user form much more accessible. Plus, it’s effortless – we tell the user form to Unload (close) itself.

Get Folder Function

I already have a function that prompts the user to select a folder and returns that as a String that I will re-use in this macro. Let’s take a look at how it works.

Function GetFolder() As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Parent Folder"
        .ButtonName = "Use This Folder"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem
    Set fldr = Nothing
End Function

We start by defining two variables – a FileDialog, and a String, which will hold the selected path. We then create a msoFileDialogFolderPicker and add some self-explanatory options to it.

The magic happens at the end of the With statement. There we have a conditional statement that checks if the .Show property is different from -1. The .Show property can either be -1 when we click the Action button (the one selecting the folder) or 0 when we close/press the Cancel button.

If .Show is anything but -1 (user selected a folder), then we skip and go to the NextCode section below. If .Show is equal to -1, this means the user picked a folder, so we set the sItem var to it.

Then the macro continues with the NextCode section, which sets GetFolder to the path string, and clears the FileDialog object from memory by setting it to equal Nothing. The way functions work in VBA, when we set the name of the function (GetFolder) to equal a value, this is the value that the function returns after it has completed running.

The Browse Button

All the browseButton has to do now is call the function and assign its result to our user form’s path text box.

Private Sub browseButton_Click()
    pathTextBox.Text = GetFolder()
End Sub

The FolderExists Function

Before we start writing out our folder creation code, we will need an additional function to check if a given folder already exists. We don’t want to overwrite an already existing location.

Function FolderExists(folder As String) As Boolean
    If Dir(folder, vbDirectory) = "" Then
        FolderExists = False
    Else
        FolderExists = True
    End If
        
End Function

It’s a simple function that takes the folder path as a String and returns a Boolean (TRUE or FALSE). How it works is it tries to open the folder. If it doesn’t open, it doesn’t exist, and vice versa. Depending on the result, the function will return FALSE if the folder path does not exist, and we can create it.

Create the Folders

Now we have all the necessary parts to start our Subroutine for the Create Directories button.

This one is a bit longer, so let’s break it into bits.

Private Sub createDirsBtn_Click()
    Dim sl As Range
    Dim counter As Integer
    
    counter = 0
    
    If FolderExists(pathTextBox.Text) = False Or pathTextBox.Text = "" Then
        MsgBox "Please, select a parent folder!", vbCritical, "No Folder Selected"
        Exit Sub
    End If

We start by adding two variables. We need a Range variable (sl) to hold the cells containing folders we want to generate. Next, we need an Integer variable (counter) to keep track of how many folders the macro generates. We then set it at zero so that it will restart every time the macro runs.

It’s time for the first check. We want to ensure the provided string value in our parent path field is a valid folder. It is also a good idea to make sure the field is not empty, as the user may click the Create Directories button before selecting the parent folder. In any of these cases, we show an error message and Exit the Subroutine, preventing the rest of the code’s execution.

Let’s look at the next check.

    If TypeName(Selection) = "Range" Then
        Set sl = Selection
    Else
        MsgBox "Please, use a valid selection!", vbCritical, "Selection Error"
        Exit Sub
    End If

The second check we perform is to ensure the user has a proper selection in Excel. They might have selected an image or a chart instead of a range of cells. In this case, we show an error and again Exit the Sub. Otherwise, we can set our Range variable (sl) to the current Selection.

Now we can go ahead and loop through all the selected cells.

    For Each cell In sl
        If cell.Value = "" Then
            'do nothing with this cell
        ElseIf FolderExists(pathTextBox.Text & "\" & cell.Value) Then
            'do nothing, folder exists already
        Else
            MkDir (pathTextBox.Text & "\" & cell.Value)
            counter = counter + 1
        End If
    Next cell
    
    MsgBox counter & " folders successfully created!", vbOKOnly, "Done"
    Unload Me
    
End Sub

Let’s employ a For Each loop to go over each cell within the user selection. For each of those, we check if the cell is empty (cell.Value = “”). We also check if the folder exists, using the function we added earlier, as we don’t want to overwrite any existing folders. In any of those two cases, the macro does nothing. If those are not true, we create the folder within the specified parent location and increase the counter by one.

Once the loop is complete, the macro shows how many folders were created and Unloads the user form.

That’s the code. Now, let’s add it to our Excel Ribbon.

Testing the Create Folders Macro

Right-click anywhere on the Ribbon tabs and go to Customize Ribbon. You can then create a new Tab and Group on the right side and look for the macro in the Macros section on the left. Add it, rename it and select an icon.

Now back to Excel. The user opens their folders list and selects all folders they want to generate. Note, we have 27 folders selected.

We hit the newly added Ribbon button, and our user form pops up.

The next step is to Select a Parent Folder. This will open a dialog window prompting you to select a folder.

This will then appear as a string in the selected path Text Box.

If this is the right location, we then press Create Directories. The macro thinks for a millisecond, and we get our message confirming 27 folders were successfully created.

Let’s make sure everything happened as it was supposed to. If we open the parent folder, we can see our folder structure reflected there, with all the main folders and sub-folders.

Conclusion

We have quickly prototyped a handy tool. It’s incredible how many aspects of the day-to-day work we can automate by employing a little VBA into our workflow. And being that the language and implementations haven’t changed in decades, it’s straightforward to learn, as information and tutorials are abundant out there.

You can copy the whole code below.

Please, show your support by sharing this article with colleagues and friends.

Download Form and Code

You can download an export of the VBA form and code below, that you can then import in your Excel instance. Please, keep in mind, depending on your Excel version, this may not work straight after importing.

Full Code

Function GetFolder() As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Parent Folder"
        .ButtonName = "Use This Folder"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem
    Set fldr = Nothing
End Function
Function FolderExists(folder As String) As Boolean
    If Dir(folder, vbDirectory) = "" Then
        FolderExists = False
    Else
        FolderExists = True
    End If
        
End Function
Private Sub browseButton_Click()
    pathTextBox.Text = GetFolder()
End Sub
Private Sub cancelBtn_Click()
    Unload Me
End Sub
Private Sub createDirsBtn_Click()
    Dim sl As Range
    Dim counter As Integer
    
    counter = 0
    
    If FolderExists(pathTextBox.Text) = False Or pathTextBox.Text = "" Then
        MsgBox "Please, select a parent folder!", vbCritical, "No Folder Selected"
        Exit Sub
    End If
    
    If TypeName(Selection) = "Range" Then
        Set sl = Selection
    Else
        MsgBox "Please, use a valid selection!", vbCritical, "Selection Error"
        Exit Sub
    End If
    
    For Each cell In sl
        If cell.Value = "" Then
            'do nothing with this cell
        ElseIf FolderExists(pathTextBox.Text & "\" & cell.Value) Then
            'do nothing, folder exists already
        Else
            MkDir (pathTextBox.Text & "\" & cell.Value)
            counter = counter + 1
        End If
    Next cell
    
    MsgBox counter & " folders successfully created!", vbOKOnly, "Done"
    Unload Me
    
End Sub

Dobromir Dikov

FCCA, FMVA

Hi! I am a finance professional with 10+ years of experience in audit, controlling, reporting, financial analysis and modeling. I am excited to delve deep into specifics of various industries, where I can identify the best solutions for clients I work with.

In my spare time, I am into skiing, hiking and running. I am also active on Instagram and YouTube, where I try different ways to express my creative side.

Twitter Instagram Youtube Linkedin