Dear Readers,

Many of you were looking for a excel tool same as File Manager – which lists down all the files from each and every folders and subfolders, to list down all the folders and sub-folders under a given Main Folder Path. Therefore rather than replying to you all individually a piece of code, I though of posting an article with a downloadable file which can be used to list all folders and subfolders in your excel sheet in a Hierarchical structure (same as the folders and sub-folders are structured).

List Folders and Sub Folders

VBA Code to list All the Folders and Sub Folders inside a given Main Folder Path

Sub ListAllFoldersAndSubFolders(SourceFolderName As String, isSubFolder As Boolean)

'--- For Example:Folder Name= "C:\Folder Name\" and IsSubfolder Flag as True or false

Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.folder, SubFolder As Scripting.folder
Dim FileItem As Scripting.File

On Error GoTo err
    
    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)

        For Each SubFolder In SourceFolder.Subfolders
            Cells(strtRow, strtCol).Value = SubFolder.Name
            strtRow = strtRow + 1
            If isSubFolder = True Then
                strtCol = strtCol + 1
                ListAllFoldersAndSubFolders SubFolder.Path, isSubFolder
            End If
        Next SubFolder
        ' At the end of the subfolder
        ' set the column back to the
        ' immediate main folder backward
        strtCol = strtCol - 1
err:
 If err.Number <> 0 Then MsgBox err.Description
End Sub

How to call above function or How to use above

As you can see to run above function it requires certain parameter like Main Folder Path and a Boolean flag to say if you want to list all subfolders as well or just the folders which are there under the main folder.
Therefore you need to call this function as shown in the below example:

Public strtRow As Integer
Public strtCol As Integer
Sub CallAboveFunction()
    'set the start row and column for displaying the list
    strtRow = 2
    strtCol = 2
    ListAllFoldersAndSubFolders "C:\Users\vmishra\Documents\Davinci\VISHWA", False
End Sub

FREE Download

Using the above functions and with some formatting, I have created a simple excel tool where you can list all your folders and sub-folders in a hierarchical structure. You can download this file, use it, refer to the code, share it with friends to help. In short you can do anything you want with this excel :D.