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.
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.
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.
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
FCCA, FMVA, Founder of Magnimetrics
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.
The information and views set out in this publication are those of the author(s) and do not necessarily reflect the official opinion of Magnimetrics. Neither Magnimetrics nor any person acting on their behalf may be held responsible for the use which may be made of the information contained herein. The information in this article is for educational purposes only and should not be treated as professional advice. Magnimetrics accepts no responsibility for any damages or losses sustained in the result of using the information presented in the publication.