There are a few ways to do this.
’s Dir() function is popular. You specify a file spec on the first call, then provide no argument on subsequent calls. Each call will find the next file that fits the spec. When there are no more that match, calling Dir() returns "". If you call it without arguments after that, and error is raised.
Dim fNam As String
fNam = Dir("*.xls")
Do Until fNam = ""
' Do your processing here
fNam = Dir()
In the "Do processing here" you would put a call to a routine that has the file name as an argument, and which opens the spreadsheet, and processes its values, then closes it.
Excel will let you open more than one spreadsheet at a time, so you can have this code in one spreadsheet, which then opens additional spreadsheets for processing. Those sheets would be accessed through the collection of sheets, Application.Sheets("<[green]Name you used to open it here