How to run macro in a different workbook
24 views (last 30 days)
Varun Lakshmanan on 15 Jul 2019
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;
Sheets = excelAll.ActiveWorkBook.Sheets;
sheet1 = get(Sheets, 'Item', sheetnum);
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.
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