Creating A Folder/File Tree View

This page describes code used to create a Tree View representation of Folder And Files.

Introduction

You can use the TreeView control to create a tree representing the folders and files under a root folder. The code on this page assumes that you have the TreeView control installed on your system. The code requires the Microsoft Scripting Runtime Library and the TreeView control. In the VBA Editor, go to the Tools menu, choose References, and scroll down to and check the entry for Microsoft Scripting Runtime. Next, insert a new UserForm into your project. From the Insert menu, choose UserForm. On the Toolbox (the panel that displays controls like text boxes and command buttons), right-click and choose Additional Controls. In that dialog, scroll down to and select Microsoft TreeView Control. If this item does not appear in the list, close Excel, go to the Windows Start Menu, choose Run and enter the following:

RegSvr32 C:\Windows\System32\MSCOMCTL.ocx

If you get a message indicating that the control could not be found, you do not have this control on your system and therefore you cannot create a TreeView control. If you get a message indicating success, open Excel and repeat the steps for adding the TreeView control to the Toolbox.

You can download an example workbook containing all the code on this page.

The code uses recursion, a technique in which a procedure calls itself, to build the child elements on the tree. See Recursive Programming for a discussion of recursive programming.

In the code, you must specify the Top Level Folder, which is the folder whose subfolders and files will be listed. This folder will be at the top of the tree. The code as shown below uses the BrowseFolder function in the module modBrowseFolder. The code in that module is not shown below. You can get the code from the Browse Folder page. You must assign the name of the top level folder to the variable TopFolderName in the btnLoadTree_Click procedure in the UserForm's code module. The modBrowseFolder module is included in the downloadable example workbook.

Creating The Form Controls And Code

With the TreeView control added to the ToolBox, click it and draw it on the UserForm to the size desired. Below the TreeView control on the UserForm, add a command button named btnLoadTree, a command button named btnNodeInfo, and a command button named btnClose. In the code module for the UserForm, paste in the following code:

Private Sub btnClose_Click()
    Unload Me
End Sub

Private Sub btnLoadTree_Click()
''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This is the Click event of a button on the UserForm.
' It sets some options and then calls the procedure
' CreateFolderTreeView which actually builds the
' tree.
''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim TVX As MSComctlLib.TreeView
Dim TopFolderName As String
Dim ShowFullPaths As Boolean
Dim ShowFiles As Boolean
Dim ItemDescriptionToTag  As Boolean


'''''''''''''''''''''''''''''''''''''''''''''''''''
' Options
'''''''''''''''''''''''''''''''''''''''''''''''''''

''''''''''''''''''''''''''''''''''''''
' If ShowFullPaths is True, the
' fully qualified folder and file
' names will appear in the tree. If
' ShowFullpaths is False, only the
' name of the folder or file is
' displayed in the tree.
''''''''''''''''''''''''''''''''''''''
ShowFullPaths = False

