Folder Tree
This page describes VBA code to create a "tree" listing of files and subfolder of a specified folder.
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.
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.
An small example listing is shown below:
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.
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
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.
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.
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