MATLAB: How to run an Excel macro from MATLAB

activexMATLAB

I am using MATLAB 7.5 (R2007b) and would like to run an Excel Macro from MATLAB.

Best Answer

One way to invoke an Excel macro from MATLAB is by launching Excel as a COM server from MATLAB using the 'actxserver' command.
Suppose there is an Excel file 'myFile.xls' containing the macro called 'Macro1'. The following commands should first open the Excel file and then run the macro:
% Create object.
ExcelApp = actxserver('Excel.Application');
% Show window (optional).
ExcelApp.Visible = 1;
% Open file located in the current folder.
ExcelApp.Workbooks.Open(fullfile(pwd,'\myFile.xls'));
% Run Macro1, defined in "ThisWorkBook" with one parameter. A return value cannot be retrieved.
ExcelApp.Run('ThisWorkBook.Macro1', parameter1);
% Run Macro2, defined in "Sheet1" with two parameters. A return value cannot be retrieved.
ExcelApp.Run('Sheet1.Macro2', parameter1, parameter2);
% Run Macro3, defined in the module "Module1" with no parameters and a return value.
retVal = ExcelApp.Run('Macro3');
% Quit application and release object.
ExcelApp.Quit;
ExcelApp.release;
Please note the following:
- Macros defined in a sheet, ThisWorkBook or a module can be called with zero or more arguments depending on the method's signature.
- Only functions (not subs) defined in a module can return a value. An attempt to retrieve a value from a function defined in a sheet or ThisWorkBook will return "NaN" in MATLAB or "Empty" in VB/VBA.
Related Question