Modify this VBA to List all Folders, Subfolders and Files in a Directory?

BadDogTitan

New Member
Joined
Sep 16, 2013
Messages
18
This bit of hobbled together VBA from various sources lists all files in the selected directory. They are sorted alphabetically, then enumerated in Column 1, which is also a hyperlink to the folder the file is in. Column 2 is the filename, which is a hyperlink to the actual file. Works great for files.

How do I modify it to list everything in the selected directory, including folders and subfolders?

Code:
Option ExplicitDim r As Integer


Sub ListFiles()
    Dim sPath As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select directory"
        .InitialFileName = ThisWorkbook.Path & "\"
        .AllowMultiSelect = False
        If .Show = 0 Then Exit Sub
        sPath = .SelectedItems(1) & "\"
    End With
    r = 5: Range(r + 1 & ":" & Rows.Count).Delete
    DirList sPath, Cells(r, 1)
End Sub


Sub DirList(ByVal sPath As String, rList As Range)
    ' Attribute mask
    Const iAttr     As Long = vbNormal + vbReadOnly + vbSystem + vbDirectory
    Dim jAttr       As Long         ' file attributes
    Dim Coll        As Collection   ' queued directories
    Dim iFile       As Long         ' file counter
    Dim sFile       As String       ' file name
    Dim sName       As String       ' full file name
    Dim sn          As Variant
    Dim sn_tmp      As String
    Dim x           As Integer
    Dim lRng        As Range


    Application.ScreenUpdating = False
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False


    If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
    Set Coll = New Collection
    Coll.Add sPath


    Do While Coll.Count
        sPath = Coll(1)


        sFile = Dir(sPath, iAttr)


        Do While Len(sFile)
            sName = sPath & sFile


            On Error Resume Next
            jAttr = GetAttr(sName)


            If Err.Number Then
                MsgBox sName, vbCritical, "File name violation - Error " & Err.Number
                ' Can't get attributes for files with Unicode characters in
                ' the name, or some particular files (e.g., "C:\System Volume Information")
                Debug.Print sName
                Err.Clear


            Else
                On Error GoTo 0
                If jAttr And vbDirectory Then
                    If Right(sName, 1) <> "." Then Coll.Add sName & "\"
                Else
                    iFile = iFile + 1
                    With rList
                        .Cells(iFile, 1).Hyperlinks.Add Anchor:=.Cells(iFile, 1), Address:=sPath, TextToDisplay:=sName
                        .Cells(iFile, 2).Hyperlinks.Add Anchor:=.Cells(iFile, 2), Address:=sName, TextToDisplay:=sFile
                        sn = Split(sName, "\")
                    End With
                End If
            End If
            sFile = Dir()
        Loop
        Coll.Remove 1
    Loop


    iFile = iFile + 1
    Set lRng = rList.CurrentRegion
    With lRng
        .AutoFilter Field:=1, VisibleDropDown:=False
        .AutoFilter Field:=2, VisibleDropDown:=False
        .Sort Key1:=rList.Cells(r, 1), Header:=xlYes
        .Font.Underline = False
        rList = "1"
        Range(rList.Cells(2, 1), lRng(lRng.Rows.Count, 1)).FormulaR1C1 = "=SUBTOTAL(3,R5C[1]:R[-1]C[1])+1"
    End With


    Columns("A:A").ColumnWidth = 6
    Columns("B:B").ColumnWidth = 80
    Columns("C:C").ColumnWidth = 10
    'Columns.AutoFit
    Rows.AutoFit
    With ActiveWindow
        If .FreezePanes Then .FreezePanes = False
        .SplitColumn = 0
        .SplitRow = r - 1
        .FreezePanes = True
    End With
    Application.ScreenUpdating = True
    lRng.Cells(2, 1).Select
End Sub
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Try like this:
Code:
Option Explicit


Dim r As Integer




Sub ListFiles()
    Dim sPath As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select directory"
        .InitialFileName = ThisWorkbook.Path & "\"
        .AllowMultiSelect = False
        If .Show = 0 Then Exit Sub
        sPath = .SelectedItems(1) & "\"
    End With
    r = 5: Range(r + 1 & ":" & Rows.Count).Delete
    DirList sPath, Cells(r, 1)
End Sub




Sub DirList(ByVal sPath As String, rList As Range)
    ' Attribute mask
    Const iAttr     As Long = vbNormal + vbReadOnly + vbSystem + vbDirectory
    Dim jAttr       As Long         ' file attributes
    Dim Coll        As Collection   ' queued directories
    Dim iFile       As Long         ' file counter
    Dim sFile       As String       ' file name
    Dim sName       As String       ' full file name
    Dim sn          As Variant
    Dim sn_tmp      As String
    Dim x           As Integer
    Dim lRng        As Range




    Application.ScreenUpdating = False
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False




    If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
    Set Coll = New Collection
    Coll.Add sPath




    Do While Coll.Count
        sPath = Coll(1)




        sFile = Dir(sPath, iAttr)




        Do While Len(sFile)
            sName = sPath & sFile




            On Error Resume Next
            jAttr = GetAttr(sName)




            If Err.Number Then
                MsgBox sName, vbCritical, "File name violation - Error " & Err.Number
                ' Can't get attributes for files with Unicode characters in
                ' the name, or some particular files (e.g., "C:\System Volume Information")
                Debug.Print sName
                Err.Clear




            Else
                On Error GoTo 0
[COLOR=#0000ff]                If jAttr And vbDirectory Then[/COLOR]
[COLOR=#0000ff]                    If Right(sName, 1) <> "." Then[/COLOR]
[COLOR=#0000ff]                        Coll.Add sName & "\"[/COLOR]
[COLOR=#0000ff]                        sFile = sName[/COLOR]
[COLOR=#0000ff]                    Else[/COLOR]
[COLOR=#0000ff]                        sFile = ""[/COLOR]
[COLOR=#0000ff]                    End If[/COLOR]
[COLOR=#0000ff]                End If[/COLOR]
[COLOR=#0000ff]                If sFile <> "" Then[/COLOR]
[COLOR=#0000ff]                    iFile = iFile + 1[/COLOR]
[COLOR=#0000ff]                    With rList[/COLOR]
[COLOR=#0000ff]                        .Cells(iFile, 1).Hyperlinks.Add Anchor:=.Cells(iFile, 1), Address:=sPath, TextToDisplay:=sName[/COLOR]
[COLOR=#0000ff]                        .Cells(iFile, 2).Hyperlinks.Add Anchor:=.Cells(iFile, 2), Address:=sName, TextToDisplay:=sFile[/COLOR]
[COLOR=#0000ff]                        sn = Split(sName, "\")[/COLOR]
[COLOR=#0000ff]                    End With[/COLOR]
[COLOR=#0000ff]                End If[/COLOR]
            End If
            sFile = Dir()
        Loop
        Coll.Remove 1
    Loop




    iFile = iFile + 1
    Set lRng = rList.CurrentRegion
    With lRng
        .AutoFilter Field:=1, VisibleDropDown:=False
        .AutoFilter Field:=2, VisibleDropDown:=False
        .Sort Key1:=rList.Cells(r, 1), Header:=xlYes
        .Font.Underline = False
        rList = "1"
        Range(rList.Cells(2, 1), lRng(lRng.Rows.Count, 1)).FormulaR1C1 = "=SUBTOTAL(3,R5C[1]:R[-1]C[1])+1"
    End With




    Columns("A:A").ColumnWidth = 6
    Columns("B:B").ColumnWidth = 80
    Columns("C:C").ColumnWidth = 10
    'Columns.AutoFit
    Rows.AutoFit
    With ActiveWindow
        If .FreezePanes Then .FreezePanes = False
        .SplitColumn = 0
        .SplitRow = r - 1
        .FreezePanes = True
    End With
    Application.ScreenUpdating = True
    lRng.Cells(2, 1).Select
End Sub
 
Upvote 0
Small modification for better looking output :) IMHO:
Code:
Option Explicit


Dim r As Integer


Sub ListFiles()
    Dim sPath As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select directory"
        .InitialFileName = ThisWorkbook.Path & "\"
        .AllowMultiSelect = False
        If .Show = 0 Then Exit Sub
        sPath = .SelectedItems(1) & "\"
    End With
    r = 5: Range(r + 1 & ":" & Rows.Count).Delete
    DirList sPath, Cells(r, 1)
End Sub


Sub DirList(ByVal sPath As String, rList As Range)
    ' Attribute mask
    Const iAttr     As Long = vbNormal + vbReadOnly + vbSystem + vbDirectory
    Dim jAttr       As Long         ' file attributes
    Dim Coll        As Collection   ' queued directories
    Dim iFile       As Long         ' file counter
    Dim sFile       As String       ' file name
    Dim sName       As String       ' full file name
    Dim sn          As Variant
    Dim sn_tmp      As String
    Dim x           As Integer
    Dim lRng        As Range


    Application.ScreenUpdating = False
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False


    If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
[COLOR=#0000ff]    Dim sp2 As String: sp2 = sPath[/COLOR]
    Set Coll = New Collection
    Coll.Add sPath


    Do While Coll.Count
        sPath = Coll(1)
        sFile = Dir(sPath, iAttr)


        Do While Len(sFile)
            sName = sPath & sFile




            On Error Resume Next
            jAttr = GetAttr(sName)




            If Err.Number Then
                MsgBox sName, vbCritical, "File name violation - Error " & Err.Number
                ' Can't get attributes for files with Unicode characters in
                ' the name, or some particular files (e.g., "C:\System Volume Information")
                Debug.Print sName
                Err.Clear
            Else
                On Error GoTo 0
                If jAttr And vbDirectory Then
                    If Right(sName, 1) <> "." Then
                        Coll.Add sName & "\"
[COLOR=#0000ff]                        sFile = Replace(sName, sp2, "..\", , , vbTextCompare) & "\"[/COLOR]
                    Else
                        sFile = ""
                    End If
                End If
                If sFile <> "" Then
                    iFile = iFile + 1
                    With rList
                        .Cells(iFile, 1).Hyperlinks.Add Anchor:=.Cells(iFile, 1), Address:=sPath, TextToDisplay:=sName
                        .Cells(iFile, 2).Hyperlinks.Add Anchor:=.Cells(iFile, 2), Address:=sName, TextToDisplay:=sFile
                        sn = Split(sName, "\")
                    End With
                End If
            End If
            sFile = Dir()
        Loop
        Coll.Remove 1
    Loop


    iFile = iFile + 1
    Set lRng = rList.CurrentRegion
    With lRng
        .AutoFilter Field:=1, VisibleDropDown:=False
        .AutoFilter Field:=2, VisibleDropDown:=False
        .Sort Key1:=rList.Cells(r, 1), Header:=xlYes
        .Font.Underline = False
        rList = "1"
        Range(rList.Cells(2, 1), lRng(lRng.Rows.Count, 1)).FormulaR1C1 = "=SUBTOTAL(3,R5C[1]:R[-1]C[1])+1"
    End With


    Columns("A:A").ColumnWidth = 6
    Columns("B:B").ColumnWidth = 80
    Columns("C:C").ColumnWidth = 10
    'Columns.AutoFit
    Rows.AutoFit
    With ActiveWindow
        If .FreezePanes Then .FreezePanes = False
        .SplitColumn = 0
        .SplitRow = r - 1
        .FreezePanes = True
    End With
    Application.ScreenUpdating = True
    lRng.Cells(2, 1).Select
End Sub
 
Upvote 0
Thanks a million, bobsan42. Sometimes you just get stuck on a thing. Seems simple now. I took your suggestions - I agree with your humble opinion on the output.

Another issue has come up, in that files named with a "+" at the beginning result in a blank cell. I know the quick fix is to not name a file with a plus, but I am afraid end users will continue to do so, and won't know why the files aren't listed.
 
Upvote 0
See if this helps:
Code:
                        .Cells(iFile, 2).Hyperlinks.Add Anchor:=.Cells(iFile, 2), Address:=sName, TextToDisplay:="'" & (sFile)
 
Upvote 0
That did it. Thanks again, bobsan42.

Your improvement got me to thinking if there would be a way to format the directory font differently from the file font, and enumerate only the files?
 
Upvote 0
The way I would do it:
Include a third column in the output having only two Values: FILE or FOLDER (or anything to help distinguish which one is which).
Then on this column you can base the formula in the first and conditionally formatting the second.

The other way is to set formatting during writing the values, and the formula to check for \ in the name and skip numbering.

No need for second collection.
 
Upvote 0

Hi bobsan42


Thanks for the code - it was a life saver. Is there anyway folder and file size can also be added next to the folder and file names respectively?

Can you help adding the following string, if it will work?

Dim fSize As Integer ' Folder/File size
FileLen(sPath)/1024
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top