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
Nick,
ReplyDeleteThanks a millions for sharing this!
SPbb
No problem - glad to help! Please share with your friends and co-workers.
DeleteSub CreateFolders()
ReplyDeleteDim 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
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.
ReplyDeleteHow can we create automatically a text file and batch file in excel macro vba
ReplyDeletehi,
Deletei found this... i did not test it yet..
see this video on youtube:
https://www.youtube.com/watch?v=9J5Lr40GbMs&t=2s
This looks like it should work perfectly for what I need.
ReplyDeleteHowever, 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.
hi,
ReplyDeletei found this... i did not test it yet..
see this video on youtube:
https://www.youtube.com/watch?v=9J5Lr40GbMs&t=2s
New template here: https://excelspreadsheetshelp.blogspot.com/2022/04/the-best-free-template-to-automatically.html
ReplyDelete