Concatinate Excel Files

D

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
 
B

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
 
Top