Monday, April 25, 2022

The Best FREE Template to Automatically Create Folders From Excel

One topic I've been discussing for more than ten years, and keep coming back to with improvements, is how to automatically create folders from Excel. I originally shared a simple macro to make folders back in 2011. More recently, I showed how to add hyperlinks back to the newly created folders. But I was never quite happy with these programs. So now I'm back with a better, more powerful, and easy to use template that will instantly create folders for you.

This Excel template to create folders will teach you:

  • How to use a macro to automatically create folders from Excel (duh)
  • How to change the color of a toggle button (and how to use a toggle button in Excel)
  • How to add hyperlinks to folders
  • Conditional formatting to change cell color based on combo box selection
  • How to copy existing folder names into Excel
  • How to duplicate a file structure

create folders from excel file

As I've done with all my recent templates, I try to make them look as clean as possible when the user first opens the sheet so as not to overwhelm them. I like to use yellow to clearly show the cells that require user input. Command buttons make running your macros very easy.

I envision this template being used in two primary ways: to create a set of new folders from scratch (their names manually typed in), or to quickly duplicate an existing file structure.

First, enter the location in the first yellow box where you want the new folders to be created. Next, type the folder names in the column. Or, if you want to duplicate existing folders, select the folders in explorer, hold down shift and right click, copy as Path. Use find and replace to remove the path. See in this example video:

One unique feature about this template is it allows the user to choose from one of four different options about how they want the folders to be named.

You can download this Folder Generator template for free, just enter a 0 here, an examine the VBA macro code yourself:

Quick VBA tip: To change the color of a toggle button once it is clicked (toggled on or toggled off), use this code:

Private Sub ToggleButton1_Click()

    If ToggleButton1.Value = True Then ToggleButton1.BackColor = vbGreen

    If ToggleButton1.Value = False Then ToggleButton1.BackColor = vbWhite

End Sub

Do you find this template to be useful? If so, please let me know in the comments below!