Merging multiple excel files into one

I want to merge files of same name stored in different folders. All folders are stored at common directory path for e.g. C:/my documents/data. This then has all subfolders which contain excel sheets of same name. For e.g. extract.xls stored in folder 1, folder 2, folder 3 etc..

Structure of extract.xls file is: each file has same five tabs (worksheets- named tab1, tab2, tab3, tab4, tab5). Each tab has data spread between columns A and BN (which is max column name). The data stored by the number of rows would change for each tab under each file. The main data starts from row#7 of each tab within each file. However Cells A1 until C2 are filled with some file related information in each tab of each file.

I need to copy this data (from row#7 onwards) stored in one tab (e.g. tab1) of one file (extract.xls under folder 1) into a master file (which is stored at common path) which will have same tab name (i.e.tab1). Underneath this data I need to copy data from tab1 for second file (extract.xls stored at folder 2).

Please can you help?

Thanks.

[Guillaume]

This is not a particularly difficult task but it would still require significant time investment to write and debug that I doubt anybody is going to do it for you.

What we can help with is specific points that cause you problem, so I'd recommend you make a start and when stuck come back and edit your question.

To do your task efficiently you would just be using matlab to automate excel using the excel object model so to be honest, you may as well write the code in excel VBA.

The other option would be to load the excel data into matlab (using xlsread or readtable) do the merging in matlab and write back to excel. This would be a lot slower, would lose all formatting and I think would actually be more difficult to get correct.

[Arijeet Sinha]

I have been writing the code. But i can't find solution for adding theme one by one column wise.