File - Directory (Tree Map) in Excel

Being a software developer and in my quest of achieving an organised methodology for saving my personal files, I many times end up saving same file in multiple locations. Ahh…

So many times we can’t keep track of how our file structure if expanding underlying the cover of multiple nested folders. So, I thought to develop a tool in Excel/VBA which could visually represent me the file directory structure in the form of tree and also could provide me an easy to navigate mechanism for the nodes I wish to explore.

Some thing like this …



This tool request for the root node location to start its parsing and with the condition provided by the user to layout the sub folder (recursively) by a check box you will achieve the above result.

Public Sub FileLister(rootPath As String, exploreSubFolder As Boolean)

    Dim file As Scripting.file, folder As Scripting.folder, subfolder As Scripting.folder

    If fso Is Nothing Then
        Set fso = New FileSystemObject
    End If

    Set folder = fso.GetFolder(rootPath)

    If (folder.SubFolders.Count > 0) And exploreSubFolder Then
    
        For Each subfolder In folder.SubFolders
            
            nestLevel = nestLevel + 1
            ReDim Preserve navigated(nestLevel)
            
            If navigated(nestLevel) = 0 Then
                For Each file In folder.Files
                    'Record files in folder
                    anchorRange.Offset(printRow, nestLevel + 1).Value = file.Name
                    anchorRange.Offset(printRow, 0).Hyperlinks.Add Control.Range("A12").Offset(printRow, 0), file.path, , , "Link"
                    printRow = printRow + 1
                Next file
            End If
            
            anchorRange.Offset(printRow, nestLevel + 1).Value = subfolder.Name
            anchorRange.Offset(printRow, nestLevel + 1).Interior.Color = 10092543  'Light Yellow
            anchorRange.Offset(printRow, 0).Hyperlinks.Add Control.Range("A12").Offset(printRow, 0), subfolder.path, , , "Link"
            printRow = printRow + 1

            navigated(nestLevel) = 1
            FileLister subfolder.path, exploreSubFolder
            nestLevel = nestLevel - 1
            
        Next subfolder
        
    Else
    
        nestLevel = nestLevel + 1
        For Each file In folder.Files
            'Record files in folder
            anchorRange.Offset(printRow, nestLevel + 1).Value = file.Name
            anchorRange.Offset(printRow, 0).Hyperlinks.Add Control.Range("A12").Offset(printRow, 0), file.path, , , "Link"
            printRow = printRow + 1
        Next file
        For Each subfolder In folder.SubFolders
            anchorRange.Offset(printRow, nestLevel + 1).Value = subfolder.Name
            anchorRange.Offset(printRow, nestLevel + 1).Interior.Color = 10092543  'Light Yellow
            anchorRange.Offset(printRow, 0).Hyperlinks.Add Control.Range("A12").Offset(printRow, 0), subfolder.path, , , "Link"
            printRow = printRow + 1
        Next subfolder
        nestLevel = nestLevel - 1
        
        If exploreSubFolder Then
            navigated(nestLevel) = 0
        End If
        
    End If
End Sub

Download:


Download solution