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

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:


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

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

    Set home = ActiveSheet.Range("A1")
    Set rng = home
    path.Add (rng.Value)            'Root

    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
        path.Add (rng.Value)

        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)

        If c = 0 Then done = True


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:


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


    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. 5 Alan R Tonn May 13, 2015 at 7:41 pm


    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. 8 Alan R Tonn 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?
    Thank you in advance

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

February 2009
« Jan   Mar »

%d bloggers like this: