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]

one more thing, please...
i have a code that only display the files and not with subfolders, hence I need the code that if i should check a checkbox it should list the files and subfolder files inside my listbox and if i uncheck it, it should list the files without the subfolder files...

 

[Fluff]

How about

Private Sub CheckBox1_Click()
   If Me.CheckBox1 Then
      Me.FileList.ColumnWidths = "500;0"
   Else
      Me.FileList.ColumnWidths = "0;500"
   End If
End Sub

Change the 500 to any number such that you can see the entire path/file name.

 

[yinkajewole]

How about

Private Sub CheckBox1_Click()
   If Me.CheckBox1 Then
      Me.FileList.ColumnWidths = "500;0"
   Else
      Me.FileList.ColumnWidths = "0;500"
   End If
End Sub

Change the 500 to any number such that you can see the entire path/file name.

i'm not with my pc at the moment, so i'm yet to test the code. i may get back to you as soon as i test it

 

[Fluff]

That's ok, no rush

 

[yinkajewole]

please, can you do me a favour,
i have a textbox that i want it to search the files in the listbox, and i want to search letters at any position. eg if i should type "ren", it should bring results of "apprentice, render, children, church renumeration"

 

[Fluff]

As this is now a completely different question, you will need to start a new thread for it.
Thanks

 

[yinkajewole]

How about

Private Sub CheckBox1_Click()
   If Me.CheckBox1 Then
      Me.FileList.ColumnWidths = "500;0"
   Else
      Me.FileList.ColumnWidths = "0;500"
   End If
End Sub

Change the 500 to any number such that you can see the entire path/file name.

i'm sorry, you did not get me well, I did not mean to expand the file list box.
what i mean was that, my first post in this thread contain the code that will display the files in a folder, while your code will display even the files in subfolders. so i like to have a checkbox that when i click the checkbox, the file list box will display the files using your code, while if i uncheck it, it will display the files using my own code.
i hope you understand me better?

 

[yinkajewole]

As this is now a completely different question, you will need to start a new thread for it.
Thanks

ok, no problem, will you be the one to answer me or you can't handle that?

 

[yinkajewole]

Please, did you get this?

i'm sorry, you did not get me well, I did not mean to expand the file list box.
what i mean was that, my first post in this thread contain the code that will display the files in a folder, while your code will display even the files in subfolders. so i like to have a checkbox that when i click the checkbox, the file list box will display the files using your code, while if i uncheck it, it will display the files using my own code.
i hope you understand me better?

 

[Fluff]

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