Discover MakerZone

MATLAB and Simulink resources for Arduino, LEGO, and Raspberry Pi

Learn more

Discover what MATLAB® can do for your career.

Opportunities for recent engineering grads.

Apply Today

New to MATLAB?

memory leak using activex to save multiple excel files

Asked by Rhys

Rhys (view profile)

on 30 Nov 2012

I have been trying to make a code that will save 1000's of excel files containing data analysed from csv files. I have a base excel file I write the data to, run several macros to make charts etc and then I save the file. I then make a copy of the file (which I store away) and I clear the data in my base excel file by running a macro called 'cleanup' so I'm ready to repeat these steps with the next set of data. I have done it this way to avoid opening and closing connections all over the place in order to make things run faster. However there appears to be a memory leak and although things start off fast the code eventually slows to a crawl as excel starts to hog well over a GB of memory. Is there a way to stop the memory build up in excel - there should only ever be the one connection so I don't understand how it uses an increasing amount of memory. (edit: the individual files made are fairly small ~ 1MB)

Thanks.

The initial connection is set up as:

if true
e = actxserver ('Excel.Application');
set(e,'DisplayAlerts',0);
set(e, 'Visible', 0);
invoke(e.Workbooks,'Open',file_template);
end

and for every file the following is called:

if true
function excelwrite( file , data , macro , macroName , e)
% write data to the excel file      
[m,n] = size(data);
range = '';
%calcrange is borrowed from 'xlswrite'.
range = calcrange(range,m,n);
Select(Range(e,sprintf('%s',range)));
set(e.selection,'Value',data);
% if macro is set to 1 then run the macros in the array: 'macroName'
if macro == 1
  for i = 1:length(macroName(:,1))
      e.Run(macroName(i,:));  
  end
end
%save, make a copy, and delete data from active workbook.
e.ActiveWorkbook.Save
copyfile(e.activeWorkbook.fullname,file_out);
e.Run('cleanup');
end

2 Comments

Jan Simon

Jan Simon (view profile)

on 3 Dec 2012

The "if true" is useless. I suggest to omit it, when you post code in a forum.

Are you sure that Matlab occupies the memory? Or could it be Excel, which does not cleanup as wanted? In the later case, an Excel forum would be a better location to ask.

Rhys

Rhys (view profile)

on 4 Dec 2012

Thanks for taking a look. It is indeed the Excel application that can be seen to use increasingly large amounts of memory even though the amount of data in the file doesn't increase. I was just wondering if this was a known issue with using ActiveX within from Matlab or if there was something obviously wrong with the code that I am not spotting. I'll try and look into the Excel part of it more.

Rhys

Rhys (view profile)

Products

No products are associated with this question.

1 Answer

Answer by Mark Whirdy

Mark Whirdy (view profile)

on 4 Dec 2012

are you not closing the workbooks?

2 Comments

Image Analyst

Image Analyst (view profile)

on 4 Dec 2012

That would certainly cause the problem!

Rhys

Rhys (view profile)

on 6 Dec 2012

There is only ever one workbook open and I keep it open for the duration of the program. I perform a process along the lines of: write data to the open workbook, save the workbook, make copy of the excel file (that gets kept for later), clear the workbook (via a macro), write new data to the workbook, save it, and so on. In task manager you can see that the memory being used by the instance of Excel keeps on increasing. I have had a look on some Excel forums and found a few similar issues, but no definitive solutions. Currently I have "fixed" the issue by periodically closing and reopening the excel file with activex after every 100 writes. This is a bit of a fudge, but gives me the speed increase of not opening and closing the workbook constantly whilst not killing my computer with memory issues.

Mark Whirdy

Mark Whirdy (view profile)

Contact us