ExcelBanter - Here is a tough one!

Private oFSO as object

Sub LoopFolders()
Dim i As Integer

Set oFSO = CreateObject("Scripting.FileSystemObject")

selectFiles "c:\MyTest"

Set oFSO = Nothing

End Sub

'---------------------------------------------------------------------------
Sub selectFiles(sPath)
'---------------------------------------------------------------------------
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr

Set Folder = oFSO.GetFolder(sPath)

For Each fldr In Folder.Subfolders
selectFiles fldr.Path
Next fldr

For Each file In Folder.Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook.Worksheets(1)
If .Range("a4").Value = " YES" Then
myTotal = myTotal + .Range("A5").Value
End If
endwith
ActiveWorkbook.Close savechanges = False
End If
Next file

MsgBox myTotal

End Sub

--
HTH

Bob Phillips

"Michael Smith" wrote in message
...
I have a folder with a bunch of xls files. I have a separate TOTALS
file open which I need to return the sum of all the files A5 cells if
A4=YES. The file names will all be different, so I need some kind
of...open all files and if A4=YES then add it to my total on my new
sheet.

Confused - Mike
TIA

*** Sent via Developersdex http://www.developersdex.com ***