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).
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
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.