How can I run an Excel macro from MATLAB?
110 views (last 30 days)
Show older comments
MathWorks Support Team
on 20 Oct 2009
Commented: dpb
on 7 Jan 2023
I am using MATLAB 7.5 (R2007b) and would like to run an Excel Macro from MATLAB.
Accepted Answer
MathWorks Support Team
on 20 Oct 2009
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.
3 Comments
dpb
on 7 Jan 2023
Several of the almost limitless possibilities...just in case somebody else stumbles upon the thread as it comes up early in a search...
- @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...
- 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..
- 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.
More Answers (0)
See Also
Categories
Find more on Data Import from MATLAB in Help Center and File Exchange
Products
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!