Merging 800 Excel files

[Kjetil]

I have 800 (!) Excel files - all organized the same way.

They may contain 2 to - let say - 100 rows. Only columns A through C are used.

My goal is to gather all this info into one file. However, i would like the name of the original file to be placed in Column D (for every row), ie:


File1.xls:
Fruit;Weight;Qty
Apple;5;20
Banana;1;4
Peach;3;9

File2.xls:
Fruit;Weight;Qty
Pear;4;17
Plum;2;30

MergedFile.xls:
Fruit;Weight;Qty;File1.xls
Apple;5;20;File1.xls
Banana;1;4;File1.xls
Peach;3;9;File1.xls
Fruit;Weight;Qty;File2.xls
Pear;4;17;File2.xls
Plum;2;30;File2.xls

I assume a macro could handle this, but i lack the necessary knowledge, so any suggestions would be heighly appreciated. Any conditions like "all excel files should be opened on the working computer before macro is run" are no problem.

 

[Ron de Bruin]

Check out this page for code and a add-in
http://www.rondebruin.nl/copy3.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


I have 800 (!) Excel files - all organized the same way.

They may contain 2 to - let say - 100 rows. Only columns A through C are used.

My goal is to gather all this info into one file. However, i would like the name of the original file to be placed in Column D (for every row), ie:


File1.xls:
Fruit;Weight;Qty
Apple;5;20
Banana;1;4
Peach;3;9

File2.xls:
Fruit;Weight;Qty
Pear;4;17
Plum;2;30

MergedFile.xls:
Fruit;Weight;Qty;File1.xls
Apple;5;20;File1.xls
Banana;1;4;File1.xls
Peach;3;9;File1.xls
Fruit;Weight;Qty;File2.xls
Pear;4;17;File2.xls
Plum;2;30;File2.xls

I assume a macro could handle this, but i lack the necessary knowledge, so any suggestions would be heighly appreciated. Any conditions like "all excel files should be opened on the working computer before macro is run" are no problem.