how to export folder and subfolder structure into an excel spreadsheet

Posted on 2011-10-13
Last Modified: 2012-05-12
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.
Question by:ckwillGWU
    LVL 6

    Expert Comment

    by:Alan Gunn
    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.

    LVL 6

    Expert Comment

    by:Alan Gunn
    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.


    Accepted Solution

    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
    LVL 6

    Expert Comment

    by:Alan Gunn
    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.



    Author Closing Comment


    Author Comment


    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now