How to make folders from an excel spreadsheet

Written by brian valle
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to make folders from an excel spreadsheet
Use Microsoft Excel to quickly create multiple folders. (Medioimages/Photodisc/Photodisc/Getty Images)

Excel spreadsheets can help you automate the task of creating multiple folders. One way to generate folders is to create a script using data ranges and the batch command "md." Another method is by using a macro through the MkDir function, a component of Microsoft Excel VBA. This offers flexibility, and it is less time-consuming. Only include proper folder names in your spreadsheet for these techniques to work. Folder names should not contain quotation marks, or any of the characters "\," "/," ":," "*," "?," "<," ">" and "|."

Skill level:

Things you need

  • Plain text editor (Notepad, Notepad++ or TextPad)

Show MoreHide


  1. 1

    Arrange your folder names in a single column. Click on the column's header and type "FOLDERNAMES" in the name field beside the formula bar.

  2. 2

    Add a blank column to the left of the "FOLDERNAMES" column by right-clicking the column header and choosing "Insert."

  3. 3

    Highlight the first cell in the blank column. Enter the following text into the formula bar, and copy it to the other blank cells.

    ="md "&FOLDERNAMES

  4. 4

    Open your plain text editor and create a blank document. Copy and paste the code below at the beginning of the file. Replace "C:\Temp" with the complete path to the directory in which you would like to add folders. For example, to add folders to "Songs" inside your "D" drive, use the path "D:\Songs."

    @echo off

    cd "C:\Temp"

  5. 5

    Return to your Excel workbook. Right-click the header of the column containing the "md" formulas and choose "Copy." Go back to your text editor, and paste the values at the end of the file. Your file should now look something like this:

    @echo off

    cd "c:\Temp"

    md foldername1

    md foldername2


  6. 6

    Save the file on your Desktop as "CreateFolders.bat." Go to the Desktop, and run the batch file by double-clicking on its icon. Your folders are now ready for use.

  1. 1

    Open the Excel workbook. Hold the "Alt" key down and press "F8" to open the Macros window.

  2. 2

    Enter "MakeFolders" under "Macro Name," and click the "Create" button. This opens the VBA editor.

  3. 3

    Copy and paste the following macro in the editor:

    Sub MakeFolders()

    Dim Rng As Range

    Dim maxRows, maxCols, r, c As Integer

    Set Rng = Selection

    maxRows = Rng.Rows.Count

    maxCols = Rng.Columns.Count

    For c = 1 To maxCols

    r = 1
    Do While r &lt;= maxRows
        If Len(Dir(ActiveWorkbook.Path &amp; "\" &amp; Rng(r, c), vbDirectory)) = 0 Then
            MkDir (ActiveWorkbook.Path &amp; "\" &amp; Rng(r, c))
            On Error Resume Next
        End If
        r = r + 1

    Next c

    End Sub

  4. 4

    Save your file as a macro-enabled workbook by opening the "File" menu and choosing "Save As." Enter your preferred file name and choose "Excel Macro-Enabled Workbook" beside "Save As File Type." Click the "Save" button.

  5. 5

    Highlight the cells containing the folder names. Hold down the "Alt" key and press "F8" to open the Macros window. Double-click on "MakeFolders." The folders are now ready for use.

Tips and warnings

  • The macro creates folders in the same directory as your active workbook. To create folders in the directory that contains the macro-enabled workbook, replace all instances of "ActiveWorkbook" in the code with "ThisWorkbook."
  • If you are using Microsoft Excel 2007, you may not have access to the Macros window and the "Developer" tab by default. To enable this, click the Microsoft Office orb and choose "Excel Options." Click "Popular" in the Category pane, and choose "Show Developer tab in the Ribbon." Click "OK" to save your changes.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.