how to create folders from excel list

How to Create Delete and Rename Folders from a List

I started making videos to go along with my post on pivot tables called 101 Advanced Pivot Table Tips And Tricks You Need To Know. I wanted to be a bit organized and have all the related files for each video tip in its own folder. This meant creating 101 new folders and then naming them based on each tip in my post.

Doing this manually would be a very tedious task.

Copy tip title into clipboard.
Open the file explorer and right click to create a new folder.
Paste the tip title name into the folder name
Prefix the folder name with"001 – " because I wanted them to be listed in the file explorer in the same order as the post.
Repeat steps 1 to 4 a hundred more times 🙁

This is not really the kind of task I find fun, and usually prompts me to think "how can I avoid doing this?".

Since I already had my tip titles listed in an Excel sheet, this was the perfect task for a little bit of VBA.

Creating the Folders from a List

I set up a named range in my sheet called MakeFolderPath as an input for a folder path. This is where you can put the location you want to create all your folders.

I also set up a 1 column table and named it MakeFolderNames. This table will contain the names of all the folders I want to create.

Creating named ranges and tables for our inputs will just allow us to move these around on the sheet without the need to update a reference in the VBA.

          Sub MakeFolders()  Dim Folder As Range Dim FolderPath As String      FolderPath = Range("MakeFolderPath").Value     On Error Resume Next     For Each Folder In Range("MakeFolderNames[Folder Name]")     MkDir FolderPath & "\" & Folder.Text Next Folder  End Sub

It's a simple bit of VBA. It loops through each item in our list of folder names and creates a folder with that name using the MkDir command. It also skips over any errors that might occur due empty cells in our list, the folder already existing or special characters that aren't allowed in a folder name.

Deleting Folders from a List

Deleting all folders in a directory isn't hard, you just highlight them all and press Delete. But if you have a large number of folders and you only want to delete a subset of those, it can be time consuming to delete them. You would need to individually select and delete folders in this case.

If you have a list of the subset of folders, then we can use VBA to delete them in a similar way to how we created folders from a list.

The named range to read the path is called DeleteFolderPath and the table containing my list of folder names to delete is called DeleteFolderNames.

          Sub DeleteFolders()  Dim Folder As Range Dim FolderPath As String      FolderPath = Range("DeleteFolderPath").Value      On Error Resume Next      For Each Folder In Range("DeleteFolderNames[Folder Name]")     RmDir FolderPath & "\" & Folder.Text Next Folder  End Sub

This will loop through and delete any folders in my list using the RmDir command. It will skip any errors from folders not existing or empty cells in the list.

Be careful though, you won't be able to undo this. Once it's gone, it's gone!

Renaming Folders from a List

You might already have the folders set up but need to rename them to some new naming convention. When I was setting up my folders, I have the forethought to prefix them with a number including leading zeros so that sorting them alphabetically arranged them in the order I wanted them to appear.

The named range to read the path is called RenameFolderPath and the table containing my list of folder names and their corresponding new name is called RenameFolderNames.

          Sub RenameFolders()  Dim Folder As Range Dim FolderPath As String      FolderPath = Range("RenameFolderPath").Value      On Error Resume Next      For Each Folder In Range("RenameFolderNames[Original Name]")     Name FolderPath & "\" & Folder.Text As FolderPath & "\" & Folder.Offset(0, 1).Text Next Folder  End Sub

We use a similar structure of VBA with the Name As command to rename our folders. We just need to reference our New Name and we can do that using an Offset from the Original Name since it is one column to the right of the Original Name.

Again, there's no undo here, so be careful!

About the Author

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.