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

Recursion vs. Loops in VBA and SQL

Recursion, when I was first taught about it in our class rooms our professor mentioned, this is one topic which every student ask to repeat once again.

Though after a while in the business I am learning actually it’s not that bad too. But it’s just a concept which has to be used wisely. In the past the context I have used recursion is for file/directory searches and listing. But I was also taught to us in the context of factorial and mathematical concepts which could be achieved via loops.

Thus I am embarking in this post to figure out which is a better one to choose:

With recursion, though our mathematical models can be represented well in comparison to loops programmatically. The concept of recursion has some underlying concerns which also need to be taken into account while designing the programs.

The following concerns are sourced from MSDN (for further details please refer to the detailed MSDN documentation):

1. Limiting condition: This is the most important part of recursion and upon which many times my programs have crashed up. Design the recursion ending condition well for all scenarios of the input, if not you might end up into stack overflow error.

2. Memory Usage/Performance: This is one of the most important considerations to take into account while writing code for recursion. As the function/procedure calls upon itself each time the copy of local variable for each instance of execution is created on stack and the overhead of argument passing has to be taken.

3. Debug: Painful to debug in recursive codes.

So rather stick with Looping?? Well it’s an answer you as developer have to decide which route to take; personally I have used recursion for scenarios of file/directory listing arenas and mostly stick to the simple forms of looping for any other tasks of computations. But your comments are valuable for me in this space .. !!!

Below are the codes to illustrate the use of recursion in VBA and in SQL (too but only limited to 32 levels)

VBA:

Option Explicit

Public Function recursive_fact(n As Integer) As Integer
    If n < 1 Then
        recursive_fact = 1
        Exit Function
    End If
    recursive_fact = n * recursive_fact(n - 1)
End Function

Public Function looping_fact(n As Integer) As Integer
    Dim jCount As Integer
    looping_fact = 1
    For jCount = n To 1 Step -1
        looping_fact = looping_fact * jCount
    Next jCount
End Function

Sub test_functions()
    Dim iCount As Integer
    Dim jCount As Integer
    iCount = 5
    jCount = 5
    iCount = recursive_fact(iCount)
    jCount = looping_fact(jCount)
    Debug.Print "Factorial of iCount: " & iCount
    Debug.Print "Factorial of jCount: " & jCount
End Sub
'--Output--
'Factorial of iCount: 120
'Factorial of jCount: 120

SQL:

--Create a sample table
CREATE TABLE employee(
    id          INTEGER NOT NULL PRIMARY KEY,
    first_name  VARCHAR(10),
    last_name   VARCHAR(10),
    salary      DECIMAL(10,2),
    start_Date  DATETIME,
    region      VARCHAR(10),
    city        VARCHAR(20),
    managerid   INTEGER
 );
--Insert some records into it
INSERT INTO employee VALUES (1, 'Jason' ,  'Martin', 5890,'2005-03-22','North','Vancouver',3);
GO
INSERT INTO employee VALUES (2, 'Alison',  'Mathews',4789,'2003-07-21','South','Utown',4);
GO
INSERT INTO employee VALUES (3, 'James' ,  'Smith',  6678,'2001-12-01','North','Paris',5);
GO
INSERT INTO employee VALUES (4, 'Celia' ,  'Rice',   5567,'2006-03-03','South','London',6);
GO
INSERT INTO employee VALUES (5, 'Robert',  'Black',  4467,'2004-07-02','East','Newton',7);
GO
INSERT INTO employee VALUES (6, 'Linda' ,  'Green' , 6456,'2002-05-19','East','Calgary',8);
GO
INSERT INTO employee VALUES (7, 'David' ,  'Larry',  5345,'2008-03-18','West','New York',9);
GO
INSERT INTO employee VALUES (8, 'James' ,  'Cat',    4234,'2007-07-17','West','Regina',9);
GO
INSERT INTO employee VALUES (9, 'Joan'  ,  'Act',    6123,'2001-04-16','North','Toronto',10);
GO
--Verify the data
SELECT * FROM employee;
GO  
 
--Create the procedure
CREATE PROC usp_FindBoss(
   @EmployeeID int
 )
 AS
 DECLARE @ReportsTo int
 SELECT
   @ReportsTo = managerid
 FROM
     Employee
 WHERE
     Id = @EmployeeID
 IF @ReportsTo IS NOT NULL AND @@NESTLEVEL <= 32
 BEGIN
   SELECT
     @EmployeeID AS Employee,
     @ReportsTo  AS Manager
   EXEC usp_FindBoss
     @ReportsTo
 END
 GO
 
--Execute the procedure
SELECT * FROM employee
EXEC usp_FindBoss 2

Note: For SQL Stored Procedures CAN call Stored Procedures & Functions(Both), but Functions CAN call Functions (Only).

Refrences:
Link1 (MSDN Recursion): http://msdn.microsoft.com/en-us/library/81tad23s%28v=vs.80%29.aspx
Link2: http://stackoverflow.com/questions/660337/recursion-vs-loops