Combine Multiple Excel Files

Is there an automatic way to combine multiple Excel spreadsheet files into a single template file using visual basic code stored within the template file?

The conditions would be;
1.)The template file and the individual files would all have the same column header row
2.)Files to be combined would be in a specific folder
3.)Individual files would vary in size (i.e. number of rows of data would vary)
jazzman2Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
calacucciaConnect With a Mentor Commented:
Re Jazzman2,

The reason for this clipboard message is that the contents are too big to ignore it's data being lost for Windows, in following code part, I just copty one cell into the clipboard to empty previous large contents. This should get the errative message away (at least on my example).

Just add following code just after the line

Worksheets("sheet1").Range(Cells(2, 1), Cells(NRow, Col)).Copy

######################################
Workbooks("MultiShTmpl.xls").Activate
Worksheets("sheet1").Range(Cells(Row + 1, 1), Cells(Row + NRow - 1, Col)).Select
ActiveSheet.Paste
Worksheets("sheet1").Range("A1").Copy
Workbooks(Filelist(i - 1)).Close
######################################

And then continue with original macro:

Row = Worksheets("sheet1").UsedRange.Rows.Count
Next i
End Sub



Good Luck,

Calacuccia


0
 
nfrostCommented:
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?
0
 
packhorseCommented:
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.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
tfspryCommented:
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

0
 
jazzman2Author Commented:
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
0
 
calacucciaCommented:
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
0
 
jazzman2Author Commented:
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?
0
 
calacucciaCommented:
Hi JazzMan,

Try this:

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

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

0
 
jazzman2Author Commented:
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?
0
 
jazzman2Author Commented:
Hi Calacuccia

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

A pleasure working with you.
Regards
Jazzman2

0
 
jazzman2Author Commented:
Calacuccia,

Thanks again!

Jazzman2
0
 
calacucciaCommented:
No Thanx, Jazzman2, Always happy to help,

See you, Calacuccia
0
All Courses

From novice to tech pro — start learning today.