Skip to main content

Create multiple folders and subfolders from a list of data in Excel

Author: Xiaoyang Last Modified: 2024-03-01

Suppose you have a list of staff names within a worksheet range and aim to create individual folders for each to store their information. Manually creating each folder can be time-consuming. However, there are efficient methods to expedite this process. In this guide, I'll share several approaches to quickly generate folders based on the specified cell values.

Create folders based on cell values

Create folders and subfolders based on cell values with VBA code


Create folders based on cell values

In this section, we'll explore various methods in detail, offering comprehensive step-by-step instructions to swiftly and effortlessly create folders based on a list of cell values.

Create folders from a list by using MD command and Notepad

Turning a list from Excel into folders using the MD command and Notepad is a smart trick that mixes easy batch scripting with Excel's knack for keeping things organized. This method is great for making lots of folders quickly, without having to do it all by hand. Here's a step-by-step guide to accomplish this task:

Step 1: Use the MD command to create formulas

Copy or enter the following formula into a blank cell next to your first cell value (B1, for instance), and then, drag the fill handle down to apply the formula to all your list items.

="MD "&A1

Step 2: Copy and paste the formulas into a Notepad file

  1. Press Ctrl + C to copy the cells with the MD command formula.
  2. Open Notepad and press Ctrl + V to paste the commands into a new file.

Step 3: Save the Notepad file as .bat file

Click Save as from the File tab in the Notepad, in the Save as dialog box, choose a directory where you want to create multiple folders, and then give a name for this file with a .bat extension. Finally, click Save button. See screenshot:

Step 4: Double click the .bat file to generate multiple folders

  1. Close the Notepad file, navigate to the folder you previously save the .bat file.
  2. Now, witness the magic: double-click on the file, and you will see multiple folders being created all at once. See the demo below:
 

Create folders from a list by using powerful tool – Kutools for Excel

With the powerful Kutools for Excel’s Create Folders from Cell Contents feature, you can now easily and quickly create folders from an Excel list. But it doesn't stop at just basic folders; Kutools also allows you to create complex structures with multi-level subfolders in one go. Just a few simple steps can transform data from Excel into an organized folder system, significantly boosting your productivity.

Note: If you want to use this Create Folders from Cell Contents feature, please download and install Kutools for Excel first.

After installing Kutools for Excel, please click Kutools Plus > Import / Export > Create Folders from Cell Contents to open the Create Folders from Cell Contents dialog box:

  1. Select the cell values you want to create folders based on;
  2. Then, click the button to specify the destination folder you want to save the folders;
  3. Finally, click OK button.

Result:

Kutools will process the list from your sheet and create a folder for each entry in the specified destination. Navigate to the destination folder to see the result. See screenshot:

Tips:
  1. This useful feature also can help to create folders along with their subfolders as you need. To do this, you should enter the desired folder and subfolder names into the cells, using the backslash sign (\) to separate each level. The contents of each cell will act as a guide for setting up the desired structure of folders and subfolders.

    Then, apply the Create Folders from Cell Contents feature, all folders along with their subfolders will be created successfully. See screenshot:
  2. To apply this feature, please download and install Kutools for Excel first.
 

Create folders from a list by using VBA code

Using VBA code in Excel can transform the tedious task of creating folders from a list into a quick, automated process. This section will show you how to apply VBA code to generate folders.

Step 1: Open the VBA module editor and copy the code

  1. Hold down the ALT + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
  2. Click Insert > Module, and paste the following code in the Module Window.
    VBA code: Create folders based on a list of cell values
    Sub CreateFoldersFromSelection()
    'Updateby Extendoffice
        Dim FolderPath As String
        Dim Cell As Range
        Dim SelectedRange As Range
        Dim FolderName As String
        On Error Resume Next
        Set SelectedRange = Application.InputBox("Select the range with folder names", "Kutools for Excel", Type:=8)
        If SelectedRange Is Nothing Then Exit Sub
        On Error GoTo 0
        
        With Application.FileDialog(msoFileDialogFolderPicker)
            .Title = "Select the destination Folder"
            .AllowMultiSelect = False
            If .Show <> -1 Then Exit Sub
            FolderPath = .SelectedItems(1) & "\"
        End With
        
        For Each Cell In SelectedRange
            FolderName = FolderPath & Cell.Value
            If Cell.Value <> "" And Not FolderExists(FolderName) Then
                MkDir FolderName
            End If
        Next Cell
    End Sub
    
    Function FolderExists(ByVal Path As String) As Boolean
        On Error Resume Next
        FolderExists = (GetAttr(Path) And vbDirectory) = vbDirectory
        On Error GoTo 0
    End Function
    

