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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,349
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
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,349
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
 

BadDogTitan

New Member
Joined
Sep 16, 2013
Messages
18
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.
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,349

ADVERTISEMENT

See if this helps:
Code:
                        .Cells(iFile, 2).Hyperlinks.Add Anchor:=.Cells(iFile, 2), Address:=sName, TextToDisplay:="'" & (sFile)
 

BadDogTitan

New Member
Joined
Sep 16, 2013
Messages
18
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?
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,349

ADVERTISEMENT

of course there is :)
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,349
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,284
Messages
5,577,200
Members
412,776
Latest member
nadroy87
Top