Are you tired of manually creating multiple folders on your computer? Do you have a long list of folder names in an Excel sheet that you want to create at once? In this article, we will show you two simple methods of how to create multiple folders at once from Excel. The first method involves using the “MD” function and Notepad, while the second method uses Excel VBA to automate the process.
Using the first method, we will guide you through the process of creating a batch file using the “MD” function and Notepad to create multiple folders at once. The second method involves writing a VBA macro in Excel to automate the process of creating multiple folders with subfolders. By following the step-by-step instructions in this article, you will learn how to save time and effort by creating multiple folders at once from Excel.
Download Practice Book
You can download the free Excel workbook from here and practice independently.
How to Launch Visual Basic Editor in Excel
To Launch Visual Basic Editor, we need to click on Visual Basic under the Developer tab.
Then, Insert a module to write the code. Finally, inside the module, we can write suitable codes to get our desired output. We have to repeat this process for adding each of the new Macros. (If you are using VBA for the first time, you may need to add the Developer tab inside the ribbon in Excel)
How to Create Multiple Folders at Once from Excel: 2 Ways
Let us learn two ways to create multiple folders at once from Excel. And at the end, you can choose either of the two methods based on your convenience.
1. Using “MD” Function and NotePad to Create Multiple Folders at Once
The main folder names are documented inside column B. We need to add the object “MD” before all the folder names. The following are the 2 ways to do that.
Please note that, if you also want to create subfolders inside the main folders, you should follow the first one.
1.1 Adding MD in Cells Using Flash Fill
Inside cell C5, type the following
MD “Benjamin Johnson/Personal Information” “Benjamin Johnson/Complaints” “Benjamin Johnson/Accomplishments”
Select cells C5 to C9. Then, Under the Data tab, select Flash Fill. Selected cells will be filled with corresponding information.
Copy the cells from C5 to C9.
After that, open a text document. And paste the information inside the document.
Save the file by pressing Ctrl+S.
Rename the document as .bat extension.
Double-click on the file. The desired folders will be created inside the same location.
Three subfolders will also be created inside each of the folders.
Read More: How to Make a List within a Cell in Excel (3 Quick Methods)
1.2 Adding MD in Cells Using Formula
Here, instead of using the Flash Fill feature, we will use a formula to add MD before all the folder names. Creating subfolders is not possible in this method.
Inside cell C5, type the following formula and press Enter.
You will see that “MD” will be added to the left of the folder name “Benjamin Johnson” inside cell C5.
Now place the mouse pointer at the bottom right corner of cell C5. Left-click there and drag the pointer up to cell C9. The rest of the folder names will also be accompanied by “MD” right before them.
The rest of the procedures are similar to the previous method stated earlier.
Read More: Create Outlook Folders from Excel List (Step-by-Step Guide)
2. Excel VBA to Create Multiple Folders at Once
It is also possible to create multiple folders at once using Excel VBA. To do that, first, we need to insert a module inside the Visual Basic window under the Developer tab. This process is mentioned at the beginning of the article.
Inside the module, we need to write the following code:
Code Syntax:
Sub CreateFoldersAndSubfolders()
Dim path As String
Dim mainFolderRange As Range
Dim subfolderNames As Variant
Dim i As Integer, j As Integer
'Change this to the desired path
path = "C:\Users\User\Desktop\SOFTEKO\Article 19\"
subfolderNames = Array("Personal Information", "Complaints", "Accomplishments")
'Change this to the range containing the names of the main folders
Set mainFolderRange = Range("B5:B9")
For i = 1 To mainFolderRange.Rows.Count
'Create main folder
MkDir path & mainFolderRange(i, 1).Value
'Create subfolders within main folder
For j = 0 To UBound(subfolderNames)
MkDir path & mainFolderRange(i, 1).Value & "\" & subfolderNames(j)
Next j
Next i
End SubCode Breakdown:
- “Sub CreateFoldersAndSubfolders()” – defines a new VBA subroutine named “CreateFoldersAndSubfolders”.
- “Dim path As String” – declares a string variable named “path” which will store the path where the folders will be created.
- “Dim mainFolderRange As Range” – declares a range variable named “mainFolderRange” which will store the range of cells containing the names of the main folders.
- “Dim subfolderNames As Variant” – declares a variant variable named “subfolderNames” which will store the names of the subfolders as an array.
- “Dim i As Integer, j As Integer” – declares two integer variables named “i” and “j” which will be used as counters in the for-loops.
- “path = “C:\Users\User\Desktop\SOFTEKO\Article 19″” – sets the value of the “path” variable to the desired path where the folders will be created.
- “subfolderNames = Array(“Personal Information”, “Complaints”, “Accomplishments”)” – sets the value of the “subfolderNames” variable to an array of strings containing the names of the subfolders.
- “Set mainFolderRange = Range(“B5:B9″)” – sets the value of the “mainFolderRange” variable to the range of cells containing the names of the main folders.
- “For i = 1 To mainFolderRange.Rows.Count” – starts a for-loop which will iterate through the rows of the “mainFolderRange” variable.
- “MkDir path & mainFolderRange(i, 1).Value” – creates a new folder with the name specified in the current row of the “mainFolderRange” variable at the specified path.
- “For j = 0 To UBound(subfolderNames)” – starts a nested for-loop which will iterate through the elements of the “subfolderNames” array.
- “MkDir path & mainFolderRange(i, 1).Value & “” & subfolderNames(j)” – creates a new subfolder with the name specified in the current element of the “subfolderNames” array inside the current main folder at the specified path.
- “Next j” – ends the nested for-loop.
- “Next i” – ends the main for-loop and completes the subroutine.
- “End Sub” – ends the subroutine.
Finally, run the code. You will see that the desired folders and subfolders are created inside your chosen destination.
The following video makes it easier for you to understand.
Read More: How to Create Folders from Excel List (3 Easy Methods)
Things to Remember
- Make sure to double-check the path where you want to create the folders to avoid any errors.
- Ensure that the folder names do not contain any special characters or illegal characters that are not allowed in folder names.
- Ensure that you have the necessary permissions to create folders at the specified path.
Conclusion
Knowing how to create multiple folders at once from Excel can be a time-saving process for many users. By utilizing either the MD function and Notepad or Excel VBA, you can easily create multiple folders and subfolders with just a few clicks. This process can also help to ensure that your file organization is efficient and well-structured, leading to increased productivity.
If you’re interested in learning more about Excel and its various uses, be sure to check out Exceldemy. They offer a wealth of resources and courses for users of all skill levels, allowing you to take your Excel skills to the next level. Feel free to share your feedback or queries in the comment section below.
Related Articles
- How to Make a Bulleted List in Excel (9 Methods)
- Make Alphabetical List in Excel (3 Ways)
- How to Make a To Do List in Excel (3 Easy Methods)
- Creating a Mailing List in Excel (2 Methods)
- How to Make a Numbered List in Excel (8 Methods)
- Generate List Based on Criteria in Excel (5 Methods)
- Create a Unique List in Excel Based on Criteria (9 Methods)
