Why can I not call Add-in macros when using Excel as a COM Automation Server from MATLAB 7.4 (R2007a)?

I am trying using Excel as a COM Automation Server. When I try to execute a macro from one of my Add-ins:

Excel = actxserver('Excel.Application');
Workbooks = get(Excel,'Workbooks');
fWorkbook = invoke(Workbooks,'Add');
fWorksheet = get(fWorkbook,'Activesheet');

fRange = fWorksheet.Range('A1:B2');
fRange.Value{1,1} = 'name';
fRange.Value{2,1} = 'foo';
fRange.Value{1,2} = 'amount';
fRange.Value{2,2} = 100;

Excel.Run('myMacro');

I receive the following error:

ERROR: % ??? Invoke Error, Dispatch Exception: 
% Source: Microsoft Office Excel
% Description: The macro 'myMacro' cannot be found.
% Help File: D:\Applications\MSOffice\OFFICE11\1033\xlmain11.chm
% Help Context ID: 0

Answer 1

[MathWorks Support Team]

This is the expected behavior. Microsoft Excel does not load Add-ins when started as a COM Automation server. This behavior is not specific to use with MATLAB.

Microsoft has information about the issue, the reasons behind it, and workarounds here:

https://mskb.pkisolutions.com/kb/213489

The following MATLAB code uses the same method. Note that Microsoft's solution involves specifying the path to the XLA-file; assuming the file resides in D:\Work.

%%\nExcel = actxserver('Excel.Application');\nWorkbooks = get(Excel,'Workbooks');\nfWorkbook = invoke(Workbooks,'Add');\nfWorksheet = get(fWorkbook,'Activesheet');\n\nfRange = fWorksheet.Range('A1:B2');\nfRange.Value{1,1} = 'name';\nfRange.Value{2,1} = 'foo';\nfRange.Value{1,2} = 'amount';\nfRange.Value{2,2} = 100;\n\n%% Now load the add-in, using the method suggested by Microsoft:\n% https://mskb.pkisolutions.com/kb/213489% Open file on disk\nExcel.Workbooks.Open('D:\Work\myAddin.xla');\n\n% Use this method to execute any Auto_Run macros.\nExcel.Workbooks.Item('myAddin.xla').RunAutoMacros(1);\n\n% Now that the Add-in is loaded, execute the macro it contains:\nExcel.Run('myMacro');\n\n%% Finally, make Excel visible.\nset(Excel,'Vis',1);