How to run macro in a different workbook
12 views (last 30 days)
Show older comments
Varun Lakshmanan
on 15 Jul 2019
Commented: Varun Lakshmanan
on 16 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;
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
Guillaume
on 15 Jul 2019
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).
Accepted Answer
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
Guillaume
on 16 Jul 2019
Edited: Guillaume
on 16 Jul 2019
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.
More Answers (0)
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!