VBA List Folders Subfolders in Directory

Home/VBA Code Explorer/Projects/VBA List Folders Subfolders in Directory
  • VBA List Folders Subfolders in a Directory

Objective: VBA List Folders Subfolders in a Directory helps you to list or find all folders and Subfolders from a Directory. You can list all details of Folders in a Worksheet. So that we can easily have an eye on all the details related to Folders and Subfolders. It includes Folder property details like complete path, short path, folder name, short folder name, file size, folder create date, number of Subfolders, number of files in a Subfolder etc.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

BROWSE ALL TEMPLATES

50+ Project Management Templates Pack
Excel PowerPoint Word

VIEW DETAILS

Advanced Project Plan & Portfolio Template
Excel Template

VIEW DETAILS

Ultimate Project Management Template
Excel Template

VIEW DETAILS

20+ Excel Project Management Pack
Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack
PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack
Word Templates

VIEW DETAILS


VBA List Folders Subfolders in Directory: Project Approach

To List Folders Subfolders in Excel Workbook, minimum one worksheet should be exist (Note: Please keep it in mind workbook always contains minimum worksheet. We can’t delete all worksheets from a workbook). Let me explain the key steps to develop this folders and Subfolders details in a Worksheet project. We are going to write two procedures (sbListAllFolderDetails and sbListAllFolders) and one function (sbBrowesFolder) in one module. Please find step by step instructions to List Folders Subfolders from Directory.

  • Step 1: TurnOff screen update and Events: We are temporarily avoiding screen flickering and events triggering in the application.
  • Step 2: Variable Declaration: We will be declaring required variables and objects which are using in our procedures and function.
  • Step 3: Browse Main folder (Directory): We will browse and select root folder list folders and sub folders from Directory.
  • Step 4: Delete old ‘Folder Details’ Worksheet: Before creating new ‘Folder Details’ Worksheet, we have to check if there is any existing Worksheet with the same name and delete it.
  • Step 5: Add new ‘Folder Details’ Worksheet: Lets add new worksheet, named it as ‘Folder Details’.
  • Step 6: Create Title and Headers : Here we will create main title and headers and then will do formatting to Main title and headers.
  • Step 7: Calling Sub Procedure from Main Procedure: Here we call Sub Procedure to list folders and sub folder details in Worksheet.
  • Step 8: TurnOn screen update and Events: Let’s reset the screen update and events of application.

List Folders Subfolders :Code and explanation for each control

Here is the detailed VBA Code/Macro/Procedure explanation. We are creating two procedures here. The first procedure name is ‘sbListAllFolderDetails’. Now we will discuss about first procedure.

Step 1: Disable Screen Updating is used to stop screen flickering and Disable Events is used to avoid interrupted dialog boxes or popups.

'Disable screen update
Application.ScreenUpdating = False

Step 2: Declaring variables which are using in the entire project.

'Variable Declaration
Dim shtFldDetails As Worksheet
Dim sRootFolderName As String

Step 3: Browse Main Folder or Directory Folder

This VBA code prompts the user to browse for a folder. You can select folder to list files and Subfolder from Directory. Now click on Ok button to continue process. If you click on cancel button, It will display message like “Please select folder to find list of folders and Subfolders” and exit from the Procedure.

'Browse Root Folder
    sRootFolderName = sbBrowesFolder & "\"
    
    'If path is not available, it display message and exit from the procedure
    If sRootFolderName = "\" Then
        MsgBox "Please select folder to find list of folders and Subfolders", vbInformation, "Input Required!"
        Exit Sub
    End If

Function to Browse Folder:

Here is the function to browse Directory folder.

Public Function sbBrowesFolder()
    Dim FldrPicker As FileDialog
    Dim myPath As String
        
    'Browse Folder Path
    Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
    
      With FldrPicker
        .Title = "Browse Root Folder Path"
        .AllowMultiSelect = False
          If .Show <> -1 Then Exit Function
          myPath = .SelectedItems(1)
      End With
 
      sbBrowesFolder = myPath
      If myPath = vbNullString Then Exit Function

End Function

Step 4: Deleting the ‘Folder Details’ Worksheet if it exists in the Workbook. And Display Alerts is used to stop pop-ups while deleting Worksheet.

Check if any worksheet is exit with name ‘Folder Details’. If it exists, delete sheet using delete method.

    'Delete Sheet if it exists
    Application.DisplayAlerts = False
        On Error Resume Next
        ActiveWorkbook.Sheets("Folder Details").Delete
    Application.DisplayAlerts = True

Step 5: Adding a new WorkSheet at the end of the Worksheet. Naming as ‘Folder Details’. And finally it is assigned it to an object (shtFldDetails).

    'Add new Worksheet and name it as 'Folder Details'
    With ThisWorkbook
        Set shtFldDetails = .Sheets.Add(After:=.Sheets(.Sheets.Count))
        shtFldDetails.Name = "Folder Details"
    End With

    'Create object for sheet name
    Set shtFldDetails = Sheets("Folder Details")

