You need to use the FSO (FileSystemObject). I have code  I use often to
process files  in a one main dir. In it (which is very similar in base to the
code below) I use the FSO to pull all files from a predefined path variable
(captured from the user) to do [whatever] by putting the files into an Array
and handling each by looping through the array.

Here's some code that I got from Chip Pearson (Excel MVP)...that does about
the same thing, but he uses For/Each to loop  through the files in the dir,
whereas I pull the files into an array, first. Not sure which might be more
efficient? However, Chip's code (below) includes the code to process
subfolders, something that I just haven't had a need for to date. So,
although I haven't used his code...I'm sure it works great and should do
what you need.

Note...You'll need to set a Reference in VBA to the Microsoft Scripting
RunTime Library.

(If you need any further help, search Google groups for:

"FSO" "VBA"



Sub DoIt()
Dim FSO As Scripting.FileSystemObject
Dim TopFolder As String
Set FSO = New Scripting.FileSystemObject
TopFolder = "C:\FolderName" '<<<<<<<<< CHANGE THIS
InnerProc FSO.GetFolder(TopFolder), FSO
End Sub


Sub InnerProc(F As Scripting.Folder, FSO As
Scripting.FileSystemObject)


Dim SubFolder As Scripting.Folder
Dim OneFile As Scripting.File
Dim WB As Workbook


For Each SubFolder In F.SubFolders
InnerProc SubFolder, FSO
Next SubFolder
For Each OneFile In F.Files
Debug.Print OneFile.Path
If Right(OneFile.Name, 4) = ".xls" Then
Set WB = Workbooks.Open(Filename:=OneFile.Path)
'
' your code here
'
WB.Close savechanges:=True
End If
Next OneFile


End Sub