Opening excel files in subfolders

[Griff389]

Can anyone help me. I have a folder containing approx 8 sub folders, with a total of about 1000 excel files. I need to open each one, replace some forumlas, and then save and close them.

What i've done previously is copy all those files into one folder, and then run the code below to modify the files. This works, but does anyone know some code get excel to open files in lots of subfolders as oppose to one single folder?

The code i've got which works for one folder is:

'Set Paths
MyPath = "n:\user\shared\bom\TotalCostTemp"
myfile = Dir(MyPath & "\*.xls", vbNormal)
'open file needed for vlookup function to work
Workbooks.Open "n:\user\shared\bom\StockCostingsExtract.xls", 3, 1

'Start Loop
Do While myfile <> &quot;&quot;
'Open file
Workbooks.Open Filename:=MyPath & &quot;\&quot; & myfile, UpdateLinks:=0
Perform find and replace
Workbooks(myfile).Worksheets(&quot;Sheet1&quot.Cells.Replace What:=&quot;\\2k01files\files\&quot;, Replacement:=&quot;n:\&quot;, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Application.MaxChange = 0.001
'Remove settings for saving external links
With ActiveWorkbook
.PrecisionAsDisplayed = False
.SaveLinkValues = False
End With
'Save and close file

ActiveWorkbook.Save
ActiveWorkbook.Close
myfile = Dir

Loop


Regards
Griff

 

[Luis939]

maybe u can use the vbDirectory argument, which Specifies directories or folders in addition to files with no attributes.