In this article, we will use a VBA Macro to create folders from an Excel list. Multiple folders and subfolders with various names may occasionally be necessary, but manually generating those directories is time-consuming. What if we build a list of folder and subfolder names and then create all of these folders/subfolders with a single click?
The above video shows newly created folders and subfolders under a main folder named Test Folder.
Method 1 – Using VBA
Suppose we have a list of folder names in an Excel worksheet.
We can create new folders with the same names under a specific Parent folder 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()
- Creates 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
- Asks the user to select a Parent folder under which the new folders will be created. The selected folder’s address is stored in the folderPath string type variable.
Set Rng = Application.InputBox("Select the list of Folder", Type:=8)
- Prompts the user to select the list of folders from the worksheet.
If Len(Dir(folderPath & "\" & Rng(row, clmn), vbDirectory)) = 0 Then
MkDir (folderPath & "\" & Rng(row, clmn))
- 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 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 above.
For clmn = 1 To max_Cols
.
.
Next clmn
- The For loop iterates through each column and executes the Do While Loop 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, then end the subroutine.
Steps:
- Run the code by pressing F5 or clicking the Run button.
A window will open up asking us to select a parent folder.
- Select the folder named “Test Folder”.
- Click on OK.
Another window will open asking to select the list of folders.
- Select the B5:B12 range.
- Click OK.
A message box pops up informing that 8 folders have been created inside the parent folder.
Here is a picture of the newly created folders inside the parent folder in Windows Explorer.
Notes:
In this method, using this comma, we can create folders inside a Parent folder, but cannot create any Subfolders inside newly created folders. To create folders inside newly created folders, apply Method 2 below.
Read More: How to Create Multiple Folders at Once from Excel
Method 2 – Creating Folders and Subfolders
Suppose we have a list of Folders and Subfolders like this:
Based on this list, we want to create a number of Folders (Parent folders) and multiple Subfolders (Child Folders) within them. We can do this by using 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
- Creates 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
- A New Subroutine Parent_child_Folders is 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)
- Asks 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
- Calculates the total number of rows in the selected range Folder_List and stores it in Total_Row. This value will be used as the maximum value of the iteration variable in the For loop.
For i = 1 To Total_Row
- Applies a For loop iterating through i=1 to Total_Row.
Parent = Folder_List.Cells(i, 1).Value
Child = Folder_List.Cells(i, 2).Value
- Sets 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
- Applies multiple If statements. In the first If statement, we check 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 exit the For loop using the Exit For command. Else, if the Child folder does not exist, 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 displays the message “Folders Creation Completed”.
Steps:
- Paste the above code into a new module and run it.
A window will open asking to “Select a Parent folder inside which new folders will be created”.
- Select any Folder and click OK.
Another window will open asking to select the range of the table of the Parent & Child folder.
- Here, you MUST NOT select the header of the table.
- Click OK.
After the creation of all the Folders & Subfolders, a MsgBox will show Folders Creation Completed.
If you navigate inside the selected Folder, you should see all the created Folders and Subfolders.
Method 3 – Using UserForm
We can also use a 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.
To accomplish this, we’ll create a UserForm like this:
The UserForm has 1 TextBox and 3 CommandButtons. Take care with entering the Names of these items, which should be exactly as displayed above.
- Double-click anywhere on the UserForm to open the Code window.
- Paste the following code in 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 calls the Get_Folder() function and returns the Folder address to TextBox1.
Private Sub CommandButton2_Click()
Unload Me
End Sub
- This code will run when CommandButton2 is clicked. It unloads 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
- Determines 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 checks 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
- Determines 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
- A For loop is executed where first it checks whether the folder in a cell of selected range already exists as a Folder or not. If it already exists then the loop exits. 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, a MsgBox shows the number of folders that have been successfully created, then the userform is unloaded.
Now to create the folders.
Steps:
- Select the list from the worksheet.
- Go to the VBA Editor and run the UserForm by clicking anywhere on it and then clicking the Run button.
The UserForm will open.
- Click CommandButton1 (with the name “Click me to select Parent Folder”) to select the Parent Folder.
A File dialog box will open up.
- Select a folder.
The address of the Parent folder should be visible in TextBox1.
- Click on CommandButton3 (with the name Create Folders) to create the Folders.
After Folders creation, a MsgBox like this will be displayed:
If you go to the Parent folder, you should see all the newly created folders and Subfolders inside it.
Read More: How to Create Files From Excel List
How to Delete Folders from a List Using VBA
Deleting folders generally 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 them, this process can be cumbersome. 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
- 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)
- 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
- A For Each loop is applied to iterate through each folder in the selected range, deleting each one.
Suppose we want to delete Folder A, Folder B & Folder C that we have created inside the Test Folder in Example 1.
Steps:
- Run the code.
A dialogue box will open asking to select the Parent folder inside which the folders to be deleted are situated.
- For the demonstration, select Test Folder and click on OK.
Another dialogue box will open asking us to select the list of folders that we want to delete.
- Select the list of folders from the worksheet and click on OK.
The selected folder will be deleted. A confirmation MsgBox will also appear.
If we go to the Test Folder, the selected folders do not exist anymore.
How to Rename Folders From an Excel List Using VBA
Suppose we want to rename a large number of folders, a very tedious task to undertake manually. However, if we 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)
- 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)
- 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
- A For Each loop is applied to iterate through each folder in the selected range, with each one renamed according to its corresponding new name.
Suppose we want to rename the folders created in Example 1. The old names and the new names of the folders are listed in a worksheet like this:
Steps:
- Run the code.
A dialogue box will appear asking to select the folder inside which folders need to be renamed.
- For the demonstration, select Test Folder and click on OK.
Another dialog box will open asking us to select the list of folders that we want to rename.
- Select the list of folders from the worksheet (range B5:B12) and click OK.
As a result, the selected folder will be renamed according to their corresponding new names.
If we go to the Test Folder, all the folders have been renamed accordingly.
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.
- Name the buttons of the UserForm exactly as I have shown in the Figure. Otherwise, the code will not work properly.
Download Practice Workbook
<< Go Back to Create Folder in Excel | Excel Files | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!