UserForm to list all files within folder and subfolder

[dshafique]

Hi everyone, im trying to get a Userform which allows a user to chose a file and edit it from the form. I was successful in having it be able to edit from the userform, however the only thing left is that when I select a folder, it only shows me the files within the folder, I want it to display the files and all the files within the subfolders as well. I've been at it a couple of days, and would appreciate any help from you guys.

Sub SelectFolder()Dim sFolder As String
    ' Open the select folder prompt
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = -1 Then ' if OK is pressed
            sFolder = .SelectedItems(1)
        End If
    End With
    
    If sFolder <> "" Then ' if a file was chosen
        If Right(sFolder, 1) <> "\" Then
            sFolder = sFolder & "\"
        End If
    End If
    Txt_BrowseFile = sFolder
    Dir1 = Txt_BrowseFile.value
    
End Sub

thanks!

 

[Kenneth Hobson]

To do that, I guess that I would put the list of files with full paths into a Listbox with MultiSelect.

Here is the method that I normally use:

Sub Test_aFFs()
  Dim x, i As Long
  x = aFFs("C:\Users\hobs0003\Dropbox\Excel\FileFolder\*.xlsm", , True)
  
  'MsgBox Join(x, vbLf)
  For i = 0 To UBound(x)
    Debug.Print i, x(i)
  Next i
End Sub

'Set extraSwitches, e.g. "/ad", to search folders only.
'MyDir should end in a "\" character unless searching by wildcards, e.g. "x:\test\t*
'Command line switches for the shell's Dir, http://ss64.com/nt/dir.html
Function aFFs(myDir As String, Optional extraSwitches = "", _
  Optional tfSubFolders As Boolean = False) As Variant
  
  Dim s As String, a() As String, v As Variant
  Dim b() As Variant, i As Long
  
  If tfSubFolders Then
    s = CreateObject("Wscript.Shell").Exec("cmd /c dir " & _
      """" & myDir & """" & " /b /s " & extraSwitches).StdOut.readall
    Else
    s = CreateObject("Wscript.Shell").Exec("cmd /c dir " & _
      """" & myDir & """" & " /b " & extraSwitches).StdOut.readall
  End If
  
  a() = Split(s, vbCrLf)
  If UBound(a) = -1 Then
    MsgBox myDir & " not found.", vbCritical, "Macro Ending"
    Exit Function
  End If
  ReDim Preserve a(0 To UBound(a) - 1) As String 'Trim trailing vblfcr
  
  For i = 0 To UBound(a)
    If Not tfSubFolders Then
      s = Left$(myDir, InStrRev(myDir, "\"))
      'add the folder name
      a(i) = s & a(i)
    End If
  Next i
  aFFs = sA1dtovA1d(a)
End Function

Function sA1dtovA1d(strArray() As String) As Variant
  Dim varArray() As Variant, i As Long
  ReDim varArray(LBound(strArray) To UBound(strArray))
  For i = LBound(strArray) To UBound(strArray)
    varArray(i) = CVar(strArray(i))
  Next i
  sA1dtovA1d = varArray()
End Function

 

[dshafique]

thanks Kenneth, but is there a way to do this so the user chooses the file path? in my form what happens is the user clicks browse and chooses a folder. the file path is displayed in a text box and the all the files within are displayed in a different textbox. then the user selects a file and it opens it and edits it. I dont want to hardcode the file path because people will always change destinations. thanks

 

[Kenneth Hobson]

Your first post shows that you know how to show a folder select dialog.

I prefer a listbox or combobox myself. If you really want a textbox for the list, I guess you can use Join().

Here is my listbox method.

Private Sub CommandButton1_Click()
  TextBox1.Value = Get_Folder(ThisWorkbook.Path & "\")
End Sub

Private Sub CommandButton2_Click()
  If Dir(TextBox1.Value) = "" Then Exit Sub
  ListBox1.List = aFFs(TextBox1.Value & "*.xlsm", , True)
End Sub

Function Get_Folder(Optional FolderPath As String, _
  Optional HeaderMsg As String) As String
  With Application.FileDialog(msoFileDialogFolderPicker)
    If FolderPath = "" Then
      .InitialFileName = Application.DefaultFilePath
      Else
      .InitialFileName = FolderPath
    End If
    .Title = HeaderMsg
    If .Show = -1 Then
        Get_Folder = .SelectedItems(1) & "\"
    Else
        Get_Folder = ""
    End If
  End With
End Function

 

[dshafique]

thanks Kenneth, this helped me get closer to my goal.