MATLAB Answers

How to use Xlswrite command in actxserver and how to avoid script gets hanged due to hworkbook.close command?

35 views (last 30 days)
Hi
I have two queries in the below codes.
Query 1: How to involve the xlswrite command in actxserver.
FYI: Names{gp} --> Heading name to be placed in excel sheet from A1.
data_peak{gp} --> data to be placed in excel sheet from A2.
gp --> used to work in for loop.
Somehow i made with xlswrite through conventional method and autofit the columns thorugh actxserver. But the time will be saved if xlswrite command also to be invloved in the actxserver itself.
Query 2: When i used to close the workbook using hWorkbook.Close command, the script gets hanged and it doesn't go to the next line. I dont know th reason why it is hanged and it doesnot show any warning or error.
xlswrite(fullFileName,T,Names{gp},'A1'); % Title on the first row of excel sheet
xlswrite(fullFileName,data_peak{gp},Names{gp},'A2'); % Data on the second row of excel sheet
%% Excel application - actxserver
hExcel = actxserver('Excel.Application');
hWorkbook = hExcel.Workbooks.Open(fullFileName);
% Select the entire spreadsheet.
hExcel.Cells.Select;
% Auto fit all the columns.
hExcel.Cells.EntireColumn.AutoFit;
% Center align the cell contents.
hExcel.Selection.HorizontalAlignment = 3;
hExcel.Selection.VerticalAlignment = 2;
% Put "cursor" or active cell at A1, the upper left cell.
hExcel.Range('A1').Select;
hWorkbook.Save
% delete the sheets
try
sheetName = 'Sheet';
hExcel.ActiveWorkbook.Worksheets.Item([sheetName '1']).Delete;
catch ME
end
hWorkbook.Close
hExcel.Quit
hExcel.delete
clear hExcel
Kindly provide me some ideas to get rid of these two issues and let me know if you need any further information.
Thanks in advance.
  3 Comments

Sign in to comment.

Accepted Answer

dpb
dpb on 28 Feb 2021
". I just need to avoid xlswrite in my command"
Indeed. You don't want to be running dueling ActiveX processors at the same time, particularly on the same object. There is a FEX submission that allows one to avoid the repetitive open/close cycle that works nicely if you obey the rules.
However, until you get your code thoroughly debugged, you'll probably have to kill zombie orphaned processes manually when things fail internally and the cleanup code doesn't manage to shutdown everything gracefully.
See <fileexchange/69745-xlswriteex>. I have used it quite successfully in a pretty complex operation and recommend it highly with the above caveat that you'll undoubtedly find things don't always work as expected while you're developing the code so be prepared to use the system monitor to kill zombie processes.
  1 Comment
dpb
dpb on 28 Feb 2021
And, of course, be certain you have backed up any valuable data ere starting this process!!! You WILL destroy whatever you're working on multiple times before it's debugged!!!

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!

Translated by