How to run macro in a different workbook

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.

[Guillaume]

In excel, is it possible to run that macro against another workbook? If so, how do you do it?

If not, then it won't be possible in matlab either. The macro would need to be modified (or you could just rewrite it completely in matlab).

[Varun Lakshmanan]

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.

Answer 1

[Guillaume]

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

[Varun Lakshmanan]

Thanks for your answer. When I try running this, I'm getting the error

Matrix dimensions must agree.

on the line

targetbook = excel.Workbooks.Open(targetfile); 

What could be causing this? Is it possible to open two different workbooks with the same excel activexserver?

[Guillaume]

Is it possible to open two different workbooks with the same excel activexserver

Yes, of course. That line just tells excel to open another file. Excel places no restrictions on how many files can be open.

Are you sure that this is the line causing the error? I really can't see how that line could throw this kind of error.

[Varun Lakshmanan]

Yes, this line is causing the error. This is the complete error message.

Matrix dimensions must agree.

Error in visualize_3d_plot (line 75)
targetbook = excel.Workbooks.Open('C:\Users\Seth\Desktop\' + origInputName + '\Z1\all.csv');

Even if I move this line above the line that opens the macrobook, the error is still present. One thing to note is that my targetbook is a .csv file; will this still work or must it be a .xlsx file?

[Guillaume]

The bit that is causing Matrix dimension must agree is your own addition, nothing to do with excel or actxserver. It's

'C:\Users\Seth\Desktop\' + origInputName + '\Z1\all.csv'

which tries to add (as in numeric addition) 3 char vectors. Concatenation of char vectors is:

['C:\Users\Seth\Desktop\', origInputName, '\Z1\all.csv']

+ is only defined as concatenation for string arrays:

"C:\Users\Seth\Desktop\" + origInputName + "\Z1\all.csv"  %"" is string array, '' is char vector. Not the same class at all

But since you're manipulating paths, fullfile is even better:

fullfile('C:\Users\Seth\Desktop', origInputName, 'Z1\all.csv');

fullfile takes care of adding the correct folder delimiter for your OS (\ on windows, / on linux and Mac).

edit: To understand what + does with char arrays see:

>> 'abc' + '123'
ans =
   146   148   150
>> double('abc') + double('123')
ans =
   146   148   150

+ simply adds the numerical values of the characters of the char vector. Of course, the two char vectors must be the same length otherwise you get a Matrix dimension must agree error.

[Varun Lakshmanan]

Thank you so much for your help! This was the problem.