Summing Multiple Excel Sheets into One Table.

Hello, I have an excel file with 1000+ sheets, and each sheet has a 71x301 data matrix. I want to sum all those matrices into a single one. I tried to write an Excel macro but couldn't do it, so I thought I might give Matlab a chance for the task.

I think I have to create a loop that calls sheetnames one by one (the sheetnames are not in a pattern so I can't create a string loop to call the names) So I guess I have to count the sheets in the file and make Matlab process each sheet with its order on Excel.

If someone can offer some guidance with key functions, I will be faithful.

Thanks in advance, Al

Answer 1

[the cyclist]

The primary function you will need is called xlsread. I suggest you read the documentation for that

doc xlsread

and that should get you started. If you get stuck again, I recommend you launch a new, more specific question, perhaps including the coding you've already attempted.

[Friedrich]

I think xlsread is bad idea for that because this approach will result in a loop which calls xlsread, which opens and closes excel in each iteration.

You have to call excel through COM manually and get the data manually in order to get a good runtime behavior.

So start with ex = actxserver('excel.application') and proceed from there. You can look at the code of xlsread in order to see how to open files, access worksheets etc.

[Image Analyst]

Friedrich is right. You must not use xlsread for more than about 5 worksheets or you'll be waiting an extremely long time. Each call to xlsread causes Excel to launch, then read in the data, and shutdown Excel. ActiveX is the way to go since you only need to open Excel once and it's lightning fast. Perhaps I'll post a demo.

Answer 2

[Image Analyst]

See this file exchange entry: http://www.mathworks.com/matlabcentral/fileexchange/22365-xlsread-m-for-faster-data-transfer

There is a way to get the name of each spreadsheet, though you don't need it (like you said) since you can access (switch to) each sheet based on it's number (1, 2, 3, etc. up to 1000 or whatever you have). If you need the name also, let me know and I can get the code to do that off my other computer - I don't remember off the top of my head.