memory leak using activex to save multiple excel files

3 次查看（过去 30 天）
Rhys
Rhys 2012-11-30
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 个评论
Jan
Jan 2012-12-3
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 2012-12-4
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.

请先登录，再进行评论。

请先登录，再回答此问题。

回答（1 个）

Mark Whirdy
Mark Whirdy 2012-12-4
are you not closing the workbooks?
  2 个评论
Rhys
Rhys 2012-12-6
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.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 ActiveX 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by