Step 2: Execute the code

  1. After pasting this code, please press F5 key to run this code. In the prompt box, select the cell values you want to create folders from. And then, click OK.
  2. Then, in the following Select the destination Folder window, specify the destination path to output the created folders. And then, click OK button, see screenshot:

Result:

After executing the VBA code, head over to the destination directory to view the outcome. There, you will find the newly created folders, each corresponding to an item from your Excel list. see screenshot:

Tips:
  1. If there are duplicate entries in the cells, running the code will result in only one folder being created for those duplicates.
  2. If you find yourself using this code frequently, consider saving your workbook in Excel Macro-Enabled workbook format. This action preserves the code within the workbook, allowing you to directly execute it in future without the need to re-enter or re-import the code.

Create folders and subfolders based on cell values with VBA code

Occasionally, you might find yourself in a situation where you need to generate not just folders, but also their corresponding subfolders, all based on the data within Excel cells. To achieve this task, here, I will introduce a VBA code.

Step 1: Prepare the data

First, you should enter the data as following screenshot shown, place the main folder names in the first column and the names for the subfolders in the second column.

Step 2: Open the VBA module editor and copy the code

  1. Hold down the ALT + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
  2. Click Insert > Module, and paste the following code in the Module Window.
    VBA code: Create folders and subfolders based cell values
    Sub CreateFoldersAndSubfoldersWithUserInput()
    'Updateby Extendoffice
        Dim Rng As Range
        Dim Cell As Range
        Dim basePath As String
        Dim fldrPicker As FileDialog
        Dim FolderPath As String, subfolderPath As String
        On Error Resume Next
        Set Rng = Application.InputBox("Select the range of cells (two columns: one is folder column, another s subfolder column):", "Kutools for Excel", Type:=8)
        If Rng Is Nothing Then Exit Sub
        On Error GoTo 0
        Set fldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
        With fldrPicker
            .Title = "Select the Base Folder Path"
            .AllowMultiSelect = False
            If .Show <> -1 Then Exit Sub
            basePath = .SelectedItems(1)
        End With
        If Right(basePath, 1) <> "\" Then basePath = basePath & "\"
        For Each Cell In Rng.Columns(1).Cells
            If Not Cell.Value = "" Then
                FolderPath = basePath & Cell.Value
                If Not FolderExists(FolderPath) Then MkDir FolderPath
                If Not Cell.Offset(0, 1).Value = "" Then
                    subfolderPath = FolderPath & "\" & Cell.Offset(0, 1).Value
                    If Not FolderExists(subfolderPath) Then MkDir subfolderPath
                End If
            End If
        Next Cell
    End Sub
    
    Function FolderExists(FolderPath As String) As Boolean
        On Error Resume Next
        FolderExists = (GetAttr(FolderPath) And vbDirectory) = vbDirectory
        On Error GoTo 0
    End Function
    

Step 3: Execute the code

  1. After pasting this code, please press F5 key to run this code. In the prompt box, select the cell values you want to create folders from. And then, click OK.
  2. In the following popped-out window, specify the destination path to output the created folders. And then, click OK button, see screenshot:

Result:

After executing the VBA code, go to the destination directory to view the outcome. You will find that the folders and their respective subfolders, as dictated by the cell values, have been successfully created as following screenshot shown:

Tips:
  1. This code is only available to create only the main folders and their first-level subfolders.
  2. If you find yourself using this code frequently, consider saving your workbook in Excel Macro-Enabled workbook format. This action preserves the code within the workbook, allowing you to directly execute it in future without the need to re-enter or re-import the code.

Related Articles:

  • List all folders and subfolders in Excel
  • Have you ever suffered with this problem that list all folders and subfolders from a specified directory into a worksheet? In Excel, there is no a quick and handy way to get the name of all the folders in a specific directory at once. To deal with the task, this article may help you.
  • Copy or move files from one folder to another based on a list
  • If you have a list of file names in a column in a worksheet, and the files locate in a folder in your computor. But, now, you need to move or copy these files which names are listed into the worksheet from their original folder to another one as following screenshot shown. How could you finish this task as quickly as you can in Excel?
  • Rename multiple files of a folder
  • May be most of us are suffered with this problem that we need to rename multiple files in a folder, to rename the filenames one by one will make us crazy if there are hundreds or thousands files in that folder. Are there any good functions for us to deal with this task?
