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

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.

[Mario Malic]

You're not supposed to run actxserver xlswrite inside of actxserver, Excel has its own methods to do so.

You can run this line as well to see what is going on in Excel so you can check if you do it okay or not.

hExcel.Visible = 1;

Secondly, why it doesn't want to close, probably because you get a prompt that you can't see because property Visible is off. Also, try to work with ActiveWorkbook property in the command.

This line below is fixed, use scalar with Item to select the worksheet.

hExcel.ActiveWorkbook.Worksheets.Item(1).Delete;

Look here for available methods https://docs.microsoft.com/en-us/office/vba/api/overview/excel/object-model that you can use.

[Gopinath Karuppannan]

Thanks for the quick response Mario.

I have resolved the second query, a popup was displayed in background. So i just eliminated.

In First query, if xlswrite is not supposed to run in actxserver then, is there any other command is available to write in excel inside of actxserver excel application. Because my code is gonna work in for loop. So everytime it crosses the xlswrite command, it takes time to write in excel. I just need to avoid xlswrite in my command. kindly let me know if have any suggestions.

Thanks

[Mario Malic]

Take a look at https://docs.microsoft.com/en-us/office/vba/api/word.selection.typetext

There is one similar qustions here, https://ch.mathworks.com/matlabcentral/answers/753779-creating-tables-in-word-using-actxserver?s_tid=srchtitle

Search in other places, I am sure you'll find a proper way to do this.

Answer 1

[dpb]

". 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.

[dpb]

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!!!

Answer 2

[Image Analyst]

I don't think you need th close function. Just delete that line and it should shutdown just fine.