Here is a macro that will merge all sheets of all files in a single folder
into one workbook.  If you want all the data on one, worksheet, then run the
second macro, which will copy the block of cells that includes A1 onto a
single sheet (the assumption being that the data is in a database starting in
A1 and with no blank rows or columns).

HTH,
Bernie

Sub ConsolidateAllSheetsIntoOneSpreadSheet()
Dim mySht As Worksheet
With Application.FileSearch
    .NewSearch
    'Change this to your directory
    .LookIn = "C:\Excel"
    .SearchSubFolders = False
    .FileType = msoFileTypeExcelWorkbooks
    If .Execute() > 0 Then
        For i = 1 To .FoundFiles.Count
            OpenIt = MsgBox("File: " & .FoundFiles(i) & Chr(13) & _
            "Do you want to include this?", vbYesNo)
            If OpenIt = vbYes Then
                Workbooks.Open .FoundFiles(i)
                For Each mySht In ActiveWorkbook.Worksheets
                    mySht.Name = ActiveWorkbook.Name & "- " & mySht.Name
                    mySht.Select (False)
                Next mySht
                ActiveWindow.SelectedSheets.Move After:=ThisWorkbook.Sheets(1)

            End If
        Next i
        Else: MsgBox "There were no files found."
    End If

End With

End Sub

Sub ConsolidateFirstSheetIntoOneSheet()

With Application.FileSearch
    .NewSearch
    'Change this to your directory
    .LookIn = "C:\Excel"
    .SearchSubFolders = False
    .FileType = msoFileTypeExcelWorkbooks
    If .Execute() > 0 Then
    Set Basebook = Workbooks.Open(.FoundFiles(1))
        For i = 2 To .FoundFiles.Count
            Set myBook = Workbooks.Open(.FoundFiles(i))
            Range("A1").CurrentRegion.Copy
Basebook.Worksheets(1).Range("a1").End(xlDown).Offset(1, 0)
            myBook.Close
         Next i
        Basebook.SaveAs Application.GetSaveAsFilename("Consolidated file.xls")

    End If
End With

End Sub