Cycle through sub-folders and files in a user-specified root directory [duplicate]

by

You might find it easier to use the FileSystemObject, somthing like this

This dumps a folder/file list to the Immediate window

Option Explicit

Sub Demo()
    Dim fso As Object 'FileSystemObject
    Dim fldStart As Object 'Folder
    Dim fld As Object 'Folder
    Dim fl As Object 'File
    Dim Mask As String
    
    Set fso = CreateObject("scripting.FileSystemObject") ' late binding
    'Set fso = New FileSystemObject 'or use early binding (also replace Object types)
    
    Set fldStart = fso.GetFolder("C:\Your\Start\Folder") '-- use your FileDialog code here

    Mask = "*.xls"
    Debug.Print fldStart.Path & "\"
    ListFiles fldStart, Mask
    For Each fld In fldStart.SubFolders
        ListFiles fld, Mask
        ListFolders fld, Mask
    Next
End Sub


Sub ListFolders(fldStart As Object, Mask As String)
    Dim fld As Object 'Folder
    For Each fld In fldStart.SubFolders
        Debug.Print fld.Path & "\"
        ListFiles fld, Mask
        ListFolders fld, Mask
    Next

End Sub

Sub ListFiles(fld As Object, Mask As String)
    Dim fl As Object 'File
    For Each fl In fld.Files
        If fl.Name Like Mask Then
            Debug.Print fld.Path & "\" & fl.Name
        End If
    Next
End Sub

More Related Contents:

  1. VBA comma separated cells, needt last entry only
  2. How to copy Outlook mail message into excel using VBA or Macros
  3. Are there disadvantages in putting code into Userforms instead of modules?
  4. Save each sheet in a workbook to separate CSV files
  5. Difference between ‘on error goto 0’ and ‘on error goto -1’ — VBA
  6. Export each sheet to a separate csv file
  7. ShowAllData method of Worksheet class failed
  8. Detect if range is empty
  9. Checking if a worksheet-based checkbox is checked
  10. Assign keyboard shortcut to run procedure
  11. Faster way to remove ‘extra’ spaces (more than 1) from a large range of cells using VBA for Excel
  12. Uncheck all checkboxes across entire workbook via CommandButton
  13. return csv file as recordset
  14. VBA workbooks.Close without being prompted to if the user wants to save?
  15. VBA equivalent to Excel’s mod function
  16. Get text from clipboard using GetText – avoid error on empty clipboard
  17. Copy a selected range to another worksheet
  18. Benchmarking VBA Code
  19. Excel macro – paste only non empty cells from one sheet to another
  20. The MacScript function is not working well in Office for Mac 2016! Any ideas?
  21. Pointers needed for speeding up nested loop macro in VBA
  22. Display milliseconds in Excel
  23. ‘1004’: “The sort reference is not valid.”
  24. Application.Quit command not closing the entire Excel Application
  25. Create hyperlink to another sheet
  26. Reducing WithEvent declarations and subs with VBA and ActiveX
  27. Trying to open the workbook in separate instance
  28. Convert Early Binding VBA to Late Binding VBA : Excel to Outlook Contacts
  29. How to protect Excel workbook using VBA?
  30. How to get the range of the visible rows after applying an advanced filter in Excel (VBA)

Leave a Comment