ThreeWave Folder Tree

This page describes VBA code to create a "tree" listing of files and subfolder of a specified folder.
ShortFadeBar

Introduction

This page describes the Directory Tree Builder Add-In for Excel. DirTree will create a tree-like hierarchical list of all the folders and (optionally) files contained within a specified directory. When you load the Add-In, an item with the caption Build Directory Tree is created on your Tools menu. When you click that item, the Directory Tree Configuration screen, described below, will appear. You can set the various options of the tree from the configuration screen.

This add-in will not work in 64-bit Excel. A new compatible version is in the works but not yet ready for distribution. In the interim, you can use DirTree Lite, a trimmed down version of DirTree that will work in either 32-bit or 64-bit Office.

NOTE: There used to be two versions of the Directory Tree file -- an XLS workbook version and an XLA Add-In version. The XLS workbook version is no longer available. Only the XLA Add-In version is available. The decision to remove the XLS version was based on the overhead needed to maintain two code bases.

You can download the XLA file here. The downloadable file is contains the XLA add-in file. Unzip the zip file into the folder of your choice, and then open Excel's Options screen and select Add-Ins. There, click Browse and navigate to the location where the unzipped XLA file resides.

If you want to create a TreeView control that lists the hierarchical structure of a folder and all of its subfolders and files, see the Creating A Folder/File Tree View page.

If you want to view the code in the DirTree Add-In, you can unprotect the VBA Project with the password 'a', simply a lower case 'a' character.

SectionBreak

ShareWare

The DirTree add-in is shareware, which means you can use it for free, but if you like it and find it useful, you should pay the fee. The version available for download on this page is the full version. There are no limitations or restrictions. If you like DirTree, you can pay the modest fee of $25 via PayPal to chip@cpearson.com. Paying for shareware is good karma.

SectionBreak

Example Listing

An small example listing is shown below:

File Listing

SectionBreak

DirTree Main Screen

The main Directory Tree Builder configuration screen is shown below. You display this form by clicking the Build Directory Tree item on the Excel Tools menu. It is on this form that you set the various option for building the directory tree. These option are described below the image.

DirTreeMainScreen

Ident Subfolders
If this item is checked, subfolder names are indented to reflect is position in the hierarchy of folders and files. If this item is not checked, folder and file names will all appear in the same column, named by the Listing Start Cell range.

List Files In Addition To Folders
If t

SectionBreak

Calling DirTree From Other Code

It is not necessary to use the UserForm interface to build the tree. You can automate the process by calling the BeginFolderList procedure directly from your own VBA code:

Sub BeginFolderList(TopFolderName As String, _
                        DestRange As Range, _
                        Indent As Boolean, _ 
                        ListFiles As Boolean, _
                        SystemAndHidden As Boolean, _                        
                        FullPaths As Boolean, _
                        BoldFolders As Boolean, _                        
                        ClearEntireWorksheet As Boolean, _
                        Optional HyperLinkFolders As Boolean = False, _                        
                        Optional HyperLinkFiles As Boolean = False, _
                        Optional FileTypesToInclude As String, _
                        Optional FileTypesToExclude As String, _
                        Optional ClearOnLeftOfResult As Boolean = False, _
                        Optional DisplayFileSize As Boolean = False, _
                        Optional DisplayFolderSize As Boolean = False, _
                        Optional SizeScale As Long = 1, _
                        Optional MaxDepth As Long = 9999, _
                        optional Depth As Long = 0)

where
TopFolderName is the fully qualified folder name for which you want to build the tree,

DestRange is the cell in which the listing should begin,

Indent is either True or False, indicating whether subfolders and files should be indented in the listing,

ListFiles is either True or False indicating whether files should listed. If this is False, only subfolders are listed,

SystemAndHidden is either True or False indicating whether system and hidden files and folders should be included in the listing,

FullPaths is either True or False indicating whether each folder or file should be listed with complete drive and path information,

BoldFolders is either True or False indicating whether folder names should be display in bold font,

ClearEntireWorksheet is either True or False indicating whether the entire worksheet should be cleared prior to building the listing,

HyperlinkFolders is either True or False indicating whether to create hyperlinks for the listed folders,

HyperlinkFiles is either True or False indicating whether the create hyperlinks for the listed files,

FileTypesToInclude is a string containing the file types (extensions) to include in the listing. Only files with one of these extensions will be listed. The file type specification should be a semicolon delimited list of file types. For example, to list only Excel workbook and Word documents, use xls;doc .

FileTypesToExclude is a string containing the file types (extensions) to exclude in the listing. Files with these extensions will not appear in the listing. The file type specification should be a semicolon delimited list of file types. For example, to exclude Excel workbook and Word documents, use xls;doc .

ClearOnLeftOfResult is True or False indicating whether the cells to the left of each file and/or folder name should be cleared. This is useful if you have data or formulas to the right of the tree that you want to preserve.

DisplayFolderSize is True or False indicating whether the size of the folder (in bytes, KB, or MB, as indicated by SizeScale) should be displayed next to the folder names.

DisplayFileSize is True or False indicating whether the size of the file (in bytes, KB, or MB, as indicated by SizeScale) should be displayed next to the file names.

SizeScale is the number by which the actual file size should be divided for display. This should be 1 for bytes, 1024 for KB, or 1048576 for MB.

MaxDepth This is the maximum level of folder the hierarchy tree to process.

Depth The depth of the current folder in the hierarchy.

SectionBreak

User Preference Storage

The last-used settings of the Directory Tree Builder UserForm are in the registry in the key HKEY_CURRENT_USER\Software\Pearson\DirectoryTree.  They are retrieved when the form is loaded and saved when the form is unloaded.

SectionBreak

 

Installation Instructions

The downloadable zip file contains the XLA add-in file. Unzip the file to any directory. Then, in Excel open the Options screen and check Add-Ins. Click Browse and navigate to your unzipped XLA file.

.

You are free to examine and modify the code. The project is password protected to prevent it from opening all its windows at startup in the VBA Editor. The password for the project is 'a'.

This page last updated: 3-January-2013