Announcement

Collapse
No announcement yet.

List Folders & Subfolders From Directory

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • List Folders & Subfolders From Directory



    Does anyone know how to list all the folders/subfolders (no files) of a specified directory?
    I've seen a few examples of listing filenames from directories but I'm unsure how to just list folders/subfolders.

    Brad
    Brad.

  • #2
    Re: List Folders & Subfolders From Directory

    Hi Brad,

    Found a few on the net. Googled "vba list folders subfolders".

    Each one requires a reference to the "Microsoft Scripting Library". In the VBE I choose Tools/References and clicked "Microsoft Scripting Runtime".

    Then run this code. The original had the "Debug.Print line, I changed it to print on the sheet...

    The two best places I found were:

    http://www.vbforums.com/showthread.p...hreadid=244880

    This one I had to reformat all of the code after I copied/pasted it. It came out as one LONG line...


    This looks good too:

    http://www.erlandsendata.no/english/...ldersscripting

    A better explanation, different code.

    Here's the first, edited:

    Code:
    Sub Ck()
    
    Dim strStartPath As String
        
        strStartPath = "C:\" 'ENTER YOUR START FOLDER HERE
        ListFolder strStartPath
    
    End Sub
    Sub ListFolder(sFolderPath As String)
    
    Dim FS As New FileSystemObject
    Dim FSfolder As Folder
    Dim subfolder As Folder
    Dim i As Integer
    
        Set FSfolder = FS.GetFolder(sFolderPath)
        
        For Each subfolder In FSfolder.SubFolders
            DoEvents
            i = i + 1
    'added this line
            Cells(i, 1) = subfolder
    'commented out this one
            'Debug.Print subfolder
        Next subfolder
        
        Set FSfolder = Nothing
    
    'optional, I suppose    
        MsgBox "Total sub folders in " & sFolderPath & " : " & i
    
    End Sub
    and the second

    Code:
    Option Explicit
    
    Sub TestListFolders()
        
        Application.ScreenUpdating = False
        
        'create a new workbook for the folder list
    
    'commented out by dr
        'Workbooks.Add
        
    'line added by dr to clear old data
        Cells.Delete
        
        ' add headers
        With Range("A1")
            .Formula = "Folder contents:"
            .Font.Bold = True
            .Font.Size = 12
        End With
        
        Range("A3").Formula = "Folder Path:"
        Range("B3").Formula = "Folder Name:"
        Range("C3").Formula = "Size:"
        Range("D3").Formula = "Subfolders:"
        Range("E3").Formula = "Files:"
        Range("F3").Formula = "Short Name:"
        Range("G3").Formula = "Short Path:"
        Range("A3:G3").Font.Bold = True
        
        'ENTER START FOLDER HERE
        ' and include subfolders (true/false)
        ListFolders "C:\", True
        
        Application.ScreenUpdating = True
        
    End Sub
    
    Sub ListFolders(SourceFolderName As String, IncludeSubfolders As Boolean)
    ' lists information about the folders in SourceFolder
    ' example: ListFolders "C:\", True
    Dim FSO As Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
    Dim r As Long
        
        Set FSO = New Scripting.FileSystemObject
        Set SourceFolder = FSO.GetFolder(SourceFolderName)
        
    'line added by dr for repeated "Permission Denied" errors
        
        On Error Resume Next
        
        ' display folder properties
        r = Range("A65536").End(xlUp).Row + 1
        Cells(r, 1).Formula = SourceFolder.Path
        Cells(r, 2).Formula = SourceFolder.Name
        Cells(r, 3).Formula = SourceFolder.Size
        Cells(r, 4).Formula = SourceFolder.SubFolders.Count
        Cells(r, 5).Formula = SourceFolder.Files.Count
        Cells(r, 6).Formula = SourceFolder.ShortName
        Cells(r, 7).Formula = SourceFolder.ShortPath
        If IncludeSubfolders Then
            For Each SubFolder In SourceFolder.SubFolders
                ListFolders SubFolder.Path, True
            Next SubFolder
            Set SubFolder = Nothing
        End If
        
        Columns("A:G").AutoFit
        
        Set SourceFolder = Nothing
        Set FSO = Nothing
        
    'commented out by dr
        'ActiveWorkbook.Saved = True
    
    End Sub
    Cheers,

    dr

    "Questions, help and advice for free, small projects by donation. large projects by quote"

    http://www.ExcelVBA.joellerabu.com

    Comment


    • #3
      Re: List Folders & Subfolders From Directory

      Place this code in a Standard module

      Code:
      Const BIF_RETURNONLYFSDIRS As Long = &H1        ''' For finding a folder to start document searching
      Const BIF_DONTGOBELOWDOMAIN As Long = &H2       ''' Does not include network folders below the domain level in the tree view control
      Const BIF_RETURNFSANCESTORS As Long = &H8       ''' Returns only file system ancestors.
      Const BIF_BROWSEFORCOMPUTER As Long = &H1000    ''' Returns only computers.
      Const BIF_BROWSEFORPRINTER As Long = &H2000     ''' Returns only printers.
      Const BIF_BROWSEINCLUDEFILES As Long = &H4000   ''' Returns everything.
      
      Const MAX_PATH As Long = 260
      
      Type BROWSEINFO
          hOwner     As Long
          pidlRoot   As Long
          pszDisplayName As String
          lpszINSTRUCTIONS As String
          ulFlags    As Long
          lpfn       As Long
          lParam     As Long
          iImage     As Long
      End Type
      
      Declare Function SHGetPathFromIDListA Lib "shell32.dll" (ByVal pidl As Long, ByVal pszBuffer As String) As Long
      Declare Function SHBrowseForFolderA Lib "shell32.dll" (lpBrowseInfo As BROWSEINFO) As Long
      
      
      Function BrowseFolder() As String
      
          Const szINSTRUCTIONS As String = "Choose the folder to use for this operation." & vbNullChar
      
          Dim uBrowseInfo As BROWSEINFO
          Dim szBuffer As String
          Dim lID    As Long
          Dim lRet   As Long
      
          With uBrowseInfo
              .hOwner = 0
              .pidlRoot = 0
              .pszDisplayName = String$(MAX_PATH, vbNullChar)
              .lpszINSTRUCTIONS = szINSTRUCTIONS
              .ulFlags = BIF_RETURNONLYFSDIRS
              .lpfn = 0
          End With
      
          szBuffer = String$(MAX_PATH, vbNullChar)
      
          ''' Show the browse dialog.
          lID = SHBrowseForFolderA(uBrowseInfo)
      
          If lID Then
              ''' Retrieve the path string.
              lRet = SHGetPathFromIDListA(lID, szBuffer)
              If lRet Then BrowseFolder = Left$(szBuffer, InStr(szBuffer, vbNullChar) - 1)
          End If
      
      End Function
      In a second Module copy this code

      Code:
      Option Explicit
      
      Sub CreateList()
          Application.ScreenUpdating = False
          Workbooks.Add    ' create a new workbook for the folder list
          ' add headers
          With Cells(1, 1)
              .Value = "Folder contents:"
              .Font.Bold = True
              .Font.Size = 12
          End With
          Cells(3, 1).Value = "Folder Path:"
          Cells(3, 2).Value = "Folder Name:"
          Cells(3, 3).Value = "Size:"
          Cells(3, 4).Value = "Subfolders:"
          Cells(3, 5).Value = "Files:"
          Cells(3, 6).Value = "Short Name:"
          Cells(3, 7).Value = "Short Path:"
          Range("A3:G3").Font.Bold = True
          ListFolders BrowseFolder, True
          Application.ScreenUpdating = True
      End Sub
      
      Sub ListFolders(SourceFolderName As String, IncludeSubfolders As Boolean)
          ' lists information about the folders in SourceFolder
          Dim FSO    As Scripting.FileSystemObject
          Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
          Dim r      As Long
          Set FSO = New Scripting.FileSystemObject
          Set SourceFolder = FSO.GetFolder(SourceFolderName)
          ' display folder properties
          r = Cells(Rows.Count, 1).End(xlUp).Row + 1
          Cells(r, 1).Value = SourceFolder.Path
          Cells(r, 2).Value = SourceFolder.Name
          Cells(r, 3).Value = SourceFolder.Size
          Cells(r, 4).Value = SourceFolder.SubFolders.Count
          Cells(r, 5).Value = SourceFolder.Files.Count
          Cells(r, 6).Value = SourceFolder.ShortName
          Cells(r, 7).Value = SourceFolder.ShortPath
          If IncludeSubfolders Then
              For Each SubFolder In SourceFolder.SubFolders
                  ListFolders SubFolder.Path, True
              Next SubFolder
              Set SubFolder = Nothing
          End If
          Columns("A:G").AutoFit
          Set SourceFolder = Nothing
          Set FSO = Nothing
          ActiveWorkbook.Saved = True
      
      End Sub
      NOTE: This code uses Microsoft Scripting Runtime. The macro examples need a reference to the Microsoft Scripting Runtime library. You can do this from within the VBE by selecting the menu Tools > References and scroll for Microsoft Scripting Runtime, check th box then click OK
      Hope that Helps

      Roy

      New users should read the Forum Rules before posting

      For free Excel tools & articles visit my web site

      If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

      RoyUK's Web Site

      royUK's Database Form

      Where to paste code from the Forum

      About me.

      Comment


      • #4
        Re: List Folders & Subfolders From Directory

        Thanks Guys,

        Originally posted by royUK
        NOTE: This code uses Microsoft Scripting Runtime. The macro examples need a reference to the Microsoft Scripting Runtime library. You can do this from within the VBE by selecting the menu Tools > References and scroll for Microsoft Scripting Runtime, check th box then click OK
        Does this mean the user that I send this spreadsheet to will need to have Microsoft Scripting Runtime checked in the VBE also?

        Brad
        Brad.

        Comment


        • #5
          Re: List Folders & Subfolders From Directory

          Hi Brad,

          I believe the answer is yes. Fairly easy to do. Mayve it could be coded? New thread? <g>
          Cheers,

          dr

          "Questions, help and advice for free, small projects by donation. large projects by quote"

          http://www.ExcelVBA.joellerabu.com

          Comment


          • #6
            Re: List Folders &amp; Subfolders From Directory

            The Reference should travel with the workbook.
            Hope that Helps

            Roy

            New users should read the Forum Rules before posting

            For free Excel tools & articles visit my web site

            If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

            RoyUK's Web Site

            royUK's Database Form

            Where to paste code from the Forum

            About me.

            Comment


            • #7
              Re: List Folders &amp; Subfolders From Directory

              Right you are.

              Thanks Roy.

              Brad
              Brad.

              Comment


              • #8
                Re: List Folders &amp; Subfolders From Directory

                Please help, why this not working in excel 2007. I cant even seed where the settings is to be done.

                Comment


                • #9


                  Re: List Folders &amp; Subfolders From Directory

                  Ped,

                  DO NOT resurrect old threads, or hijack any other thread. Start your own thread and, if it helps clarify your own needs, provide a link back to this thread.
                  Please take the time to read the Forum Rules and follow them. This is second infraction in two days. If you want to continue posting, then take the time to understand the rules you agreed to abide by when you subscribed to this forum.
                  AAE
                  ----------------------------------------------------

                  Forum Rules | Message to Cross Posters | How to use Tags

                  Comment

                  Working...
                  X