Combine Multiple Excel Files

Would you want all of the data from the imported files to be combined into one worksheet, or each file copied to it's own worksheet?

When the files are combined into one worksheet, do you want:

The data from each file kept separate.  This would be done by loading the data from each file into the next available row.

Or

The data consolidated for eaxample by using titles in the left hand column.

It seems that this problem might be solved by using the Data Consolidate function. Go to DATA and then click Consolidate.

Pivot Tables may also provide a solution

reply to nfrost: yes, all data should be combined into one worksheet

reply to packhorse: I need the data from each file loaded into the next available row in my template file

reply to tfspry: I don't want to consolidate data ... I want to append each file to the next available row in my template worksheet

Hi Jazzman2,

The following macro should do the job, in the example the name of the worksheet on the template file and on the other data files is "sheet1", if you have other names, just change any "sheet1" into your sheet name.
The MyFile = Dir("C:\MyTree1\MyTree2\TestEE\*.*") line, initiates the folder to look in for the files. Just adapt the name and path for your folder. The routine also checks if the template name is not in the folder, to exclude the possibility of calling itself (line If MyFile <> "MultiShTmpl.xls" ). I called the templatefile "MultiShTmpl.xls", which of course should be adapted also.


###############################################################################
Dim Filelist()
Sub AppendData()
Row = Worksheets("sheet1").UsedRange.Rows.Count
Col = Worksheets("sheet1").UsedRange.Columns.Count
n = 0
i = 0
MyFile = Dir("C:\MyTree1\MyTree2\TestEE\*.*")
While n = 0
If MyFile <> "" Then
If MyFile <> "MultiShTmpl.xls" Then
ReDim Preserve Filelist(i)
Filelist(i) = MyFile
MyFile = Dir
i = i + 1
Else
MyFile = Dir
End If
Else
n = 1
End If
Wend
f = UBound(Filelist) - LBound(Filelist) + 1
For i = 1 To f
Workbooks.Open ("C:\Mijn documenten\Computers Question_bestanden\TestEE\" & Filelist(i - 1))
NRow = Worksheets("sheet1").UsedRange.Rows.Count
Worksheets("sheet1").Range(Cells(2, 1), Cells(NRow, Col)).Copy
Workbooks(Filelist(i - 1)).Close
Worksheets("sheet1").Range(Cells(Row + 1, 1), Cells(Row + NRow - 1, Col)).Select
ActiveSheet.Paste
Row = Worksheets("sheet1").UsedRange.Rows.Count
Next i
End Sub



Hope this helps,

Calacuccia

reply to Calacuccia: Thanks, I tried your method and it does combine the files, however, I have to press "yes" on the clipboard message screen each time before a file is closed. This is a real problem if I'm copy/pasting hundreds of files.  Can you suggest how to prevent this?

Hi JazzMan,

Try this:

Add the (False) statement to the Workbooks("anything").Close event:

Workbooks(Filelist(i - 1)).Close (False)

reply 2 to Calacuccia: This doesn't work for me ... the clipboard message screen still appears requiring me to press "yes" each time before the file is closed. Do you have another approach?

Hi Calacuccia

It works! Thank you very much! I really appreciate your solutions and your prompt response.

A pleasure working with you.
Regards
Jazzman2

Calacuccia,

Thanks again!

Jazzman2

No Thanx, Jazzman2, Always happy to help,

See you, Calacuccia