Loop files in subfolders

[DanielS]

Excel 2003
I wish to loop through all subfolders, select Rps*.xls files, open them, perform some operations through a code, and save and close the files. Directory path: C:\Databank\MRP05\Protec\

Could someone tell me how this can be achieved?

 

[jbeaucaire]

This will open the subfolders in the path you've designated, meaning each folder in that path will be search, but any subfolders deeper than that would not be:

Option Explicit

Sub OpenFilesInSubFolders()
'Author:    Jerry Beaucaire,  9/4/2011
'Summary:   Opens all files in the sub folders of a default
'           master path, process them, save, repeat.
'           User must add additional code to determine what
'           the "processing" will be once each file is open

Dim fNAME As String
Dim fPATH As String:    fPATH = "C:\Databank\MRP05\Protect\"        'don't forget the final \ in this string
Dim FSO As Object:      Set FSO = CreateObject("Scripting.FileSystemObject")
Dim FLD As Object:      Set FLD = FSO.GetFolder(fPATH)
Dim SubFLDRS As Object: Set SubFLDRS = FLD.SubFolders
Dim SubFLD As Object
Dim wbData As Workbook

Application.ScreenUpdating = False

For Each SubFLD In SubFLDRS
    fNAME = Dir(fPATH & SubFLD.Name & "\RPS*.xls")
    
    Do While Len(fNAME) > 0
        Set wbData = Workbooks.Open(fPATH & SubFLD.Name & "\" & fNAME)
   
        'insert code here to do the "processing of the file just opened
    
        wbData.Close False
        
        fNAME = Dir
    Loop
    
Next SubFLD

Application.ScreenUpdating = True
End Sub

Based on the larger macro WBs to Sheets - Part 3.

 

[DanielS]

Dear Mr. jbeaucaire

Thank you for the code. It is working perfectly ok.

I wanted to ask one thing. Could this code be extended by one more level to include "Month" as follows (without any significant amount of modifications)?. If too many changes are required, then please don't do it. I can certainly manage with this code.

C:\Databank\MRP05\Protect\Month

Thanks once again.

 

[jbeaucaire]

Can you explain that in a little more detail?

 

[DanielS]

Can you explain that in a little more detail?

You have already given me the code upto C:\Databank\MRP05\Protect\

Now I was wondering if the same code can be extended to go one more level deeper i.e. C:\Databank\MRP05\Protect\Month\ In Months folder there will be usually 21 to 22 subfolders for respective working days. I want these subfolders to be covered in the code and wondering if this is possible without too many modifications in the present code that you have supplied.

For your code to work, I cut and make new directory C:\MRP05\Protect\Month (dropping the Databank). With a new modified code, I may be able to use the original directory as it is C:\Databank\MRP05\Protect\Month. I hope my point is now clear. Again, I would request you to modify the code, only if it does not involve too much tweaking.

Thanks once again.

 

[jbeaucaire]

If you edit this one line of code:

fPATH = "C:\Databank\MRP05\Protect\"

...to read:

fPATH = "C:\Databank\MRP05\Protect\Month\"

...then the macro will parse those 21-22 subfolders inside your Month folder. No other tweaking necessary.

 

[DanielS]

Ok. Thank you very much.