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.
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.
- Place any shape by clicking on insert menu from illustrations group.
- Right click on the shape, select assign macro.
- select the macro name(‘sbListAllFolderDetails’) from the available list and click on OK button.
- Now, go to the Developer tab.
- Design Mode should be turned off from the Controls group.
- 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.
- Open VBA Editor window or Press Alt+F11.
- Insert a new module from the Insert menu.
- Copy the above procedure and paste it in the newly created module.
- 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.
Related articles about files and folders:
Please find below link to read more details about files and folders.
Files and Folders: Read More …
Thanks for explaining the concepts to deal with Folders and Subfolders using VBA. It is very helpful.
This is a great help to the beginners like me. Thanks for publishing the code.
How can I also get a list of files in each folder/subfolder?
Hi, thanks for the code. I want a list of a folder and its subfolders but to a certain level. Example: only subfolders two levels down. How do I add this to this code?
Excellent information. Thank you so much!