How can I run an Excel macro from MATLAB?

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

Answer 1

[MathWorks Support Team]

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.

[Giuseppe Naselli]

Hi Mathworks Support Team!

thanks for asking (and answering :) this question.

I am using this process in and it works fine. However, the macro I wish to run in Excel is linked to a button (in one of the Excel worksheets) and when the Excel user push the button the macro starts by prompting a window with a question (see below)

When I normally run the macro from Excel, I answer YES and then the macro carries on producing the results expected.

My question is, how can I can I say Matlab to answer YES so that te macro can carry on? (below my code)

% 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 Macro3, defined in the module "Module1" with no parameters and a return value.
retVal = ExcelApp.Run('Macro3');

% at this point the program does not do anything as Excel is waiting for an answer

Thanks in advance for your help

G

[Giuseppe Naselli]

sorry I forgot to say, I am using R2013a G

[dpb]

Several of the almost limitless possibilities...just in case somebody else stumbles upon the thread as it comes up early in a search...

1. @Brad LaCroix didn't show us the whole code, but the snippet he showed doesn't include actually opening the workbook containing the code module in which case there's no object going to be found...
2. Neither you nor he showed us the footprint of the VBA code you're trying to execute so we can't tell if your code matches or not..
3. Particularly the sub @Brad LaCroix describes probably depends upon the location of the active or selected cell in the worksheet at the time the macro is called -- that it looks for "nearby column" means it is going to be dependent upon what it does internally for that lookup...where/how it is invoked when tested inside Excel is likely quite different than the state of the worksheet at the time he tries to call the routine from MATLAB.