How to Use Macro to Create Folders from Excel List (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

In this article, I am going to demonstrate how we can use VBA Macro to create folders from an Excel list. Multiple folders and subfolders with various names may occasionally be necessary. Manually generating those directories is time-consuming. What if we build a list of folder and subfolder names and then create all of the mentioned folders/subfolders with a single click? Yes, we can do it effortlessly with the power of VBA by only typing a few lines of code. To learn more, read this article thoroughly.

Newly Created Folders and Subfolders

The above video shows the overview of newly created folders and subfolders under a main folder named Test Folder.


Macro to Create Folders From Excel List: 3 Effective Methods

In this section, we will demonstrate 3 effective methods for creating folders from an Excel list. Let’s explore those methods one by one.


1. Creating Folders Using VBA

Suppose, we have a list of folder names in an Excel worksheet.

Folder List for Creating New Folders

We want to create new folders with the same names under a specific parent folder. We can do that by running the following VBA code.

VBA macro Code to create folders from excel list

Code Syntax:

Sub Make_Folders()
Dim Rng As Range
Dim max_Rows, max_Cols, row, clmn As Integer
Dim folderPath As String
'selecting Folder from File Explorer
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .Title = "Select a folder inside which new folders will be created"
    If .Show = True Then
        folderPath = .SelectedItems(1)
    End If
End With
Set Rng = Application.InputBox("Select the list of Folder", Type:=8)
max_Rows = Rng.Rows.Count
max_Cols = Rng.Columns.Count
For clmn = 1 To max_Cols
row = 1
Count = 0
Do While row <= max_Rows
If Len(Dir(folderPath & "\" & Rng(row, clmn), vbDirectory)) = 0 Then
MkDir (folderPath & "\" & Rng(row, clmn))
Count = Count + 1
On Error Resume Next
End If
row = row + 1
Loop
Next clmn
MsgBox (Count & " Folders Created inside " & folderPath)
End Sub

📌 How Does the Code Work?

Sub Make_Folders()
  • Creating a new sub-routine named Make_Folders.
Dim folderPath As String
'selecting Folder from File Explorer
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .Title = "Select a Parent folder inside which new folders will be created"
    If .Show = True Then
        folderPath = .SelectedItems(1)
    End If
End With
  • This portion of the code asks the user to select a Parent folder under which the new folders will be created. The selected folder’s address is stored in folderPath string type variable.
Set Rng = Application.InputBox("Select the list of Folder", Type:=8)
  • This code prompts the user to select the list of folders from worksheet.
If Len(Dir(folderPath & "\" & Rng(row, clmn), vbDirectory)) = 0 Then
MkDir (folderPath & "\" & Rng(row, clmn))
  • Here, the IF statement checks whether a folder with the same name exists inside folderpath. If it doesn’t then a new folder is created with the same name by using the MkDir function.
Do While row <= max_Rows

.

.

Loop
  • The Do While loop iterates through each row of the list and executes the IF statement explained above.
For clmn = 1 To max_Cols

.

.

Next clmn
  • For loop iterates through each column and executes the Do While Loop explained above.
MsgBox (Count & " Folders Created inside " & folderPath)
End Sub
  • Finally, a MsgBox will be displayed which will inform the user of the total number of created folders and then it will end the subroutine.

Now, follow the steps below to create the listed folder.

Steps:

  • Run the code by pressing F5 or clicking the Run button.

Running VBA Code for Creating Folders From Excel List

  • As a result, a window will open up Asking for selecting a parent folder. Here, we selected a folder named “Test Folder”. Then click on OK.

Selecting Parent Folder

  • After that another window will open asking for selecting the list of folders. In this space, we selected the B5:B12 range and clicked OK.

Selecting List of Newly Created Folders

  • As a result, a message box pops up informing that 8 folders have been created inside the parent folder.

MsgBox Showing Total Number of Newly Created Folders

  • Here is the picture of newly created folders inside the parent folder in Windows Explorer.

Newly Created Folders in the File Explorer

Notes

Here, using this comma, we can only create folders inside a parent folder but cannot create any Subfolders inside newly created folders. to create some folders inside newly created folders we need to apply the second method.

Read More: How to Create Multiple Folders at Once from Excel


2. Create Folders and Subfolders Using VBA

Suppose, we have a list of Folders and Subfolders like this below.

List of Folders & Subfolders

Based on this list, we want to create a number of Folders (Parent folders) and multiple subfolders (Child Folders) inside those folders. To create those folders and subfolders, you can use the following VBA Code.

VBA Code For Creating Folders & SubFolders

VBA Code For Creating Folders & SubFolders

Code Syntax:

Function FolderExists(ByVal directory As String, ByVal folderName As String) As Boolean
    On Error Resume Next
    FolderExists = (Len(Dir(directory & "\" & folderName, vbDirectory)) > 0)
    On Error GoTo 0
End Function
Sub Parent_child_Folders()
Dim folderPath As String
'selecting Folder from File Explorer
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .Title = "Select a Parent folder inside which new folders will be created"
    If .Show = True Then
        folderPath = .SelectedItems(1)
    End If
End With
'Selecting parent Folder and Child Folder List
Dim Folder_List As Range
Set Folder_List = Application.InputBox("Select the table of Parent & Children Folder", Type:=8)
Dim Total_Row As Integer
Total_Row = Folder_List.Rows.Count
Dim Parent, Child As String
For i = 1 To Total_Row
    Parent = Folder_List.Cells(i, 1).Value
    Child = Folder_List.Cells(i, 2).Value
    'Checking If Parent Folder Already Exist or not
    If FolderExists(folderPath, Parent) Then
        'Checking If Child Folder Already Exist or not
        Parent_Path = folderPath & "\" & Parent
        If FolderExists(Parent_Path, Child) Then
        'Exiting For loop
        Exit For
        Else
        MkDir (folderPath & "\" & Parent & "\" & Child)
        End If
    Else
        MkDir (folderPath & "\" & Parent)
        MkDir (folderPath & "\" & Parent & "\" & Child)
    End If
Next i
MsgBox "Folders Creation Completed"
End Sub

📌 How Does the Code Work?

Function FolderExists(ByVal directory As String , ByVal folderName As String) As Boolean
    On Error Resume Next
    FolderExists = (Len(Dir(directory & "\" & folderName, vbDirectory)) > 0)
    On Error GoTo 0
End Function
  • Here I have created a User Defined Function that will return True if a Folder (folderName) already exists inside a parent folder (directory).
Sub Parent_child_Folders()
Dim folderPath As String
'selecting Folder from File Explorer
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .Title = "Select a Parent folder inside which new folders will be created"
    If .Show = True Then
        folderPath = .SelectedItems(1)
    End If
End With
  • Here, a New Subroutine Parent_child_Folders has been created. Then, using the FileDialog  method, a Parent Folder is selected (msoFileDialogFolderPicker) under which all the folders and subfolders will be created.
'Selecting parent Folder and Child Folder List
Dim Folder_List As Range
Set Folder_List = Application.InputBox("Select the table of Parent & Children Folder", Type:=8)
  • Then the user is asked to select the range in the worksheet containing the list of Parent Folders and Child Folders. The range is saved in Folder_List.
Dim Total_Row As Integer
Total_Row = Folder_List.Rows.Count
  • Here, we calculated the total number of rows in the selected range Folder_List and stored it in Total_Row. Later, it will be used as the maximum value of the iteration variable in the For loop.
For i = 1 To Total_Row
  • Applying For loop iterating through i=1 to Total_Row.
Parent = Folder_List.Cells(i, 1).Value
Child = Folder_List.Cells(i, 2).Value
  • Setting the first column as Parent and 2nd column as Child.
'Checking If Parent Folder Already Exist or not
            If FolderExists(folderPath, Parent) Then
        'Checking If Child Folder Already Exist or not
        Parent_Path = folderPath & "\" & Parent
        If FolderExists(Parent_Path, Child) Then
        'Exiting For loop
        Exit For
        Else
        MkDir (folderPath & "\" & Parent & "\" & Child)
        End If
    Else
        MkDir (folderPath & "\" & Parent)
        MkDir (folderPath & "\" & Parent & "\" & Child)
    End If
Next i
  • Here, we applied multiple If statements. In the first If statement, we checked whether the Parent Folder already exists. If it does then we check whether the Child folder already exists or not. If the Child folder exists as well, then we come out of the For loop using the Exit For command. Else, if the Child folder does not exist, then we create the Child folder inside the Parent folder using the MkDir function. On the Other hand, if the Parent folder does not exist then we first create the Parent Folder and then create the Child Folder. This loop will continue until the last row of the selected range.
MsgBox "Folders Creation Completed"
End Sub
  • After completing the For loop, a MsgBox will display the message “Folders Creation Completed”.

Now, to run the code, follow the steps below.

Steps:

  • First, paste the above code into a new module and run it from there. As a result, a window will open asking for “Select a Parent folder inside which new folders will be created”. From here, select any Folder and click OK.

Selecting the Parent Folder under which all The Folders & Subfolders will be created

  • Now, another window will open asking for selecting the range of the table of the Parent & Child folder. Here, you MUST NOT select the header of the table. Then click OK.

Selecting the range of Parent & Child Folders

  • Finally, after the creation of all the Folders & Subfolders, a MsgBox will show that Folders Creation Completed.

MsgBox showing that Folders have been created

  • Now, if you go inside the selected Folder, you should be able to see all those Folders and Subfolders.

Newly Created Folders and Subfolders

Read More: Create Outlook Folders from Excel List


3. Using UserForm to Create Folders & SubFolders

In this example, I am going to show you how we can use an UserForm to create Folders and Subfolders from a list in Excel. Here, the list of Folders and Subfolders should be structured in the following way.

Folder List Structure

Now, we need to create a UserForm like this.

UserForm Structure

Here, we have created a UserForm with 1 TextBox and 3 CommandButtons. Here, While creating the UserForm, you need to be careful about the Name of those items. The Name should be exactly the same as what I have displayed above. Now, double-click anywhere on the UserForm to open the Code window. Then paste the following code on the window.

UserForm VBA Code-1

UserForm VBA Code-2

VBA Code Syntax: 

Private Sub CommandButton1_Click()
TextBox1.Text = Get_Folder()
End Sub
Private Sub CommandButton2_Click()
    Unload Me
End Sub
Function Get_Folder() As String
    Dim Foldr As FileDialog
    Dim Selected_Folder As String
    Set Foldr = Application.FileDialog(msoFileDialogFolderPicker)
    With Foldr
        .Title = "Select the Main Parent Folder"
        .ButtonName = "Take This Folder"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo NextCode
        Selected_Folder = .SelectedItems(1)
    End With
NextCode:
    Get_Folder = Selected_Folder
    Set Foldr = Nothing
End Function
Function Folder_Exists(folder As String) As Boolean
    If Dir(folder, vbDirectory) = "" Then
        Folder_Exists = False
    Else
        Folder_Exists = True
    End If
End Function
Private Sub CommandButton3_Click()
 Dim Slctn As Range
    Dim counter As Integer
    counter = 0
    If Folder_Exists(TextBox1.Text) = False Or TextBox1.Text = "" Then
        MsgBox "You must select a parent folder", vbCritical, "No Folder Found"
        Exit Sub
    End If
    If TypeName(Selection) = "Range" Then
        Set Slctn = Selection
    Else
        MsgBox "Please, select a valid range!", vbCritical, "Invalid Selection"
        Exit Sub
    End If
    For Each cell In Slctn
        If cell.Value = "" Then
            'skip the cell
        ElseIf Folder_Exists(TextBox1.Text & "\" & cell.Value) Then
            'skip the cell, folder already exists
        Else
            MkDir (TextBox1.Text & "\" & cell.Value)
            counter = counter + 1
        End If
    Next cell
    MsgBox counter & " folders successfully created!", vbOKOnly, "Done"
    Unload Me
End Sub

📌How Does the Code Work?

Function Get_Folder() As String
    Dim Foldr As FileDialog
Dim Selected_Folder As String
    Set Foldr = Application.FileDialog(msoFileDialogFolderPicker)
    With Foldr
        .Title = "Select the Main Parent Folder"
        .ButtonName = "Take This Folder"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo NextCode
        Selected_Folder = .SelectedItems(1)
    End With
NextCode:
    Get_Folder = Selected_Folder
    Set Foldr = Nothing
End Function
  • The function Get_Folder prompts the user to select a parent Folder
Private Sub CommandButton1_Click()
TextBox1.Text = Get_Folder()
End Sub
  • This code will run when the CommandButton1 is clicked. It will call the Get_Folder()  function and return the Folder address to the TextBox1.
Private Sub CommandButton2_Click()
    Unload Me
End Sub
  • This code will be run when CommandButton2  is clicked. It will unload the UserForm1.
Function Folder_Exists(folder As String) As Boolean
    If Dir(folder, vbDirectory) = "" Then
        Folder_Exists = False
    Else
        Folder_Exists = True
    End If        
End Function
  • This function will determine whether a folder already exists or not. If the folder exists, then it will return True, otherwise False will be returned.
Private Sub CommandButton3_Click()
 Dim Slctn As Range
    Dim counter As Integer
    counter = 0
    If Folder_Exists(TextBox1.Text) = False Or TextBox1.Text = "" Then
        MsgBox "You must select a parent folder", vbCritical, "No Folder Found"
        Exit Sub
    End If
  • This code will run whenever the CommandButton3 button is clicked.  At first, using the Folder_Exists function it is checked whether the selected Parent Folder already exists or not. If it doesn’t exist then it will show a message to select a valid parent folder.
If TypeName(Selection) = "Range" Then
        Set Slctn = Selection
    Else
        MsgBox "Please, select a valid range!", vbCritical, "Invalid Selection"
        Exit Sub
    End If
  • Then we determine whether the selected range is valid or not.  If it is not a valid range then a message box will be displayed asking to select a valid range.
For Each cell In Slctn
        If cell.Value = "" Then
            'skip the cell
        ElseIf Folder_Exists(TextBox1.Text & "\" & cell.Value) Then
            'skip the cell, folder already exists
        Else
            MkDir (TextBox1.Text & "\" & cell.Value)
            counter = counter + 1
        End If
    Next cell
  • Now a for loop is executed where first it is checked whether the folder in a cell of selected range already exists as Folder or not. If it already exists then the loop exists. If the folder does not exist then it is created using MkDir Function.
MsgBox counter & " folders successfully created!", vbOKOnly, "Done"
    Unload Me
End Sub
  • Finally, MsgBox will show the number of folders that have been successfully created, and then the userform will be unloaded.

Now, to create the folders, follow the steps below.

Steps:

  • Firstly, select the list from the worksheet.

Selecting List from the Worksheet

  • Now, go to the VBA Editor and run the UserForm. To do that, first, click anywhere on the UserForm and then click the Run button.

Running the UserForm

  • Consequently, the UserForm will open. Now, click CommandButton1 (with the name “Click me to select Parent Folder”) to select the Parent Folder.

Clicking CommandButton1 to open File Dialogue Box

  • As a result, a File dialog box will open up. From there, select a folder.

Selecting Parent Folder

  • The address of the Parent folder should be visible on TextBox1. Now, click on CommandButton3 (with the name Create Folders) to create the Folders.

Clicking The 3rd CommandButton to create Folders

  • After Folders creation, a MsgBox like this will be displayed.

Confirmation MsgBox

  • Now, if you go to the parent folder, you should be able to see all the newly created folders and Subfolders inside the parent folder.

Newly Created Folders

Read More: How to Create Files From Excel List


How to Delete Folders from a List in Excel Using VBA

In this section, we will see how we can delete a list of folders in an Excel File using VBA. Deleting folders is super easy as we just need to select them in File Explorer and press the Delete key. However, when we have a large set of folders and we need to delete only a subset of folders from them, it can be cumbersome. In those situations, if you create a list of folders that you want to delete in Excel, you can use the following VBA code to delete all those folders with a single click.

VBA Code for Deleting Folders From a List in Excel Using VBA

Code Syntax:

Sub Delete_Folders()
Dim Folder As Range
Dim folderPath As String 
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .Title = "Select the Parent folder inside which folders to be deleted are situated"
    If .Show = True Then
        folderPath = .SelectedItems(1)
    End If
End With
Set Folder_List = Application.InputBox("Select list of folders to be deleted", Type:=8) 
On Error Resume Next
Count = 0
For Each Folder In Folder_List
    RmDir folderPath & "\" & Folder.Text
    Count = Count + 1
Next Folder
MsgBox Count & " folders deleted"
End Sub

📌 How Does the Code Work?

With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .Title = "Select the Parent folder inside which folders to be deleted are situated"
    If .Show = True Then
        folderPath = .SelectedItems(1)
    End If
End With
  • This portion of the code asks the user to select a Parent folder under which the folders to be deleted are situated. The selected folder’s address is stored in the folderPath string type variable.
Set Folder_List = Application.InputBox("Select list of folders to be deleted", Type:=8) 
  • This code prompts the user to select the list of folders to be deleted from the worksheet.
For Each Folder In Folder_List
    RmDir folderPath & "\" & Folder.Text
    Count = Count + 1
Next Folder
  • In this portion of the code,  A For Each loop is applied to iterate through each folder in the selected range, and then each one is deleted.
  • Now, suppose, we want to delete the Folders Folder A, Folder B & Folder C that we have created inside the Test Folder in Example-1.

Folders to be deleted

To delete the folders, follow the steps.

Steps:

  • First, run the code. As a result, A dialogue box will open and ask to select the parent folder inside which the folders to be deleted are situated. For the demonstration, we selected Test Folder and clicked on OK.

Selecting the Parent Folder

  • Then, another dialogue box will also open and it will ask us to select the list of folders that we want to delete. We selected the list of folders from the worksheet and then clicked on OK.

Selecting the list of folders to be deleted

  • As a result, the selected folder will be deleted. A confirmation MsgBox will also appear.

Deleting Confirmation

  • If we go to the Test Folder, we will see that the selected folders do not exist anymore.

Selected Folders Deleted

Note: Please be careful while running this VBA code. Once a folder is deleted, it’s gone forever and it cannot be recovered by Recycle bin.

How to Rename Folders From an Excel List Using VBA

Suppose we want to rename a large number of folders. manually renaming each folder will be a very tedious task. However, if you make a list of old names and new names we can use the following VBA code to rename all those existing folders with a click of a button.

VBA Code for Renaming Folder

Code Syntax:

Sub Rename_Folders()
Dim Folder As Range
Dim FolderPath As String
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .Title = "Select the Parent folder inside which folders to be Renamed are situated"
    If .Show = True Then
        FolderPath = .SelectedItems(1)
    End If
End With
Set Folder_List = Application.InputBox("Select list of folders to be renamed", Type:=8)
On Error Resume Next
For Each Folder In Folder_List
    Name FolderPath & "\" & Folder.Text As FolderPath & "\" & Folder.Offset(0, 1).Text
Next Folder
End Sub

📌 How Does the Code Work?

Set Folder_List = Application.InputBox("Select list of folders to be renamed", Type:=8)
  • This portion of the code asks the user to select a Parent folder under which the folders to be renamed are situated. The selected folder’s address is stored in the folderPath string type variable.
Set Folder_List = Application.InputBox("Select list of folders to be renamed", Type:=8)
  • This code prompts the user to select the list of folders to be renamed from the worksheet.
For Each Folder In Folder_List
    Name FolderPath & "\" & Folder.Text As FolderPath & "\" & Folder.Offset(0, 1).Text
Next Folder
  • In this portion of the code,  a For Each loop is applied to iterate through each folder in the selected range, and then each one is renamed according to its corresponding new names.
  • Suppose, I want to rename the folders that I created in example 1. The old names and the new names of the folders are listed in a worksheet like this.

List of Folders to Be Renamed

Now follow the steps below to rename all those folders with new names.

Steps:

  • If you run the code at first a dialogue box will appear asking the user to select the folder inside which folders need to be renamed.  For the demonstration, we selected Test Folder and clicked on OK.

Selecting the Parent Folder

  • Then, another dialogue box will also open and it will ask us to select the list of folders that we want to rename. We selected the list of folders from the worksheet (range B5:B12) and then clicked OK.

Selecting the old names

  • As a result, the selected folder will be renamed according to their corresponding new names.
  • If we go to the Test Folder, we will see that all the folders have been renamed accordingly.

Renamed Folders

Note: Please be careful while running this VBA code. Once a folder is renamed, it can not be undone.

Things to Remember

  • In the 3rd Example, you must add “\” at the end of the Folders name in the worksheet. Moreover, a Folder and its SubFolders should be written serially.
  • You must name the buttons of the UserForm exactly as I have shown in the Figure. Otherwise, the code will not work properly.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

That is the end of this article regarding how to use macro to create folders from Excel list. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries. Finally, please visit Exceldemy for more exciting articles on Excel.


<< Go Back to Create Folder in Excel | Excel Files | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aniruddah Alam
Aniruddah Alam

Md. Aniruddah Alam, a graduate of Bangladesh University of Engineering & Technology, Dhaka, holds a BSc.Engg in Naval Architecture & Marine Engineering. Joining Softeko in August 2022 as an Excel and VBA content developer, he has authored 90+ articles, covering fundamental to advanced Excel topics. He actively develops VBA codes for Excel automation and offers solutions in the Exceldemy Forum. Beyond work, he finds leisure in reading books, and taking relaxing walks, showcasing well-rounded blend of professional and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo