Sunday, August 12, 2012

Excel Create Folder Macro Updated

Today I’m going to revisit how to create a folder in Excel. There’s been some good discussion on my earlier post about to use an Excelmacro to automatically create folders. My original version only created folders in the same file where the Excel spreadsheet was saved. After some reader questions and collaboration we’ve created a new version which allows you to browse to the directory location where you would like the VBA macro to automatically create all the folders you have listed and selected in the Excel workbook.

A reminder of how the Excel macro creates folders. Make your list of folders in any column in a worksheet (which does NOT have to be saved like in previous versions). Select the range of names you want to create. Run the macro.

To open a folder browser with an Excel macro we need to create a shell application object using this code:  

Set ShellApp = CreateObject("Shell.Application").BrowseForFolder(0, "Please Choose The Folder For This Project", 0, OpenAt)

I put together a short video showcasing the end result and how the Excel VBA create folder macro should work. Also, if you’re looking to build your own website watch the video to get a 25% off coupon for Host Gator.


The complete code is listed below. Now you can show your bosses and coworkers how to make a folder with Excel. Please join our newsletter for more Excel tips.


Sub Create_Folders()

penAt = "My computer:\"

Set ShellApp = CreateObject("Shell.Application").BrowseForFolder(0, "Please Choose The Folder For This Project", 0, OpenAt)

'Set the folder to that selected. (On error in case cancelled)
On Error Resume Next
BrowseForFolder = ShellApp.Self.Path

'create the folders where-ever the workbook is saved
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 <= maxRows
If Len(Dir(ActiveWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0 Then
MkDir (BrowseForFolder & "\" & Rng(r, c))

On Error Resume Next
End If
r = r + 1
Loop

Next c
End Sub
 

Create Folders.xlsm Download


9 comments:

  1. Nick,

    Thanks a millions for sharing this!

    SPbb

    ReplyDelete
    Replies
    1. No problem - glad to help! Please share with your friends and co-workers.

      Delete
  2. Sub CreateFolders()

    Dim MyFolder As String
    Dim Cell As Range

    'Change the path to your main folder, accordingly
    MyFolder = "C:\Users\Domenic\Desktop\Temp\"

    'Loop through each cell in the selection to create a new folder
    For Each Cell In Selection
    MkDir MyFolder & Cell
    Next Cell

    End Sub

    ReplyDelete
  3. Hi I wonder if you can help me please? I want to create a subfolder, lets say 'Invoices' and I want to create this subfolder in MULTIPLE folders... lets say I have a folder for each client i.e. folder called 'Jameson', folder called 'Arlington', folder called 'Robertson' etc and I need to have inside each of the client folders named above a folder called 'invoices'. Is there a VBA code I can use to automatically create this 'invoices' subfolder inside ALL the above folder names at once? Please let me know! email m.n.alexandrou@gmail.com I signs up to your subscriber list.

    ReplyDelete
  4. How can we create automatically a text file and batch file in excel macro vba

    ReplyDelete
    Replies
    1. hi,
      i found this... i did not test it yet..
      see this video on youtube:

      https://www.youtube.com/watch?v=9J5Lr40GbMs&t=2s

      Delete
  5. This looks like it should work perfectly for what I need.
    However, it is not creating the folders from the cells.
    It will create a new folder, but I have the cells highlighted, and it doesn't do anything with them.

    ReplyDelete
  6. hi,
    i found this... i did not test it yet..
    see this video on youtube:

    https://www.youtube.com/watch?v=9J5Lr40GbMs&t=2s

    ReplyDelete
  7. New template here: https://excelspreadsheetshelp.blogspot.com/2022/04/the-best-free-template-to-automatically.html

    ReplyDelete

I'd love to hear from you!
-Nick