Writing to Excel sheet from Excel Add-In function

Hello!

I am currently testing a few things for a project which will be deployed as an Excel Add-In.

What I was trying to do is create a function xlaTest(rng) which returns a calculated value from an input range. That works fine.

However, when I try to access the Excel sheet from the Matlab function through the COM server as you'd normally do, I get a "Call was rejected by callee" error. The code inside the Matlab function is the following:

% Get Excel application
xl = actxGetRunningServer('Excel.Application');
% Get active worksheet
xlsheet = get(xl, 'Activesheet');
% Get target cell
xlcell = get(xlsh, 'Range', 'F10');

% Paste figure
res = xlcell.PasteSpecial;

The code works fine when executed from the Matlab command window or also a script, but inside the Excel Add-In it returns the aforementioned "Call was rejected by callee" error on the get(xl, 'Activesheet') statement.

I already tried to disable Virus Scanner, insert a pause after the actxGetRunningServer, but to no avail... Has someone got a solution for this?

Answer 1

[Guillaume]

Note that there's no need to go through get, you can simply your code to:

xl = actxGetRunningServer('Excel.Application');
xlsheet = xl.Activesheet;
xlcell = xlsheet.Range('F10')
res = xlcell.PasteSpecial;

With regards to your problem, the error is issued by Excel, which for some reason can't do what you ask. A possible reason for this is that a modal dialog box is waiting for some input. As excel is not visible by default, you don't see the dialog waiting for your input. So the first thing to do is to make excel visible:

xl = actxGetRunningServer('Excel.Application');
xl.Visible = true;
%rest of the code

If it is indeed the problem, you can tell excel to not display dialogs (and take whichever action is default for that dialog) with:

xl.DisplayAlerts = false;

[Stefan]

I've already tried to make it visible first, but to no avail, the error then pops up already at the xl.Visible = true command.

The reason why I went the get/set route was that it doesn't seem to recognize the methods without the get/set commands.

To better explain this, when I type

xl = actxGetRunningServer('Excel.Application');
xl.Visible = true;

the error I get is "Unrecognized property 'Visible' for class 'COM.Excel_Application'".

When I type

xl = actxGetRunningServer('Excel.Application');
set(xl, 'Visible', true);

I get the call rejected error.

I thought it might have something to do with the fact that when a cell gets edited, Excel tries to update all cells while it is still in editing mode and exits the editing mode only afterwards. Then the error would be explained by the fact that Excel is still in cell editing mode while I try to access it from the Matlab function, but then I don't have any clue how it should be possible to make it work...

The only workaround I can imagine would be that the function is called via macro by pushing an "Update" button in Excel, but for my purpose that would be bothersome and not user friendly...

[Guillaume]

Oh! I completetly missed that you're connecting to a running instance of excel (i.e. you're using actxGetRunningServer, not actxserver).

Yes, I'm not sure that it's ever going to work since it's within an add-in, You're asking excel to connect to itself.

I've never written add-ins for excel (and don't have the required toolbox), but surely within the realm of an add-in you're already in an excel environment so there must be a way to get to the worksheets, workbooks, etc directly. You certainly shouldn't have to go through the COM interface to do that.

Answer 2

[Joseph Long]

Has anyone found a solution to this? I need to do a similiar process. I want to process a monte carlo in matlab add-in. On each iteration I want to update a worksheet with the results.

Answer 3

[Mehdi Songhori]

"Error: Call was rejected by callee." is an error from the Excell application, and there is nothing to do with it in MatLab, except ignoring it. It happens when Excel is not responding due to updating its cell values, using add-ins, or other means. For example, when we want to read a cell value, write something in a cell, and close or save an open workbook, the error shows up. It frequently happens when we use a loop, and Excell is updating its cell values every few moments. So the best way is to wait until Excel responds to our request from Matlab. Making a subfunction and using try/catch error within this function would be the best solution.

function res = PasteExcel(xlcell)
try
    res = xlcell.PasteSpecial;
catch
    res = PasteExcel(xlcell)
end
end

The function above keeps running until Excel responds to MatLab, and the error is gone.

The PasteSpecial function may be substituted by another excel cell or workbook-related functions when needed.