### Creating a Directory Structure

I recently wanted to define a fairly complex directory structure, and be able to reproduce it in different locations. It seemed natural to define the structure as an indented list in an Excel worksheet, like this:

Directory structure in a worksheet

Obviously, in a real case, these names would be meaningful.

What we want is to ‘walk’ this tree structure, building up paths like these:

```/A
/A/B1
/A/B1/C1
/A/B1/C2
/A/B2
/A/B2/C3
/A/B2/C4
/A/B2/C4/D1
...```

and create the corresponding directories.

The way we walk the tree is ‘depth first’: keep going down-and-right while there are filled cells; when we get to an empty cell, go left until we find a filled cell:

How we walk the tree

We’ll do this by changing row and column indexes. Cell A1 is (0,0). When we get back to column 0, then we’re done.

Here’s the main procedure:

```Sub TreeWalkVal()
'Tree walker that generates chained name paths
'e.g. /foo/bar/baz
'The first name is that of the root node.
'Assumes that the tree is on Worksheets(1).

Dim r As Integer, c As Integer      'row, column indexes
Dim home As Range
Dim rng As Range                    'the current cell
Dim done As Boolean
Dim path As New List

r = 0
c = 0
done = False

Worksheets(1).Activate
Set home = ActiveSheet.Range("A1")
Set rng = home

Do Until done

Call NodeAction(r, c, rng.Value, path)

r = r + 1
c = c + 1
Set rng = home.Offset(r, c)       'walk down-right

Do While IsEmpty(rng) And c > 0   'walk left
c = c - 1
Set rng = home.Offset(r, c)
path.Remove                   'last element of path
If Not path.IsEmpty Then _
path.SetLast (rng.Value)
Loop

If c = 0 Then done = True

Loop

End Sub```

The path is held as a List of elements. List is a class (defined in a Class Module): I’ll talk about this in another post. With our List, we can add and remove elements to/from the tail of the List.

NodeAction is broken out into a separate procedure, because the tree-walking is quite generic, and we might want to do different actions:

```Sub NodeAction(r As Integer, c As Integer, _
val As String, path As List)
'Action to be performed for each node.
'In this case a directory is created
'for the specified path.
'An existing directory at path throws an error,
'which is ignored.
'There is no directory deletion.

On Error Resume Next

Dim pathstring As String
pathstring = path.ToString

Debug.Print val; " : "; pathstring

If Not path.IsEmpty Then _
MkDir (ActiveWorkbook.path + pathstring)

End Sub```

List defines a constant separator (in this case, “/”), which is used by the List.ToString method.

The On Error Resume Next ignores the ‘directory already exists’ error.

The TreeWalkVal procedure works with incremental additions to the structure, but does not handle deletions (an exercise left for the reader, as they say).

An alternative procedure is TreeWalkNum, which generates paths with positional numbering:

```/1
/1/1
/1/2
/2
/2/1
/2/2
/2/2/1
/2/3
...```

This could be useful for generating nested sections in a document (where these are numbered explicitly, not auto-numbered).

#### 9 Responses to “Creating a Directory Structure”

1. 1 Anghel Mirela March 2, 2010 at 12:13 pm

Hello,

Please help me with this, I have used your code to create a folder structure as in your exaple but it seems that I ave not the list, or object library or I don`t know

can you give me a helping hand?

The path is held as a List of elements. List is a class (defined in a Class Module): I’ll talk about this in another post. With our List, we can add and remove elements to/from the tail of the List.

2. 4 Austin August 23, 2011 at 4:48 pm

example data works, but with my data it does not seem to go to all subdirectories, only some

3. May 13, 2015 at 7:41 pm

Hello

does anyone have a copy of this xls still? I could really use the List Class Module to complete the code…

4. 6 Leszek August 24, 2015 at 5:18 pm

Well I desperately need it but unfortunately links doesn’t work and without a list class is pretty useless…

5. 7 Leszek August 26, 2015 at 2:24 pm

I have rewrite it without List type so I can send you my code…

6. April 27, 2016 at 6:33 pm

send it as a zip attachment to alpha, lima, alpha, november, romeo, tango, oscar, november, november @ hotel, oscar, tango, mike, alpha, india, lima . charlie, oscar, mike

Thanks! 🙂

7. 9 Flávio November 10, 2016 at 4:29 pm

I tried to run this macro has it’s stated here, but it won’t run properly, is it possible to show me one that does?