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.
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.