Listing Sub Folders

[yinkajewole]

I have this code this list the files of a folder into a ListBox called "Filelist". how can i modify the code to include the files in its subfolders?

Function FileArray(Path As String)    Dim Name As String, Counter As Integer, Files() As String
    Name = Dir("C:\Users\USER\Documents\*.xls", vbNormal)
    Counter = 0
    Do While Name > ""
        If Name > "." And Name > ".." Then
            ReDim Preserve Files(Counter)
            Files(Counter) = Name
            Counter = Counter + 1
        End If
        Name = Dir
    Loop
    FileArray = Files()
End Function




Private Sub UserForm_Initialize()
    Dim Files As Variant, NewDocument As Variant, Folder As String
    Folder = "C:\Users\USER\Documents\"  ' ENTER PATH HERE
    Files = FileArray(Folder)
    For Each NewDocument In Files
       FileList.AddItem NewDocument 'ASSUMING U HAVE A LISTBOX FileList IN THE FORM
    Next
End Sub

 

[yinkajewole]

For switching folders try

[COLOR=#ff0000]Dim FSO As Object
Dim StartFldr As Object
Dim StartPth As String[/COLOR]

Private Sub CheckBox1_Click()
   Me.FileList.Clear
   Call RecursiveFolder(FSO, StartFldr, Me.CheckBox1.Value)
End Sub

Private Sub UserForm_Initialize()
   
   StartPth = "C:\Mrexcel"
   Set FSO = CreateObject("Scripting.FileSystemObject")
   Set StartFldr = FSO.getFolder(StartPth)
   Call RecursiveFolder(FSO, StartFldr, False)
   Me.FileList.ColumnWidths = "0;200"
End Sub
Sub RecursiveFolder(FSO As Object, Fldr As Object, IncludeSubFolders As Boolean)
   Dim FldrFile As Object, SubFldr As Object
   
   For Each FldrFile In Fldr.Files
      If FSO.GetExtensionName(FldrFile) Like "xls" Then
         With Me.FileList
            .AddItem FldrFile
            .List(.ListCount - 1, 1) = FldrFile.Name
         End With
      End If
   Next FldrFile
   
   If IncludeSubFolders Then
      For Each SubFldr In Fldr.subfolders
         Call RecursiveFolder(FSO, SubFldr, True)
      Next SubFldr
   End If
End Sub

With the lines in red at the very top of the module

It did not work

 

[yinkajewole]

Oh, I'm sorry, it worked eventually. I forgot to change the column count

 

[yinkajewole]

another thing that needs finishing touch is that i want the path to be taken from a textbox called (folder_TextBox). and that whenever I change the path in the textbox, the file list should change.
i used this code, the problem is it always add to the file list each time the path is changed

Private Sub folder_TextBox_change()
 
   StartPth = folder_TextBox.Text
   Set FSO = CreateObject("Scripting.FileSystemObject")
   Set StartFldr = FSO.getFolder(StartPth)
   Call RecursiveFolder(FSO, StartFldr, False)
   Me.Filelist.ColumnWidths = "0;200"

End Sub

i used this code, the problem is it always add to the file list each time the path is changed

 

[Fluff]

Add

Me.FileList.Clear

to the code before the

Call RecursiveFolder(FSO, StartFldr, False)

 

[yinkajewole]

Add

Me.FileList.Clear

to the code before the

Call RecursiveFolder(FSO, StartFldr, False)

you are a genus!
I wish I am experienced as you one day.

I think I am done with this thread for now.
Many Thanks.

 

[Fluff]

You're welcome & thanks for the feedback

 

[yinkajewole]

Please, is it also possible to optimise this code to list the files quicker and faster just like you did here https://www.mrexcel.com/forum/excel-questions/1095858-listbox-search-any-position-3.html
because it slows each time there are plenty of files in the folder.

 

[Fluff]

Change the folder_TextBox_change() to an AfterUpdate event or an Exit event.
Otherwise it's trying to read the file list every time you change a single letter in the textbox

 

[yinkajewole]

Change the folder_TextBox_change() to an AfterUpdate event or an Exit event.
Otherwise it's trying to read the file list every time you change a single letter in the textbox

It did not work the way I want. All the same, I still appreciate hitherto you've helped me.

 

[yinkajewole]

please, can you give me a code that will display a progress bar/text when it's populating the listbox?