MATLAB Answers

memory leak using activex to save multiple excel files

2 views (last 30 days)
Rhys 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)
The initial connection is set up as:
if true
e = actxserver ('Excel.Application');
set(e, 'Visible', 0);
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);
% if macro is set to 1 then run the macros in the array: 'macroName'
if macro == 1
for i = 1:length(macroName(:,1))
%save, make a copy, and delete data from active workbook.
Rhys 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.

Sign in to comment.

Answers (1)

Mark Whirdy
Mark Whirdy on 4 Dec 2012
are you not closing the workbooks?
Rhys 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.

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!