Macro to create a folder list including subfolders (VBA)

04/26/202101/29/2023

Oh, where did I save that folder?

Have you ever searched for a folder at work?

If you create several layers of folders, you may not know where to find the folder you want.

In such cases, use the macro to create a folder list.

If you run the macro, it will display a list of folders in a batch on an Excel sheet.

If you’d like to use it for a while, read on.

Macro to create a folder list

Option Explicit
Dim row_i As Long, col As Long

Sub GetFolderList()
'Get a list of folders, including subfolders

    Dim strTopFldr As String
    Dim dlg As Object
    Set dlg = Application.FileDialog(msoFileDialogFolderPicker)
    If Not dlg.Show Then Exit Sub 'Select the target folder.
    strTopFldr = dlg.SelectedItems(1)
    
    row_i = 1: col = 1
    Cells(row_i, col) = Left(strTopFldr, Len(strTopFldr) - 1)
    GetFolderName (strTopFldr)

End Sub

Private Sub GetFolderName(strPath As String)
'Recursively write the folder name on the sheet.

    col = col + 1
    Dim fso As FileSystemObject: Set fso = New FileSystemObject
    Dim fldr As Folder
    With fso
        For Each fldr In .GetFolder(strPath).SubFolders
            'Hidden folders are through.
            If Not fldr.Attributes And Hidden Then
                row_i = row_i + 1
                Cells(row_i, col) = fldr.Name
                Call GetFolderName(fldr.Path)
            End If
        Next
    End With
    
    Set fso = Nothing
    col = col - 1
End Sub

When you run this program, the folder selection screen will appear as shown in the figure above, and when you select a folder, the folder list will be displayed by hierarchy as shown in the figure below.

A brief description of the operation

The following is a brief description of the operation.

If you want to know the details, try running the steps to get a rough idea of how it works.

    Dim strTopFldr As String
    Dim dlg As Object
    Set dlg = Application.FileDialog(msoFileDialogFolderPicker)
    If Not dlg.Show Then Exit Sub 'Select the target folder.
    strTopFldr = dlg.SelectedItems(1)

In this part, we make the user select a folder.

strTopFldr retrieves the full path of the selected folder.

    row_i = 1: col = 1
    Cells(row_i, col) = Left(strTopFldr, Len(strTopFldr) - 1)
    GetFolderName (strTopFldr)

In this example, the selected folder name is output on the sheet.

    With fso
        For Each fldr In .GetFolder(strPath).SubFolders
            'Hidden folders are through.
            If Not fldr.Attributes And Hidden Then
                row_i = row_i + 1
                Cells(row_i, col) = fldr.Name
                Call GetFolderName(fldr.Path)
            End If
        Next
    End With

This part is the heart of the recursion process.

First, we get the collection of subfolders with .GetFolder(strPath).SubFolders.

                row_i = row_i + 1
                Cells(row_i, col) = fldr.Name

Then, output the folder name in this section.

                Call GetFolderName(fldr.Path)

Then we call ourselves (the procedure) with this Call. It is a recursive call.

This call will start processing the subfolders of “the folder you are currently processing".

By repeating this endlessly, a list of subfolders of the selected folder will be created.

As the folder hierarchy gets deeper, the number of columns (cols) increases, but I think it is better to step through this program than to explain it poorly.

I recommend giving it a try.

VBA

Posted by やろまい