Quick Directory Tree

This page describes describes some simple code you can use to make a list of directories and their subdirectories.

Introduction

Neither Excel nor Windows provides a simple way to create a list of folders and their subfolders and the files within the folders. Elsewhere on this site is some long and complicated code to create a directory tree listing, as well as an add-in to automate the process. These provide dozen of options for creating a directory tree. However, what is discussed in this page is some very simple VBA code that you can more easily adapt to your own needs.

The Code

In the code listing below, the CreateFolderTree is the first part of the process. It sets up the options for the list and gets the top-level folder. The second procedure DoOneFolder does the actual creation of the tree. It recurses itself to handle subfolders, subfolder of those subfolders, and so on. There is no limit on the level of nested folders.

The complete code is shown below. You can download a module file with all the code. The code requires a reference to the Scripting library. In VBA, go to the Tools menu, choose References, and scroll down to Microsoft Scripting Runtime Library and check that item.

'==============================================
'==============================================
Sub CreateFolderTree()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' CreateFolderTree
' By Chip Pearson, chip@cpearson.com , www.cpearson.com
' This creates a hierarchical directory listing tree.
' Requires a reference to the Scripting library (Microsoft
' Scripting Runtime).
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim FSO As Scripting.FileSystemObject
Dim FolderName As String
Dim StartFolder As Scripting.Folder
Dim SubF As Scripting.Folder
Dim F As Scripting.File
Dim R As Range
Dim Indent As Boolean
Dim FullPaths As Boolean

Set FSO = New Scripting.FileSystemObject
' Prompt the user for the top level directory.
FolderName = InputBox("Enter the top folder of the directory tree")
' Get out if no file was selected.
If Trim(FolderName) = vbNullString Then
    Exit Sub
Else
    If Dir(FolderName, vbDirectory) = vbNullString Then
        Exit Sub
    End If
End If

Set StartFolder = FSO.GetFolder(FolderName)
' Change the reference held by R to the first cell of the results tree.
Set R = ActiveSheet.Range("A1")
' Indent = True creates an indented list showing the hierarchical nature
' of the folders. Indent = False creates a single column list.
Indent = True
' FullPaths = True lists the fully qualified directory name. FullPaths = False
' lists the directory name only, with no path information.
FullPaths = True
' Start the process
DoOneFolder WhatFolder:=StartFolder, WriteTo:=R, Indent:=Indent, FullPaths:=FullPaths

End Sub
'==============================================
'==============================================
Sub DoOneFolder(WhatFolder As Scripting.Folder, WriteTo As Range, _
        Optional Indent As Boolean = False, _
        Optional FullPaths As Boolean = False)

Dim SubF As Scripting.Folder
If FullPaths = True Then
    WriteTo.Value = WhatFolder.Path
Else
    WriteTo.Value = WhatFolder.Name
End If

' move the target cell down one row
Set WriteTo = WriteTo(2, 1)

For Each SubF In WhatFolder.SubFolders
    ' For every subfolder in WhatFolder, call this
    ' same procedure passing it the file name. This
    ' recursion spans the entire directory tree.
    If Indent = True Then
        Set WriteTo = WriteTo(1, 2)
    End If
    If FullPaths = True Then
        WriteTo.Value = SubF.Path
    Else
        WriteTo.Value = SubF.Name
    End If
        
    ' Call ourself using SubF.
    DoOneFolder WhatFolder:=SubF, WriteTo:=WriteTo, Indent:=Indent, FullPaths:=FullPaths
    
    If Indent = True Then
        Set WriteTo = WriteTo(1, 0)
    End If
Next SubF
End Sub
'==============================================
'==============================================

This page last updated: 2-August-2010.