Combine multiple excel sheets of a workbook into a single sheet.
2 views (last 30 days)
Show older comments
Hi!
I have a excel workbook containing more than 200 worksheets, which contain information in the same format. I would like to combine the sheets into a single worksheet such that the content A2:O29 of all the other sheets occurs below the content of sheet1 .
Can anyone help?
3 Comments
Mohammad Monfared
on 29 Oct 2013
to me using 'xlsread' is so simple with almost nothing to do yourself but writing few simple code lines. In 'xlsread' you can specify from which sheet you want to read data (I think in 2nd argument, see its documentation). so this could be done simply in a for loop for entire sheets.
Answers (1)
Image Analyst
on 28 Oct 2013
Edited: Image Analyst
on 28 Oct 2013
Don't use xlsread()! It will take forever (well, at least many minutes because you'd need to launch and shutdown Excel 200 times). It's best to use ActiveX , like in the demo attached below. If you also take a look at the Excel utility I wrote to transfer all worksheets in one workbook to another workbook, you'll be able to see how to copy a worksheet. So then all you need to do is to paste the copied worksheets into a worksheet where you keep track of what starting row you need to paste it in to. Not too hard, but might take a half hour of programming (by you) or so.
2 Comments
Image Analyst
on 28 Oct 2013
The simple way is to use xlsread, but it will take a very very long time. And you'd still have to keep track of what row to write into. If you run your program more than a few times, you'll definitely be seeing how you can learn activeX, which will be less time than waiting for the xlsread/xlswrite to finish. How long will it take to launch Excel 400 times and shutdown Excel 400 times on your computer? Because it will do that for every one of the 200 calls to xlsread or xlswrite. I bet you're smart enough to learn ActiveX. Heck if I did it, anyone can do it, or at least a smart engineer like you.
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!