At work I often find myself having to create multiple folders before beginning a Project. Many others may take the time consuming method of doing this by hand but you can actually save yourself a lot of time by using a simple VBA macro in an Excel Spreadsheet. One method of doing this is to start a new spreadsheet and save it as a macro-enabled workbook in the location where you want to create the multiple folders (such as C:\Work Directory\Parts List)
Next, in column A list all the names of the folders you want to create. Now, hold the "Alt" key down and press "F8" to open the Macros window. Enter "CreateFolders" and click the Create button which will open the VBA editor. You can copy and paste the following code:
Sub CreateFolders()
'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 (ActiveWorkbook.Path & "\" & Rng(r, c))
On Error Resume Next
End If
r = r + 1
Loop
Next c
End Sub
Now all you have to do is highlight the cells and run the macro. Your folders are automatically created just like that! Save the macro and you can use it over and over again, saving you lots of time and impressing your fellow employees.
Hmm, I create a new folder, save it as enabled workbook but when using ALT F8 the 'create' tab is not functioning - what am I doing wrong???
ReplyDeleteIs the button suppressed already? Try typing something in the Macro Name field then try hitting "Create." Hope that helps - let me know!
ReplyDeleteYea, you just saved me countless hours... Thank you!!!
ReplyDeleteHi,
ReplyDeleteI tried this. It says path not found and highlights the following:
MkDir (ActiveWorkbook.Path & "\" & Rng(r, c))
any idea?
Thank you for your help :)
This works great! Except after I run the macro, excel freezes. Any ideas as to why?
ReplyDeleteMake sure your Excel file is saved as a macro-enabled workbook and you have to select the name of the folders in column A in order for it to work. I'm not sure why Excel is freezing after you run it. Maybe your file is corrupted? Have you tried creating a completely new spreadsheet?
ReplyDeleteOK it's work. But can you help me? I want to create file (listname.txt) by using that way?
ReplyDeleteWOW!!!!!!!!! THANK YOU! I LOVE YOU MAN!
ReplyDeleteThis is GREAT! Just a small question: If I do not want to install the folders on the same folder where the workbook is but to a different location for example My documents....
ReplyDeletewhat is the change in code for this?
Thanks in advance
Luke
Actually, I figured this out Nick, just need to change the ActiveWorkbook.path to actual target destination. But now I do have another question:
ReplyDelete- If I need to modify the target destination every time, is there anyway of doing that without going into the code? for example, upon running the macro, a message pop-up coming on to ask where to install?
Luke
Hi Luke. You can add an input box to the beginning where you would type in the destination. Something like this:
DeleteDim strName As String
strName = InputBox(Prompt:="Enter destination.", _
Title:="ENTER YOUR FILE LOCATION", Default:="Your Name here")
Let me know if you need more help than that.
Nick, thanks. Tried this but it still creates the folder in the location where the workbook I am working on is installed.
ReplyDeleteI guess except this addition in the code you suggested, I would need to modify also the lines and point instead of Activeworkbook.path to somewhere else? The key would be that the somewhere else = location selected on the pop=up window? (I am guessing here...)
If Len(Dir(ActiveWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0 Then
MkDir (ActiveWorkbook.Path & "\" & Rng(r, c))
By the way, another question (sorry :-)
1) instead of a popup box where a target path needs to be manually entered, what would it take to initiate a "browser" to navigate and select the location?
Probably insert code like this at the beginning:
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
On Error GoTo 0
To browse to the folder location instead of entering it by hand try this:
DeleteSub 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
Great, thanks for this....
ReplyDeleteYou saved me Hours of work! Thanks!!!!!
ReplyDeleteThere's a newly updated post with video here:
ReplyDeletehttp://excelspreadsheetshelp.blogspot.com/2012/08/excel-create-folder-macro-updated.html
Your AWESOME....This worked perfectly, over 6000 new folders created in less than 5 minutes! My team THANKS YOU!!!
ReplyDeleteNo problem - glad it saved you a lot of time!
DeleteWorks perfectly. Thanks for all the time saved by this.
ReplyDeleteIs there a way to create Folders and Sub-Folders. I'm trying to do this for my music. So I want to show "Beatles" as a folder and then have a macro create the album names under Beatles. So in excel, I have Beatles listed numerous times in column A, and then next to each Beatles entry is the name of the album. It would save an inordinate amount of time if a macro could do that. Is that possible?
ReplyDelete