Macro to open all Excel Files in Directory and Sub-Directory

[howard]

I would like a macro to allow me to open up all excel files in directory and sub-directory C:\import that contains Br (P) in the file name

Your assistance is most appreciated

 

[wigi]

For example:

excel - Cycle through sub-folders and files in a user-specified root directory - Stack Overflow

It's the first hit when using Google for:

excel vba loop through folders and subfolders

With 3,000 posts at MrExcel, I expect you to be able to change the code as per needed.
If not, please post back with the status and where you would have some issues.

 

[howard]

Hi Wigi

I have several files in directory c:\pull as well as several sub-folders in this directory starting with TB for eg TBBR1, TBBr2 etc

What I want to do is to open up all the XLS files that contains (p) in the file name for eg Br1 ACCNTS (p).xls

I have tried to amend the code per the link which you kindly provided, but no files are opened
,
I think it may have to do with the following code below

 Set fldStart = fso.GetFolder("C:\Pull") ' <-- use your FileDialog code here

See full code below

Your assistance in resolving this is most appreciated

Mask = "(p)*.xls"

 Option Explicit

Sub Open_Files_Pull()
    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:\Pull") ' <-- use your FileDialog code here

    Mask = "(p)*.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

 

[howard]

Have also posted on Windowssecrets

Macro to open up specified Files in Directory and Sub-directory | Windows Secrets Lounge

 

[howard]

Hi Wigi

Have you had a chance to check my code?

Howard

 

[wigi]

Hello

Thanks for asking, but unfortunately no, not had the time yet.

 

[howard]

Hi Wigi

No Problem-When you have a chance, it will be appreciated if you can have a look at my code

Howard