search file in subfold and open

frotaru

Board Regular
Joined
Oct 12, 2006
Messages
124
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

wongm003

Board Regular
Joined
Aug 8, 2005
Messages
237
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)..

Code:
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
 

Forum statistics

Threads
1,136,649
Messages
5,676,990
Members
419,667
Latest member
MegEri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top