[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 470
  • Last Modified:

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.
  • 3
  • 3
1 Solution
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.

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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:

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now