copy first sheet from all workbooks in folder

Hi All

Hopefully someone can help with this one. I need to copy sheet1 from all workbooks in a folder :

i.e. "c:\steel_monkey\excel\example\" may have 4 different workbooks in it, is it possible to open each of these and copy sheet1 into my master spreadsheet using VBA

any help would be appreciated

Thanks!

As this will need to be done on a weekly basis and the workbooks will change every week, that wont exactly work.

What i need to know is how to get excel to open ALL files in a folder, and copy the first sheet into a master workbook, so if the workbook names change on a weekly basis then it wont matter

if anyone is interested here is how i did it :

Sub filesearch()
Dim fname As String
Dim sheetname As String

   

Range("A2").Select
Set fs = Application.filesearch
With fs
    .LookIn = "Q:\Steel Monkey\excel\"
    .Filename = "*.xls"
    If .Execute(SortBy:=msoSortByFileName, _
    SortOrder:=msoSortOrderAscending) > 0 Then
        For i = 1 To .FoundFiles.Count
                fname = .FoundFiles(i)
                    If ActiveCell.Value <> fname Then
                    ActiveCell.Value = fname
                    Workbooks.Open Filename:="" & fname & ""
                    Cells.Copy
                    activeworkbook.close
                    Workbooks("Master.xls").Activate
                    Sheets.Add
                    Cells.PasteSpecial xlPasteAll
                    sheetname = Range("D3").Value
                    ActiveSheet.Name = sheetname
                    Sheets("Filelist").Select
                    ActiveCell.Offset(1, 0).Select
            Else
                ActiveCell.Offset(1, 0).Select
            End If
        Next i
    Else
        MsgBox "There were no files found."
    End If
End With
End Sub

if anyone knows a better way to do this feel free to let me know as this probably isnt the best way!

filesearch does not work in Excel 2007.... Do you know another way to copy the first worksheet in a set of workbooks and consolidate them into one?