Concatinate Excel Files

[Don]

Does anyone know a procedure to concatinate multiple
EXCEL workbooks into one workbook? I have 350 individual
workbook files (each with only one worksheet) that all
need to be combined into one worksheet. They all have the
same formatting and column layout.

TIA!

Don

 

[Jim Rech]

You might look at this tool from MS:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;181148

 

[Bernie Deitrick]

Don,

With all the files in one folder, the sub below will copy all the
files into one file. Note that the assumption is the the data is in a
single block and that it starts in cell A1. Change the folder name
where indicated. With 300 files, it may take a while, and this version
will blow up if you have more than 65536 lines of data in total.

HTH,
Bernie
MS Excel MVP

Sub Consolidate()
With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

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("A65536").End(xlUp)(2)
myBook.Close
Next i
Basebook.SaveAs Application.GetSaveAsFilename("Consolidated
file.xls")
End If
End With

With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub