VBA: Read out folder and directory names and list in Excel

Let’s say you don’t have many folders to create with VBA and Excel, but the opposite case. That is, there is a directory with quite many subfolders on a drive and for some reason you need these subdirectories in the form of a list to process them further.

Initial situation: many subdirectories need to be transferred to excel sheet

A fictitious use case: You have many documents that you need to hand over to someone or another company. These documents are structured in many individual folders. Now you want to document the progress and status of the document transfer in the form of an Excel list. It would be tedious and inefficient to type each folder name individually into the Excel spreadsheet. Such a process must be automated. This is best done using VBA. So that is the initial situation.

Macro for reading out the subfolders

To read out all the directory names, insert the following macro in the Visual Basic editor.

Option Explicit
Public Sub ListFolder()
Dim objFSO As Object
Dim folder As Object
Dim strPfad As String
Dim subFolder As Object, colSubfolders As Object
Dim i As Integer
    'Replace path here to the directory to be read out
    strPfad = "C:\Users\DeinUserName\Desktop\temp\ImageFolder"
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set folder = objFSO.GetFolder(strPfad)
    Set colSubfolders = folder.Subfolders
    'Set start line "1" for Excel here
    i = 1
    For Each subFolder In colSubfolders
        i = i + 1
        Range("A" & i).Value = subFolder.Name
    Next subFolder
    Set folder = Nothing
    Set colSubfolders = Nothing
    Set objFSO = Nothing
End Sub

The macro should preferably be inserted in the corresponding table in which the subfolders are to be listed. If you add the macro to the workbook, you still have to specify which table it refers to.

Now adjust the directory path to be read out and define the row (variable i) and column (letter at Range [A]) at which cell the entries of the folder names should start.

Result

Once all this is done, you can run the macro via the green play symbol in the VBA editor or via the “Developer Tools” (Alt+F8) in the Excel spreadsheet.

For the example shown here, the result then looks like the following image.

In addition to reading out folders, with VBA you can also read out entire web pages.