How to run macro in a different workbook

24 views (last 30 days)
I have a macro saved as an xlsm file that I would like to use on a different Excel workbook. How can I do this using ActiveX?
This is the code for the workbook that I want to apply the macro to. What do I have to add on to this?
excelAll = actxserver('Excel.Application');
Workbooks = excelAll.Workbooks;
excelAll.Visible=1;
excelAll.Workbooks.Open(filename);
sheetnum=1;
range='A1:ZZZ11041';
Sheets = excelAll.ActiveWorkBook.Sheets;
sheet1 = get(Sheets, 'Item', sheetnum);
invoke(sheet1, 'Activate');
Activesheet = excelAll.Activesheet;
I have already looked at this question (https://www.mathworks.com/matlabcentral/answers/58159-running-excel-macros-with-matlab-activex#answer_70392), but the solution provided can only be used to run a macro within the same workbook, not to run a macro on a different workbook.
  2 Comments
Varun Lakshmanan
Varun Lakshmanan on 15 Jul 2019
Hi Guillaume. In Excel, it can be done just by opening the workbook and running the VBA code Application.Run "BookWithMacro!MacroToRun". Unfortunately, I haven't found any equivalent code in Matlab to do this. This answer (https://www.mathworks.com/matlabcentral/answers/58159-running-excel-macros-with-matlab-activex#answer_70392) doesn't work for me.

Sign in to comment.

Accepted Answer

Guillaume
Guillaume on 15 Jul 2019
If the macro is supposed to act on the first workbook, the only way your excel procedure can work is if the macro rely on the ActiveWorksheet. In general, it's a bad idea to rely on ActiveAnything as that may change under your feet simply by user interaction. Same thing with the matlab code, which also rely on what's active when it could simply pass the reference to the desired sheet/workbook.
Anyway, this should reproduce the same behaviour:
%macrofile: full path of the excel workbook with a macro. xlsm file
%macroname: name of macro to run in macrofile.
%targetfile: full path of the excel workbook the macro targets. xlsx file
excel = actxserver('Excel.Application'); %stat excel
excel.Visible = true; %optional
macrobook = excel.Workbooks.Open(macrofile); %open macro book
targetbook = excel.Workbooks.Open(targetfile); %open target book
targetbook.Activate; %make sure target book is active. Should already be the case
excel.Run([macrobook.Name, '!', macroname]); %run macro in macrobook
%....
excel.Quit; %close excel when done
  5 Comments
Varun Lakshmanan
Varun Lakshmanan on 16 Jul 2019
Thank you so much for your help! This was the problem.

Sign in to comment.

More Answers (0)

Products


Release

R2018a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by