how to export folder and subfolder structure into an excel spreadsheet

I would like vb script / macro on exporting folder structure, including subfolders into an excel file. I do not necessarily need the folder names at this point. Just a file structure.
Who is Participating?
ckwillGWUAuthor Commented:
I found the following and used...It seems to work great - not too familiar with ASCII. Thanks

Sub ListThem()
   Dim startRange As Range
  Set startRange = Sheet1.Range("A1")
  'Parent Directory - Change this to whichever directory you want to use
  ListFoldersAndInfo "C:\Users\MyName\Documents\Application Data", startRange, 0
End Sub
Sub ListFoldersAndInfo(foldername As String, Destination As Range, Level As Long)
  Dim FSO As Object
  Dim Folder As Object
  Dim R As Long
  Dim SubFolder As Object
  Dim Wks As Worksheet
    Set FSO = CreateObject("Scripting.FileSystemObject")
      Set Folder = FSO.GetFolder(foldername)
      Destination = Folder.Name
      Destination.IndentLevel = Level
      Destination.Offset(0, 1) = Folder.Path
      Destination.Offset(0, 2) = Folder.Size
      Set Destination = Destination.Offset(1, 0)
      For Each SubFolder In Folder.SubFolders
         ListFoldersAndInfo Folder.Path & "\" & SubFolder.Name, Destination, Level + 1
      Next SubFolder
    Set FSO = Nothing
End Sub
Alan GunnCommented:
If you use tree /a (A for ASCII) it will extract the folder structure.

You coud try
tree /a >mytreetextfile.txt

and then open the text flie in Excel.

Alan GunnCommented:
You could also use

dir /b /ad /s

/b - Bare. No headers etc
/ad Attribute directory (That's what folders used to be called  ;-)

/s Subdirectory

dir /b /ad /s >bigdirectorystructure.txt  

To capture the results.

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Alan GunnCommented:
That's neat. :-)

I still use the tricks I learned before I knew any VB. :-)

The "dir /b /ad /s > folderstructure.txt" from the command prompt gets the results that end up in col B.


ckwillGWUAuthor Commented:
ckwillGWUAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.