Short answer: yes, I think. I presume you want the workbook that is going to
have the aggregated data placed into it do the opening and extracting from
the other workbooks?

Couple of ways to attack it, easiest is if the 'control' or new aggregate
workbook is in the same folder with the others. Then with VBA code you could
have it determine it's own location (and thus the location of the other
workbooks) and using the DIR() function in VBA, you would examine the
filenames of other workbooks in the same folder and when you found one with
Sep2006 within it that is of type .xls, then open it, grab the information
you need, place it into the control workbook, close it and look for another
until it runs out of them. Reiterative DIR() basically goes thru each
possible match until it runs out of possible matches, then returns an empty
string, so don't even have to worry about processing same book twice (during
one run of the code).

Code might look something like this:

Sub FindSpecialFiles()
Dim anyFile As String

anyFile = UCase(Dir(Left(ThisWorkbook.FullName, _
Len(ThisWorkbook.FullName) - _
Len(ThisWorkbook.Name)) & "*.xls"))

Do While anyFile < ""
'remember strings are case sensitive
If InStr(anyFile, "SEP2006") Then
'....perform file open,
'....data extraction
'....data aggregation
'....close Sep2006 file
End If
anyFile = UCase(Dir) ' get next possible file
Loop

End Sub

I used UCase and made the comparison to "SEP2006" since strings are case
sensitive in VB and the UCase assures that the returned filename(s) are all
in uppercase.

" wrote:

Is there a way to look in a folder and if a file has a certain string
in it's name "Sep2006", it will open. I don't care what else it has in
the file name, but if that string is in there, I want it to open.

Basically, I'm going to have a sheet for say 10 people with their name
and Sep2006 in a folder. I want to then open 1 sheet at a time, take a
value from it, and aggregate it into a new sheet.

Thanks,
Brett