search file in subfold and open

[frotaru]

For example the path is c:\work

But c:\work

Has subfolders c:\work\01
c:\work\02
Etc etc

Every subfolder contains a file named Diff01.xls



I would like to know if it’s possible to create a macro that can search for every file named Diff01.xls in all subfolders of c:\work

For every file Diff01.xls found
Open Workbook
And than Application.Run "test001"

or something like if diff01.xls not found in c:\work\01 try in c:\work\02 ,03 and so on


Thank you all

 

[wongm003]

You can try this code... note if there are multiple files with the same name need to close the previous file before opening the new file or you will get a runtime error (1004)..

Public Sub my_find_files()

   'search for files
    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\work"
        .SearchSubFolders = True
        .Filename = "Diff01"
        .FileType = msoFileTypeExcelWorkbooks
        .Execute

        For x = 1 To .FoundFiles.Count
            'open workbook
            Workbooks.Open (.FoundFiles(x))
            
            'do what you want to the workbook
            Application.Run "test001"
            
            'note if you dont close and try to open another wb
            'with same name will get an error 1004
            
            'close workbook
            ActiveWorkbook.Close
        Next x
    End With

End Sub

 

[frotaru]

hi

Thank you verry much