''''''''''''''''''''''''''''''''''''''
' If ShowFiles is True, files are listed
' below the folder in which they reside.
' If ShowFiles is False, files are not
' listed in the tree and only folders
' are listed.
''''''''''''''''''''''''''''''''''''''
ShowFiles = True
''''''''''''''''''''''''''''''''''''''
' If ItemDescriptionToTag is True, the
' Tag property of each node will contain
' the word "FOLDER" or "FILE" followed
' by vbCrLf followed by the fully qualified
' name of the folder or file. This information
' can be used to identify the folder or file
' to which the node refers.
''''''''''''''''''''''''''''''''''''''''
ItemDescriptionToTag = True


' BROWSE FOR FOLDER CODE
' AVAILABLE AT www.cpearson.com/Excel/BrowseFolder.aspx
' <<< BEGIN BrowseFolder Code
'''''''''''''''''''''''''''''''''''''''''''''''''''
' This example uses the BrowseFolder function
' available at
' http://www.cpearson.com/Excel/BrowseFolder.aspx
' to allow the user to select the top level folder.
' In your code, you can assign an explicit folder
' name to the variable TopFolderName or retrieve
' the folder name using whatever mechanism you want.
' However you do it, TopFolderName must be set
' to the name of an existing folder.
'''''''''''''''''''''''''''''''''''''''''''''''''''
TopFolderName = BrowseFolder("Select A Folder For The TreeView")
If TopFolderName = vbNullString Then
    MsgBox "Operation Cancelled", vbOKOnly, Me.Caption
    Exit Sub
End If
' >>> END BrowseFolder Code
' END OF BROWSE FOR FOLDER CODE

'''''''''''''''''''''''''''
' Ensure the folder exists.
'''''''''''''''''''''''''''
If Dir(TopFolderName, vbDirectory) = vbNullString Then
    MsgBox "Folder: '" & TopFolderName & "' does not exist.", vbOKOnly, Me.Caption
    Exit Sub
End If

'''''''''''''''''''''''''''
' Create the actual tree
' nodes.
'''''''''''''''''''''''''''
CreateFolderTreeView TVX:=Me.TreeView1, _
                    TopFolderName:=TopFolderName, _
                    ShowFullPaths:=ShowFullPaths, _
                    ShowFiles:=ShowFiles, _
                    ItemDescriptionToTag:=ItemDescriptionToTag


End Sub

Private Sub btnNodeInfo_Click()
''''''''''''''''''''''''''''''''''''''''''''''''''
' This displays information about the node.
''''''''''''''''''''''''''''''''''''''''''''''''''

Dim Arr As Variant
Dim SelectedNode As MSComctlLib.Node
Dim S As String

Set SelectedNode = Me.TreeView1.SelectedItem

If SelectedNode Is Nothing Then
    MsgBox "No Node Selected", vbOKOnly, Me.Caption
    Exit Sub
End If

With SelectedNode
    S = .Text & vbCrLf
    If Len(.Tag) > 0 Then
        Arr = Split(.Tag, vbCrLf)
        S = S & "Item Is: " & Arr(LBound(Arr)) & vbCrLf
        S = S & "Refers To: " & Arr(LBound(Arr) + 1) & vbCrLf
        S = S & "Count Of Children: " & CStr(.Children) & vbCrLf
    Else
        S = .Text & vbCrLf
        S = S & "Count Of Children: " & CStr(.Children)
    End If
    MsgBox S, vbOKOnly, .Text
End With

End Sub

Code To Build The Tree

The code that follows can be placed in the UserForm's code module or in a standard code module. It doesn't really matter where the code resides. Paste in the following code:

Public Sub CreateFolderTreeView(TVX As MSComctlLib.TreeView, _
                            TopFolderName As String, _
                            ShowFullPaths As Boolean, _
                            ShowFiles As Boolean, _
                            ItemDescriptionToTag As Boolean)
''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This procedure loads the TreeView with file and folder
' elements.
' Parameters:
' ------------
' TVX: The TreeView control that will be loaded.
'
' TopFolderName: The fully-qualified folder name
' whose contents are to be listed.
'
' ShowFullPaths: If True, the items in the tree will
' display the fully qualified folder or file name. If
' False, only the name, with not path information, will
' be displayed.
'
' ShowFiles: If True, files within a folder will be listed.
' If False, only folders, not files, will appear in the
' Tree listing.
'
' ItemDescriptionToTag: If True, information about the
' file or folder is stored in the Tag property of the
' Node. This information is either the word "FOLDER"
' or "FILE" followed by a vbCrLf followed by the fully
' qualified name of the folder or file.
'
' This code can reside in a standard code module - it
' need not be in the UserForm's code module.
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim TopFolder As Scripting.Folder
Dim OneFile As Scripting.File
Dim FSO As Scripting.FileSystemObject
Dim TopNode As MSComctlLib.Node
Dim S As String
Dim FileNode As MSComctlLib.Node


Set FSO = New Scripting.FileSystemObject
'''''''''''''''''''''''
' Clear the tree
'''''''''''''''''''''''
TVX.Nodes.Clear

Set TopFolder = FSO.GetFolder(folderpath:=TopFolderName)

'''''''''''''''''''''''''''''''''''
' Create the top node of the
' TreeView.
'''''''''''''''''''''''''''''''''''
If ShowFullPaths = True Then
    S = TopFolder.Path
Else
    S = TopFolder.Name
End If
    
Set TopNode = TVX.Nodes.Add(Text:=S)

If ItemDescriptionToTag = True Then
    TopNode.Tag = "FOLDER" & vbCrLf & TopFolder.Path
End If


''''''''''''''''''''''''''''''''''''''''''
' Call CreateNodes. This procedure creates
' all the nodes of the tree using a
' recursive technique -- that is, the code
' calls itself to create child nodes, child
' of child nodes, and so on.
''''''''''''''''''''''''''''''''''''''''''
CreateNodes TVX:=TVX, _
            FSO:=FSO, _
            ParentNode:=TopNode, _
            FolderObject:=TopFolder, _
            ShowFullPaths:=ShowFullPaths, _
            ShowFiles:=ShowFiles, _
            ItemDescriptionToTag:=ItemDescriptionToTag

'''''''''''''''''''''''''''''''''''''''''
' After all the subfolders are built,
' we need to add the folders that
' are directly below the TopFolder.
'''''''''''''''''''''''''''''''''''''''''
If ShowFiles = True Then
    For Each OneFile In TopFolder.Files
        If ShowFullPaths = True Then
            S = OneFile.Path
        Else
            S = OneFile.Name
        End If
        Set FileNode = TVX.Nodes.Add(relative:=TopNode, relationship:=tvwChild, Text:=S)
        If ItemDescriptionToTag = True Then
            FileNode.Tag = "FILE" & vbCrLf & OneFile.Path
        End If
    Next OneFile
End If

''''''''''''''''''''''''''''''''''''''''
' Finally, now that everything has
' been added to the TreeView, expand
' the top node.
'''''''''''''''''''''''''''''''''''''''
With TVX.Nodes
    If .Count > 0 Then
        .Item(1).Expanded = True
    End If
End With
    
End Sub


Private Sub CreateNodes(TVX As MSComctlLib.TreeView, _
    FSO As Scripting.FileSystemObject, _
    ParentNode As MSComctlLib.Node, _
    FolderObject As Scripting.Folder, _
    ShowFullPaths As Boolean, _
    ShowFiles As Boolean, _
    ItemDescriptionToTag As Boolean)
''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This procedure creates the nodes on the Tree. This
' procedure can reside in a standard code module --
' it need not reside in the UserForm's code module.
' This procedure uses a recursive technique to build
' child nodes. That is, it calls itself to build the
' children, children of children, and so on.
'
' Parameters:
' -----------
' TVX: The TreeView controls to which the nodes will
' be added.
'
' FSO: A FileSystemObject used to enumerate subfolders
' and files of FolderObject.
'
' ParentNode: The Node that will be the parent of
' all nodes created by the current iteration of this
' procedure.
'
' FolderObject: The Folder object whose contents we
' are going to list. The TreeView element for
' FolderObject has already been added to the tree.
'
' ShowFullPaths: If True, elements in the tree will
' display the fully-qualified folder or file name. If
' False, only the name of the folder or file will
' appear in the tree. No path information will be
' displayed.
'
' ShowFiles: If True, files within a folder are listed
' in the tree. If False, no files are listed and
' only Folders will appear in the tree.
'
' ItemDescriptionToTag: If True, information about the
' folder or file is stored in the Tag property of the
' node. The infomation is the word "FOLDER" or "FILE"
' followed by a vbCrLF followed by the fully qualified
' name of the folder or file. If False, no information
' is stored in the Tag property.
''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim SubNode As MSComctlLib.Node
Dim FileNode As MSComctlLib.Node
Dim OneSubFolder As Scripting.Folder
Dim OneFile As Scripting.File
Dim S As String

''''''''''''''''''''''''''''''''''''''''''''''''''
' Process each folder directly under FolderObject.
''''''''''''''''''''''''''''''''''''''''''''''''''
For Each OneSubFolder In FolderObject.SubFolders
    If ShowFullPaths = True Then
        S = OneSubFolder.Path
    Else
        S = OneSubFolder.Name
    End If
    
    '''''''''''''''''''''''''''''''''''
    ' Add the node to the tree.
    '''''''''''''''''''''''''''''''''''
    Set SubNode = TVX.Nodes.Add(relative:=ParentNode, relationship:=tvwChild, Text:=S)
    If ItemDescriptionToTag = True Then
        SubNode.Tag = "FOLDER" & vbCrLf & OneSubFolder.Path
    End If
    
    ''''''''''''''''''''''''''''''''''''''''''''''
    ' Recursive code. CreateNodes calls itself
    ' to add sub nodes to the tree. This recursion
    ' creates the children, children of children,
    ' and so on.
    ''''''''''''''''''''''''''''''''''''''''''''''
    CreateNodes TVX:=TVX, _
                FSO:=FSO, _
                ParentNode:=SubNode, _
                FolderObject:=OneSubFolder, _
                ShowFullPaths:=ShowFullPaths, _
                ShowFiles:=ShowFiles, _
                ItemDescriptionToTag:=ItemDescriptionToTag

    '''''''''''''''''''''''''''''''''
    ' If ShowFiles is True, add nodes
    ' for all the files.
    '''''''''''''''''''''''''''''''''
    If ShowFiles = True Then
        For Each OneFile In OneSubFolder.Files
            If ShowFullPaths = True Then
                S = OneFile.Path
            Else
                S = OneFile.Name
            End If
            Set FileNode = TVX.Nodes.Add(relative:=SubNode, relationship:=tvwChild, Text:=S)
            If ItemDescriptionToTag = True Then
                FileNode.Tag = "FILE" & vbCrLf & OneFile.Path
            End If
        Next OneFile
    
    End If

Next OneSubFolder
  
    
End Sub

The Loaded Form

The following image illustrates the tree view configuration form:

This page last updated: 3-January-2013