Thanks for your comments.
Unfortunately I don't know of a way to traverse the directory tree automatically from Excel. I did look for an option that allowed for a group of folders to be selected, using the Windows File Open API from inside Excel, but the find folder option does not respond to the Multi-select flag. I also tried the Windows Browse Folder API but this has no multi-select option either.
Another option is to see if what you want is available using Windows scripting language, and get the results into a text file. Then we can get the text file into Excel.
I have very little experience with this, but I have put together a short script file that creates a comma delimited text file containing the names of all subdirectories in a folder (it only does one level - so it is rather limited)
It is not exactly what you want, but it may be a starting point. You will see that the folder name and the output filename are hardcoded near the start of the code.
Copy this to a text file (use Notepad) and save it with a vbs file extension. It should run when double clicked or using 'Open with' and select 'Microsoft Windows based script host'.
Dim fso1, fso2
Dim flds, subfld, fld
Dim foldername, outputfilename
'use your first folder name here:
'create file system object for folder names
Set fso1 = CreateObject("Scripting.FileSystemObject")
Set flds = fso1.Getfolder(foldername)
Set subfld = flds.SubFolders
'create file system object for text file
Set fso2 = CreateObject("Scripting.FileSystemObject")
Set file = fso2.OpenTextFile(outputfilename, 2, True)
'get each folder name and save in text file comma delimited
For Each fld in subfld
file.Write fld.name & chr(44)
It should be possible to make this go through subfolders automatically.
There are probably other programs already written to do this, so a bit of Googling should come up with something better.
Once you have a text file, it can be imported into Excel.