Step 6: Create Main Title and Headers

Here we are creating Main Title as ‘Folder and Subfolder details’. Creating header titles and applying format to it. So that it looks neat and clean.

'Main Header and its Format
    With shtFldDetails.Range("A1")
        .Value = "Folder and SubFolder Details"
        .Font.Bold = True
        .Font.Size = 12
        .Interior.ThemeColor = xlThemeColorDark2
        .Font.Size = 14
        .HorizontalAlignment = xlCenter
    End With
    
    With shtFldDetails
        'Merge Header cells
        .Range("A1:H1").Merge
    
        'Create Headers
        .Range("A2") = "Folder Path"
        .Range("B2") = "Short Folder Path"
        .Range("C2") = "Folder Name"
        .Range("D2") = "Short Folder Name"
        .Range("E2") = "Number of Subfolders"
        .Range("F2") = "Number of Files"
        .Range("G2") = "Folder Size"
        .Range("H2") = "Folder Create Date"
        
        .Range("A2:H2").Font.Bold = True
    End With

Step 7: Call Sub Procedure from Main Procedure

The below called sub procedure lists all the details of folders & sub folders. It then uses recursive procedure to loop through all folders and subfolders and the list its properties of folder and it captures all those details to a new Excel sheet called “Folder Details”.

    'Call Sub Procedure
    'List all folders & subfolders
    sbListAllFolders sRootFolderName

Sub Procedure : sbListAllFolders

The second sub procedure name is ‘sbListAllFolders’. Now we will discuss about this second sub procedure which is used in main module.

Sub sbListAllFolders(ByVal SourceFolder As String)
    
    'Variable Declaration
    Dim oFSO As Object, oSourceFolder As Object, oSubFolder As Object
    Dim iLstRow As Integer
            
    'Create object to FileSystemObject
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oSourceFolder = oFSO.GetFolder(SourceFolder)
    
    'Define Start Row
    iLstRow = Sheets("Folder Details").Cells(Sheets("Folder Details").Rows.Count, "A").End(xlUp).Row + 1
    
    'Update Folder properties to Sheet
    With Sheets("Folder Details")
        .Range("A" & iLstRow) = oSourceFolder.Path
        .Range("B" & iLstRow) = oSourceFolder.ShortPath
        .Range("C" & iLstRow) = oSourceFolder.Name
        .Range("D" & iLstRow) = oSourceFolder.ShortName
        .Range("E" & iLstRow) = oSourceFolder.SubFolders.Count
        .Range("F" & iLstRow) = oSourceFolder.Files.Count
        .Range("G" & iLstRow) = oSourceFolder.Size
        .Range("H" & iLstRow) = oSourceFolder.datecreated
    End With
        
    'Loop through all Sub folders
    For Each oSubFolder In oSourceFolder.SubFolders
        sbListAllFolders oSubFolder.Path
    Next oSubFolder
    
    'Autofit content in respective columns
    Sheets("Folder Details").Columns("A:H").AutoFit
    
    'Release Objects
    Set oSubFolder = Nothing
    Set oSourceFolder = Nothing
    Set oFSO = Nothing

End Sub

Step 8: Enabling or TurnOn Screen Update and Events at the end of the project.

   'Enable Screen Update
    Application.ScreenUpdating = True

List Folders Subfolders :Final VBA Module Code(Macro):

Please find the following procedures and Function to List Folders Subfolders project.

First Main Procedure

Option Explicit

Sub sbListAllFolderDetails()
    
    'Disable screen update
    Application.ScreenUpdating = False
    
    'Variable Declaration
    Dim shtFldDetails As Worksheet
    Dim sRootFolderName As String
    
    'Browse Root Folder
    sRootFolderName = sbBrowesFolder & "\"
    
    'If path is not available, it display message and exit from the procedure
    If sRootFolderName = "\" Then
        MsgBox "Please select folder to find list of folders and Subfolders", vbInformation, "Input Required!"
        Exit Sub
    End If
    
    'Delete Sheet if it exists
    Application.DisplayAlerts = False
        On Error Resume Next
        ActiveWorkbook.Sheets("Folder Details").Delete
    Application.DisplayAlerts = True
    
    'Add new Worksheet and name it as 'Folder Details'
    With ThisWorkbook
        Set shtFldDetails = .Sheets.Add(After:=.Sheets(.Sheets.Count))
        shtFldDetails.Name = "Folder Details"
    End With
    
    'Create object for sheet name
    Set shtFldDetails = Sheets("Folder Details")
    
    'Clear Sheet
    shtFldDetails.Cells.Clear
    
    'Main Header and its Format
    With shtFldDetails.Range("A1")
        .Value = "Folder and SubFolder Details"
        .Font.Bold = True
        .Font.Size = 12
        .Interior.ThemeColor = xlThemeColorDark2
        .Font.Size = 14
        .HorizontalAlignment = xlCenter
    End With
    
    With shtFldDetails
        'Merge Header cells
        .Range("A1:H1").Merge
    
        'Create Headers
        .Range("A2") = "Folder Path"
        .Range("B2") = "Short Folder Path"
        .Range("C2") = "Folder Name"
        .Range("D2") = "Short Folder Name"
        .Range("E2") = "Number of Subfolders"
        .Range("F2") = "Number of Files"
        .Range("G2") = "Folder Size"
        .Range("H2") = "Folder Create Date"
        
        .Range("A2:H2").Font.Bold = True
    End With
     
    'Call Sub Procedure
    'List all folders & subfolders
    sbListAllFolders sRootFolderName
    
    'Enable Screen Update
    Application.ScreenUpdating = True
    
