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

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.

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.

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.

Note: Please be careful while running this VBA code. Once a folder is deleted, it’s gone forever and 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, 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.

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.
Name the buttons of the UserForm exactly as I have shown in the Figure. Otherwise, the code will not work properly.

Download Practice Workbook

Create Folders from List with VBA Macro.xlsm

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

Get FREE Advanced Excel Exercises with Solutions!

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

Method 1 – Using VBA

Method 2 – Creating Folders and Subfolders

Method 3 – Using UserForm

How to Delete Folders from a List Using VBA

How to Rename Folders From an Excel List Using VBA

Things to Remember

Leave a reply Cancel reply