Creating A Folder/File Tree View
This page describes code used to create a Tree View representation of Folder And Files.
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.
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()
Dim TVX As MSComctlLib.TreeView
Dim TopFolderName As String
Dim ShowFullPaths As Boolean
Dim ShowFiles As Boolean
Dim ItemDescriptionToTag As Boolean
ShowFullPaths = False
ShowFiles = True
ItemDescriptionToTag = True
' BROWSE FOR FOLDER CODE
' AVAILABLE AT www.cpearson.com/Excel/BrowseFolder.aspx
TopFolderName = BrowseFolder("Select A Folder For The TreeView")
If TopFolderName = vbNullString Then
MsgBox "Operation Cancelled", vbOKOnly, Me.Caption
Exit Sub
End If
' END OF BROWSE FOR FOLDER CODE
If Dir(TopFolderName, vbDirectory) = vbNullString Then
MsgBox "Folder: '" & TopFolderName & "' does not exist.", vbOKOnly, Me.Caption
Exit Sub
End If
CreateFolderTreeView TVX:=Me.TreeView1, _
TopFolderName:=TopFolderName, _
ShowFullPaths:=ShowFullPaths, _
ShowFiles:=ShowFiles, _
ItemDescriptionToTag:=ItemDescriptionToTag
End Sub
Private Sub btnNodeInfo_Click()
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
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)
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
TVX.Nodes.Clear
Set TopFolder = FSO.GetFolder(folderpath:=TopFolderName)
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
CreateNodes TVX:=TVX, _
FSO:=FSO, _
ParentNode:=TopNode, _
FolderObject:=TopFolder, _
ShowFullPaths:=ShowFullPaths, _
ShowFiles:=ShowFiles, _
ItemDescriptionToTag:=ItemDescriptionToTag
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
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)
Dim SubNode As MSComctlLib.Node
Dim FileNode As MSComctlLib.Node
Dim OneSubFolder As Scripting.Folder
Dim OneFile As Scripting.File
Dim S As String
For Each OneSubFolder In FolderObject.SubFolders
If ShowFullPaths = True Then
S = OneSubFolder.Path
Else
S = OneSubFolder.Name
End If
Set SubNode = TVX.Nodes.Add(relative:=ParentNode, relationship:=tvwChild, Text:=S)
If ItemDescriptionToTag = True Then
SubNode.Tag = "FOLDER" & vbCrLf & OneSubFolder.Path
End If
CreateNodes TVX:=TVX, _
FSO:=FSO, _
ParentNode:=SubNode, _
FolderObject:=OneSubFolder, _
ShowFullPaths:=ShowFullPaths, _
ShowFiles:=ShowFiles, _
ItemDescriptionToTag:=ItemDescriptionToTag
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 following image illustrates the tree view configuration form:
This page last updated: 3-January-2013