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

x
  • 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.
0
ckwillGWU
Asked:
ckwillGWU
  • 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.


0
 
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

Use
dir /b /ad /s >bigdirectorystructure.txt  

To capture the results.

0
 
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
 
  Sheet1.Cells.Clear
  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
0
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.

Cheers!

TRM
0
 
ckwillGWUAuthor Commented:
0
 
ckwillGWUAuthor Commented:
0

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