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.
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.
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.
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.
- 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.
- 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.
- As a result, a message box pops up informing that 8 folders have been created inside the parent folder.
- Here is the picture of newly created folders inside the parent folder in Windows 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.
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.
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.
- 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.
- Finally, after the creation of all the Folders & Subfolders, a MsgBox will show that Folders Creation Completed.
- Now, if you go inside the selected Folder, you should be able to see all those 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.
Now, we need to create a UserForm like this.
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.
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.
- 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.
- Consequently, the UserForm will open. Now, click CommandButton1 (with the name “Click me to select Parent Folder”) to select the Parent Folder.
- As a result, a File dialog box will open up. From there, select a 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.
- After Folders creation, a MsgBox like this will be displayed.
- 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.
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.
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.
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.
- 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.
- As a result, the selected folder will be deleted. A confirmation MsgBox will also appear.
- If we go to the Test Folder, we will see that the selected folders do not exist anymore.
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.
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.
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.
- 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.
- 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.
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
Get FREE Advanced Excel Exercises with Solutions!
