MATLAB Answers

Write data to multiple sheets in Excel using xlswrite

50 views (last 30 days)
Kassi
Kassi on 23 Mar 2011
Hi everyone,
I'm having troubles using xlswrite when I want to add multiple sheets to one workbook. I believe the problem is a matter of memory allocation with Excel, because I found that an instance of EXCEL.EXE opens in the background (via task manager/processes) for each iteration of my for loop. After enough iterations, multiple instances can be open, thereby causing a crash. To circumvent this, I added a pause into my for loop, but I'm still having the issue. The reason for writing this code is that I was having to open 2-3 different excel files for each time frame I'm considering (and I'm analyzing at least 6 time periods) and then copy the contents to a new workbook -- this is a very time consuming process. Unfortunately, the end result must be stored within excel for others to use.
I am confident that my code is correct, because it's successfully writing at least two sheets -- depending on how much data I have for each time period (this changes from source to source).
Here's my code:
for ii=1:nmmonths
keep ii nmmonths
load wseasontotal.mat
pause(5)
xlswrite(savename,monthsheet,[windseason.month{ii}]);
end
This is the error I'm getting (after two or more sheets have been successfully written):
??? Error using ==> xlswrite at 213
No appropriate method, property, or field ReadOnly for class
Interface.Microsoft_Excel_12.0_Object_Library.Workbooks.Open.
Error in ==> combine_data at 131
xlswrite(savename,monthsheet,[windseason.month{ii}]);
I've tried adding
try
% If there's an EXISTING INSTANCE of EXCEL, then use it
Excel = actxGetRunningServer('Excel.Application');
flgRunning = 1;
catch
% Otherwise, create a NEW INSTANCE
Excel = actxserver('Excel.Application');
flgRunning = 0;
end
before calling xlswrite and then
if flgRunning == 0
Excel.set('DisplayAlerts',0);
Excel.Quit;
Excel.delete;
end
after. That gives me a new error (after successfully writing at least two sheets):
??? Error using ==> xlswrite at 213
Excel returned: No appropriate method, property, or field Count for class
Interface.Excel_Application.Sheets..
To make things even worse, it sometimes runs just fine, but other times it errors out. I'm at a loss. Help?
Thanks, Kassi

Accepted Answer

Kassi
Kassi on 29 Mar 2011
After speaking with Sharath Prabhal, it was determined that the add-ins I'm using in Excel are causing the problems. Disabling the add-ins (under excel options in excel 2007) allowed Matlab to run the code perfectly as originally written. I hope this helps someone else in the future :)
Kassi
  1 Comment
Michal Kotze
Michal Kotze on 18 Apr 2013
No appropriate method, property, or field Quit for class Interface.000208DA_0000_0000_C000_000000000046.
Error in mycode (line 75) WBook.Quit();
Ek this error. the code use to work, but know i keep on getting this error. All addins in excell id of? I need help please
% Quit Excel

Sign in to comment.

More Answers (0)

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!