How to create multiple folders at once with Excel

Over the past few months, I have seen many videos showing how to create multiple folders at once using a .bat file (also known as a batch script).

Often these videos leverage Excel to create the folder paths. Then, they undertake various manual actions to get the .bat file into the correct location, so they can execute the script.

It may be better than manually creating folders, but I believe there is a much easier way to create folders with Excel.

In this post, we will work through a small project to create a tool that creates multiple folders at once. Or, if you just want the tool, you can download the example file.

Table of Contents

• Create a single folder with VBA
• Avoiding errors
• Create a single folder based on a cell
• Create multiple folders based on selected cells
• Add button to worksheet
• Conclusion

Watch the video

Watch the video on YouTube

Create a single folder with VBA

With VBA we can create a single folder with one line of code.

Press Alt + F11 to open the Visual Basic Editor.

In the Visual Basic Editor click Insert > Module.

In the new code module enter the following code.

Sub CreateFolders()
MkDir "C:\Examples\Reporting\2024\"
End Sub

This creates a 2024 folder inside an existing C:\Examples\Reporting\ folder.

Change the folder path to match a location on your PC.

MkDir is the VBA command to make a folder (Find out more about MkDir here)

Click the cursor into the code, then click Run. Hopefully, everything should run correctly, the folder will appear.

Avoiding errors

If we re-run the code, an error appears.

This occurs because MkDir cannot make a folder if a folder already exists in that location.

Errors can occur for various reasons:

• Folder with that name already exists
• The parent folder does not exist
• You do not have permission to create folders in that location

To prevent error messages, let’s use the On Error Resume Next statement.

Sub CreateFolders()
On Error Resume Next
MkDir "C:\Examples\Reporting\2024\"
On Error GoTo 0
End Sub

In the code above we change how VBA handles errors:

• On Error Resume Next – if any errors occur it moves to the next line of code.
• On Error GoTo 0 – VBA returned back to previous settings, it will stop if errors occur.

Create a single folder based on a cell

The example above only works for the folder path which is hard-coded into the code. Let’s change this code to work with a selected cell.

The folder path is in cell B7.

Change the code as follows:

Sub CreateFolders()
On Error Resume Next
MkDir Selection
On Error GoTo 0
End Sub

To run the code, select the cell with the folder path, then run the macro.

Create multiple folders based on selected cells

Rather than using a single cell, let’s create a loop so we can create a folder for every cell in a selection.

The range B7-B15 contains the list of folders:

Change the macro as follows:

Sub CreateFolders()
On Error Resume Next

Dim c As Range

For Each c In Selection

    MkDir c

Next c

On Error GoTo 0
End Sub

The additional code loops through every selected cell and runs MkDir for each.

Select all the cells from B7 to B15, then run the macro.

Add a button to the worksheet

It’s a pain to keep heading back to the Visual Basic Editor to run the code, so let’s add a button to the face of the worksheet.

Add a shape or icon to the worksheet to act as the button.

Right-click the shape, and select Assign Macro… from the menu.

In the Assign Macro dialog box, select the macro, then click OK.

Now, to run the macro, we select the cells with the file paths and click the button.

Your folder-creating application

That’s it. You have built your own tool to create multiple folders.

Let’s generate the folder paths using a formula.

The formula in cell B8 is:

="C:\Examples\Reporting\"&$C$3&"\"&"P"&$F$3&"\"

This creates the folder paths based on cells C3 and F3.

Actually, all the folder paths are built by referencing these cells. So, we can easily update all the folder paths with these cells.

Save the workbook with a .xlsm file extention.

Conclusion

In this post, we’ve seen how to build a simple automation tool to create multiple folders at once.

Going forward, to create multiple folders, it is as simple as opening the workbook, changing a few cells, and clicking the button.

Related Posts

• VBA code to loop through files in a folder (and sub folders)
• VBA code to copy, move, delete and manage files
• VBA code to Zip and Unzip files and folders

---

What next?

Discover how you can automate your work with our Excel courses and tools.