How to list all folders and subfolders in Excel?

When working with directory or file management tasks, you may encounter the need to quickly get a comprehensive list of all folders and subfolders within a specific directory and view that information directly in Excel. Unfortunately, Excel does not provide a built-in function or a simple wizard to automatically retrieve all folder or subfolder names from a designated folder path. This article introduces VBA code —that allow you to list all folders and subfolders from any chosen directory right into your worksheet. This minimize the risk of human error and eliminate tedious work, helping you quickly track and manage your data organization.

Use VBA code to list all folders and subfolders

Use VBA code to list all folders and subfolders

If you want to extract and display all folder and subfolder names from a specified directory into Excel, leveraging VBA (Visual Basic for Applications) is an effective solution. This approach provides flexibility and control, allowing you to not only obtain folder names but also additional information such as folder paths, creation dates, and last-modified dates. This method is especially suitable for users comfortable with using Excel's Developer features and those who deal with dynamic or frequently changing file systems.

1. Press ALT + F11 to open the "Microsoft Visual Basic for Applications" editor. This tool allows you to add and edit macros within your workbook.

2. In the VBA editor, click Insert > Module. This will create a new module window. Copy and paste the following code into the module:

VBA code: List all folders and subfolder names

Sub FolderNames()
'Updateby Extendoffice
Application.ScreenUpdating = False
Dim xPath As String
Dim xWs As Worksheet
Dim fso As Object, j As Long, folder1 As Object
With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Choose the folder"
    .Show
End With
On Error Resume Next
xPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
Application.Workbooks.Add
Set xWs = Application.ActiveSheet
xWs.Cells(1, 1).Value = xPath
xWs.Cells(2, 1).Resize(1, 5).Value = Array("Path", "Dir", "Name", "Date Created", "Date Last Modified")
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder1 = fso.getFolder(xPath)
getSubFolder folder1
xWs.Cells(2, 1).Resize(1, 5).Interior.Color = 65535
xWs.Cells(2, 1).Resize(1, 5).EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
Sub getSubFolder(ByRef prntfld As Object)
Dim SubFolder As Object
Dim subfld As Object
Dim xRow As Long
For Each SubFolder In prntfld.SubFolders
    xRow = Range("A1").End(xlDown).Row + 1
    Cells(xRow, 1).Resize(1, 5).Value = Array(SubFolder.Path, Left(SubFolder.Path, InStrRev(SubFolder.Path, "\")), SubFolder.Name, SubFolder.DateCreated, SubFolder.DateLastModified)
Next SubFolder
For Each subfld In prntfld.SubFolders
    getSubFolder subfld
Next subfld
End Sub

3. After inserting the code, press F5 or click the Run button in the VBA editor to execute the macro. A Choose the folder dialog will appear, prompting you to select the target directory you wish to scan for folders and subfolders.

4. Select the desired directory and click OK. The macro will process the directory and generate a new workbook listing all folders and subfolders along with their full paths, immediate parent directory, names, creation dates, and last modification dates as shown below:

This VBA solution is flexible, does not require additional add-ins or external software, and automatically creates an output workbook for your convenience. However, it is best suited for users familiar with basic macro operations, and it might require additional tweaking for specific customizations.

Related article:

How to list files in a directory to worksheet in Excel?