Comments (63)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
níže CZ verze

EN:

A better way to do this in a few seconds is to use cmd (.bat file)

If you have a list of names in excel, add the word MKdir in front of the name (folder name) and if it contains a space put the name in quotes. Then just copy it to notepad, save as and add the .bat extension. Once you have this, substitute the .bat file in the folder where it wants to be created and you're done.

If you want the cmd not to close write at the end of the puase like below

Here is the 3 word code *5* :

start
________
MKdir "Pixie Pin"

pause
________
end


this creates a folder named Pixie Pin in the folder where the command was run

CZ:

Lepší způsob jak to udělat během par sec. je použít cmd (.bat soubor)

Pokud máte seznam jmen v excelu, doplňte pomocí vzorečku slovo MKdir před jmeno (název složky) a pokud obsahuje mezeru dejte název do uvozovek. Poté stačí jen zkopírovat do oznámkového bloku (NotePad), dát uložit jako a dopsat příponu .bat . Jakmile toto máte, supsťte .bat soubor ve složce kde chce aby se vytvořili a máte to.

Pokud chcete aby se cmd nezavřelo napište na konec puase jako je níže

Zde je ten 3 slovný kód *5* :

start
________
MKdir "Pixie Pin"

pause
________
konec


toto vytvoří složku s názvem Pixie Pin ve složce kde byl příkaz spuštěn
This comment was minimized by the moderator on the site
This worked really well, even for someone with zero experience with VBA :-)
Would it be possible to adapt the macro or extend the macro to also create hyperlinks to the folders in the selected cells?
So for instance, Cell A3 is selected and you run the macro and the folder is created. Would it be possible to make cell A3 a hyperlink to the folder by expanding on the macro instead of doing that manually?
This comment was minimized by the moderator on the site
Hello, Marloes
To create hyperlinks for the cell values, the following vba code may help you:

First, please select the cell values, and then run this code, and select a folder for outputting the folders.

Sub MakeFoldersAndAddHyperlinksWithFolderSelection()
    Dim Rng As Range
    Dim maxRows, maxCols, r, c As Integer
    Dim folderPath As String
    Dim baseFolderPath As String
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    
    With fd
        If .Show = -1 Then
            baseFolderPath = .SelectedItems(1) & "\"
        Else
            MsgBox "No folder selected. Operation Cancelled."
            Exit Sub
        End If
    End With
    
    Set Rng = Selection
    maxRows = Rng.Rows.Count
    maxCols = Rng.Columns.Count
    
    For c = 1 To maxCols
        For r = 1 To maxRows
            folderPath = baseFolderPath & Rng.Cells(r, c).Value
            If Len(Dir(folderPath, vbDirectory)) = 0 Then
                MkDir folderPath
                On Error Resume Next
                ActiveSheet.Hyperlinks.Add Anchor:=Rng.Cells(r, c), Address:=folderPath, TextToDisplay:=Rng.Cells(r, c).Value
                On Error GoTo 0
            End If
        Next r
    Next c
End Sub


Please have a try, thank you!
This comment was minimized by the moderator on the site
please, i need that same macro but instead of saving them as folders, i need it to save as Excels.
This comment was minimized by the moderator on the site
is it possible to introduce a condition where if that condition is met the module can create 2 folders (each using a different path)?
if the first list of folders is in the A column then the condition occurs in the U column. The conditional criteria is whether the cell is empty or not.
if the condition is not met the module only makes one folder based on the selection.
This comment was minimized by the moderator on the site
Hi, a_c, sorry I have not found a method can solve this job yet.
This comment was minimized by the moderator on the site
Thank you very much
This comment was minimized by the moderator on the site
Thanks a lot! Your VBA code is really super
This comment was minimized by the moderator on the site
Is it possible to import data from a word to excel on colors algorythme? So, I spell the cities with red and countries with blue in a word, and the to import only these to excel. I don’t know if I made myself clear. Thanks
This comment was minimized by the moderator on the site
Thank you, this has saved me literally days of work.
This comment was minimized by the moderator on the site
Hello,


For the following code it shows error in

MkDir (ActiveWorkbook.Path & "\" & Rng(r, c))



It says Runtime error 76 path not found



Can someone please help me with this?

There are no unsupported characters in the file path.
Not sure what could be the problem

Thanks for the help!
This comment was minimized by the moderator on the site
thank you , time saved
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations