View Full Version : Excel VBA for creating Folders

03-18-2011, 08:38 AM

First up, I'll explain background a little. My friend works with a department named as Process Design (Oil and Gas Engineering consultancy). He is someone who always thinks of in terms of improving things around him. Once I had explained him DOS mode of creating folders. You can write a small bat file for bulk folder creating and then press it once. It typically contains commands like:

mkdir C:\Myfolder

A day before yesterday, I received a call from him about the DOS mode procedure. He wanted to create folders in bulk (A complete folder tree). He told me about the manual (and laborious) procedure that people in the company follow. A bit appalling in these times.

Being on this forum, has made me change my viewpoint towards MS-Excel. So I built a small macro for this exact purpose. It does create folders if the entries are made correctly.

Now I want to build a small folder creation application and improve upon the basic code. I want suggestions to work on.

And if it is a good idea and still not 'KB'ed then probably I'd be happy to contribute to a website which has helped me immensely :bow: .

I am attaching the Excel Workbook.

Bob Phillips
03-18-2011, 08:47 AM
What exactly are you after? Code to create the folders in your workbook, or suggestions on how to do it, or suggestions on a design approach, or what?

03-18-2011, 09:29 AM
Thank you Bob for your quick response. I sometimes do sound like sphinx so much so that I forget what I'm after.
I have written the basic code which does it. Suggestions on how to do it and the design approach - describe best what I'm after.

Bob Phillips
03-18-2011, 10:04 AM
The way that I would do it to have a structured list, like this


so as to make it tree-like in its representation,and then build up the directories from their, appending as we go.

But it is only really an alternative, can't say it is the best way because it is a pretty simple requirement in reality.

Kenneth Hobs
03-18-2011, 12:07 PM
You could use Join() to create the full path and then Shell() to DOS to create the folders from the full path. You might need to check for existing drive if that might change.

Shell "cmd /c md " & Join(Range("A" & i).End(xlToRight), "\"), True

As for writing the folder list, I am not sure what you are doing there.

03-18-2011, 01:24 PM
Hi Kenneth,
I can't get that Join function to work.

A slight simplification of the code. I would store the root in a cell (E1) in this code. BTW VBA will not create folders on the C: root directory in Vista/Win7
'Creating Folders
For i = sStart To lLastRow
Fld = Range("E1")
Arr = Cells(i, 1).EntireRow.SpecialCells(xlCellTypeConstants)
If Not IsArray(Arr) Then
Fld = Fld & "\" & Arr
For Each a In Arr
Fld = Fld & "\" & a
End If
MkDir Fld

Kenneth Hobs
03-18-2011, 02:19 PM
Sorry, I usually check what I post.

Dim fp As String
fp = Join(WorksheetFunction.Transpose(WorksheetFunction.Transpose(Range("A" & i, Range("A" & i).End(xlToRight)).Value2)), "\")
Shell "cmd /c md " & fp, True
Of course I left out parts for the loop and i. Some encapsulated quotes may be needed around fp if it has space characters in it.

03-18-2011, 02:41 PM
Very neat Kenneth.
I always new there was a way to create an Array from a Range without looping. Never thought of double trasposition!

03-20-2011, 05:43 AM

Any chance you could a little more explaination for us mere mortals?

I'm still trying to wrap my head around the doubled (or nested?) .Transpose for example



03-20-2011, 07:34 AM
Hi Paul,
The Join function works with an array, not directly with a range of cells. The Double transpose creates an array of values from the cell contents. In this case "\" is added as a separator to create the folder path.
I would probably create a function to do the transformation.

Sub Test()
Dim Path1, Path2
Path1 = "C:\" & Join(Array("Data1", "Data2", "Data"), "\")
Path2 = "C:\" & Join(arr(Range("A1:C1")), "\")
MsgBox Path1 & vbCr & Path2
End Sub

Function arr(Rng As Range)
Dim r
r = Application.Transpose(Rng)
arr = Application.Transpose(r)
End Function

03-20-2011, 08:19 AM
Thank you so much for the kind responses. However, I have a little unrelated question. I had seen Bob's reply but then on there was no email notification. Are the notification settings changed? I thought the thread had gone cold but obviously it wasn't. And I was little occupied on this weekend.

I belong to mere mortals. So I need a bit of time to sink the code in before I put it to use. I will get back with my doubts (if any) and version.

03-20-2011, 09:08 AM
There seems to be a delay in notifications.

Kenneth Hobs
03-20-2011, 09:20 AM
In this method, I inserted a blank row after the last folder in row 10. As I said, I am not sure what you are doing at the end.

The reason that the DOS method is handy is that it will not error if the folder already exists. The mkdir() and FSO methods require that you check for the existing folder or you get a message and it ends. In the DOS method, I also added the ecapsulated quotes to handle cases where space characters exist. I did not check for an existing drive or valid folder name.

Private Sub CreateFolders_Click()
Dim rStart As Integer
Dim cell As Range

On Error GoTo AbortProcess
rStart = 5

'Creating Folders
For Each cell In Range("A" & rStart, Range("A" & rStart).End(xlDown))
If cell.Value2 <> "" And cell.Offset(0, 1).Value2 = "" Then
Shell "cmd /c md " & """" & cell.Value2 & """", vbHide
'Creating Folder at Last Col
Shell "cmd /c md " & """" & _
Join(WorksheetFunction.Transpose(WorksheetFunction.Transpose(Range(cell, cell.End(xlToRight)).Value2)), "\") _
& """", vbHide
End If
Next cell
Exit Sub

If Err.Number <> 0 Then
Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
End If
End Sub

03-21-2011, 02:05 AM
This use this snippet from XLD, works with Excel 2003,2007 on XP, Vista, and Win7MyDir = CStr(Year(Now))
On Error Resume Next
If Dir("C:\" & MyDir) = "" Then
MkDir ("C:\" & MyDir & "\")
End If
On Error GoTo 0
Many thanks to XLD,Jim

Kenneth Hobs
03-21-2011, 05:44 AM
Jwilder1, that method generally works fine for a one level folder. If you do a 2nd level and neither level exists, VBA will not even show a problem since On Error Resume Next was used.

Iterating through each level and checking for the folder is one method that works for both fso.createfolder and mkdir() if you want to stick to those methods. I am lazy and don't like to jump through all of those hoops.