End Sub

Second Sub Procedure used in Main Module


Sub sbListAllFolders(ByVal SourceFolder As String)
    
    'Variable Declaration
    Dim oFSO As Object, oSourceFolder As Object, oSubFolder As Object
    Dim iLstRow As Integer
            
    'Create object to FileSystemObject
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oSourceFolder = oFSO.GetFolder(SourceFolder)
    
    'Define Start Row
    iLstRow = Sheets("Folder Details").Cells(Sheets("Folder Details").Rows.Count, "A").End(xlUp).Row + 1
    
    'Update Folder properties to Sheet
    With Sheets("Folder Details")
        .Range("A" & iLstRow) = oSourceFolder.Path
        .Range("B" & iLstRow) = oSourceFolder.ShortPath
        .Range("C" & iLstRow) = oSourceFolder.Name
        .Range("D" & iLstRow) = oSourceFolder.ShortName
        .Range("E" & iLstRow) = oSourceFolder.SubFolders.Count
        .Range("F" & iLstRow) = oSourceFolder.Files.Count
        .Range("G" & iLstRow) = oSourceFolder.Size
        .Range("H" & iLstRow) = oSourceFolder.datecreated
    End With
        
    'Loop through all Sub folders
    For Each oSubFolder In oSourceFolder.SubFolders
        sbListAllFolders oSubFolder.Path
    Next oSubFolder
    
    'Autofit content in respective columns
    Sheets("Folder Details").Columns("A:H").AutoFit
    
    'Release Objects
    Set oSubFolder = Nothing
    Set oSourceFolder = Nothing
    Set oFSO = Nothing


End Sub

Function to Browse Folder

Public Function sbBrowesFolder()
    Dim FldrPicker As FileDialog
    Dim myPath As String
        
    'Browse Folder Path
    Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
    
      With FldrPicker
        .Title = "Browse Root Folder Path"
        .AllowMultiSelect = False
          If .Show <> -1 Then Exit Function
          myPath = .SelectedItems(1)
      End With
 
      sbBrowesFolder = myPath
      If myPath = vbNullString Then Exit Function

End Function

Assign List Folders and Sub Folders from Directory macro to a Shape on the Worksheet:

Here are steps to create above specified project in the workbook.

  1. Place any shape by clicking on insert menu from illustrations group.
  2. Right click on the shape, select assign macro.
  3. select the macro name(‘sbListAllFolderDetails’) from the available list and click on OK button.
  4. Now, go to the Developer tab.
  5. Design Mode should be turned off from the Controls group.
  6. Now, go back to the shape and click on the created shape to see the TOC Worksheet in the Workbook.

List Folders Subfolders Project:Instructions to Execute the Procedure

You can download the below file and see the code and execute it. Or else, you create new workbook and use the above code and test it. Here are the instructions to use above code.

  1. Open VBA Editor window or Press Alt+F11.
  2. Insert a new module from the Insert menu.
  3. Copy the above procedure and paste it in the newly created module.
  4. You can hit F5 key from the keyboard and you can see the ‘Folder Details’ Worksheet at the end of all Worksheets in the workbook.

Download List Folders SubFolders from Directory – Excel VBA Project:

Here is the project workbook macro file to explore yourself.

VBA to List Folders and Subfolders in a Directory

Related articles about files and folders:

Please find below link to read more details about files and folders.
Files and Folders: Read More …

LIMITED TIME OFFER - Get it Now!
Advanced Project Plan Excel Template

 
Related Resource External VBA Reference

3 Comments

  1. Holger October 8, 2017 at 11:13 PM - Reply

    Thanks for explaining the concepts to deal with Folders and Subfolders using VBA. It is very helpful.

  2. Dr. Soni April 21, 2018 at 8:11 PM - Reply

    This is a great help to the beginners like me. Thanks for publishing the code.

  3. BRZ July 11, 2018 at 12:26 AM - Reply

    How can I also get a list of files in each folder/subfolder?

Leave A Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.