Today we teach you how to create folders from an Excel list automatically, and for that we’re going to use a simple macro. Macros help us automate tasks that we perform repeatedly. If you had to create around 100 folders, would you do it manually or would you rather have a macro do the work for you?
Show the Programmer tab
To use Excel macros you need to have the Scheduler tab visible inside your ribbon.
If you don’t see this tab inside your ribbon, you must activate it first. To do this, right-click on any of the tabs and choose Customize ribbon from the drop-down menu.
In the box on the right, enable the option Programmer or Developer and click OK.
Check that the tab is included in your ribbon.
Creating the Macro
To create the macro that allows you to create folders from an Excel list follow these steps:
Go to the Programmer tab →clica on the Visual Basic option.
In the window that has been opened, go to the top menu and click on the option Insert → Module.
In the module window paste the following code:
path = InputBox(“Enter the path where you want to create the folders”)
cell = InputBox(“First cell”)
Do While ActiveCell.Value <> “”
MkDir (path & “/” & ActiveCell.Value)
Closes the Visual Basic window. Now the module to create folders is available in your Excel sheet.
Before running the macro
Before you run the macro, note the following:
The macro you just created works to create folders from a column list. If you have the information in a row you can quickly move the rows to columns. We explain how in this tutorial.
Folder names must be in consecutive cells within the column, with no blank cells. If there are any blank cells the macro will stop running and will not finish creating the whole list of folders.
The location where you want to create the folders should not contain folders with names that match the ones you are going to create automatically. If this happens, an error will occur when running the macro.
Run the macro
Now you can start creating folders from an Excel list automatically.
To run the macro go to the Programmer tab → Macros. A pop-up window will appear with the CreateFolders macro, click on the Run button.
Next, you must enter the path and the start cell in the two pop-up windows that will appear.
First, a pop-up window appears asking you to enter the path where you want to create the folders. Enter the path and press Enter.
For example C:\Users\Desktop\Excel
You can copy the route from any location by clicking on the title bar, press CTRL+C to copy and CTRL+V to paste the route into the pop-up window.
A second window will then appear asking you to designate the first cell. Type the designation of the first cell.
In our A2 example and click OK.
Go to the folder and check that all the folders have been created automatically. Depending on how many folders Excel should create, it may take more or less time.
Save the Excel file with the macro
If you want to save the Excel file with the macro in order to be able to use it in other occasions. Go to File → Save as and in file type choose the option Excel Book enabled for macros.
As you have seen, following step by step the tutorial is really easy to create folders from a list of Excel
What happens if you add more names?
This macro simply helps you automate a repetitive task. After running the macro there is no connection between the folders and the Excel file containing the list.
If you change a name in the Excel file after creating the folders, you will only change the Excel file. The folders keep the names under which they were created.
If you add more names to the Excel file after running the macro, you will have to run it again to create the new folders. It is IMPORTANT that you change the designation of the initial cell because if the macro detects that in the specified path there are already folders with the names it should create, it will result in an error.