Friday, 4 May 2012

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
Download solution


8 comments:

Anonymous said...

great and thanks!
it helps me a lot!

Anonymous said...

Thanks for sharing. Really helped.

Anonymous said...

This is just brilliant. Thank you very much!

Anonymous said...

Mann...this is Great...really useful..i am looking for this since long time..
Thank you

Patricklepetiti@hotmail.com said...

Very nice tools, would it be possible to add the following options:
1- get only directories and sub-directories but exclude the files names from the list generated.
2- exclude some unwanted directories names.

Anonymous said...

Query:
"I am having one problem with the code that I hope you might be able to tell me how to fix. The code appears to be repeating file names when there are more than one subdirectory"

Solution:

I was able to eliminate the duoplicate names by commenting out the following last section of the Public Sub FileLister code: .......................


'If exploreSubFolder Then
' navigated(nestLevel) = 0
'End If

Curly Workshop said...

Great Spreadsheet
Helps a lot and save heaps of time.
But was wondering if it is possible to have it only record folders and not the files.
Once again thankyou

Joshua said...

Great work and big heart to share. Thank you!