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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

bobsan42

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

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

ADVERTISEMENT

of course there is :)
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
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,109,349
Messages
5,528,188
Members
409,807
Latest member
nicky736

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top