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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.