Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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:

    VB:
    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

    VB:
    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
    17,898

    Re: List Folders & Subfolders From Directory

    Place this code in a Standard module

    VB:
    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

    VB:
    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
    17,898

    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,206

    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, 04:02
  2. Breakdown Of All Folders & Files In Directory
    By hgus393 in forum EXCEL HELP
    Replies: 3
    Last Post: September 12th, 2007, 22:18
  3. List Files, Path & Sub Folders From Directory
    By ghh3rd in forum EXCEL HELP
    Replies: 1
    Last Post: December 6th, 2006, 04: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, 19:38
  5. Search all folders/subfolders in outlook
    By bvg in forum Excel and/or Email Help
    Replies: 1
    Last Post: March 12th, 2005, 00: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