Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: List Folders & Subfolders From Directory

  1. #1
    Join Date
    21st September 2006
    Posts
    312

    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. #2
    Join Date
    24th December 2004
    Location
    Nanaimo, Vancouver Island, British Columbia, Canada
    Posts
    2,464

    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

  3. #3
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    19,291

    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

    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.

  4. #4
    Join Date
    21st September 2006
    Posts
    312

    Re: List Folders & Subfolders From Directory

    Thanks Guys,

    Quote 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.

  5. #5
    Join Date
    24th December 2004
    Location
    Nanaimo, Vancouver Island, British Columbia, Canada
    Posts
    2,464

    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

  6. #6
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    19,291

    Re: List Folders & Subfolders From Directory

    The Reference should travel with the workbook.
    Hope that Helps

    Roy

    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.

  7. #7
    Join Date
    21st September 2006
    Posts
    312

    Re: List Folders & Subfolders From Directory

    Right you are.

    Thanks Roy.

    Brad
    Brad.

  8. #8
    Join Date
    25th July 2010
    Posts
    47

    Re: List Folders & Subfolders From Directory

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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,205

    Re: List Folders & 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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Can you create folders in using a list
    By billyj in forum Excel and/or Email Help
    Replies: 11
    Last Post: December 11th, 2010, 05:02
  2. Breakdown Of All Folders & Files In Directory
    By hgus393 in forum EXCEL HELP
    Replies: 3
    Last Post: September 12th, 2007, 23:18
  3. List Files, Path & Sub Folders From Directory
    By ghh3rd in forum EXCEL HELP
    Replies: 1
    Last Post: December 6th, 2006, 05:56
  4. Search all mail item folders and subfolders
    By bvg in forum Excel and/or Email Help
    Replies: 1
    Last Post: March 23rd, 2005, 20:38
  5. Search all folders/subfolders in outlook
    By bvg in forum Excel and/or Email Help
    Replies: 1
    Last Post: March 12th, 2005, 01:49

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno