How can you make a particular Excel workbook active when you have more than one open in MATLAB 7.14 (R2012a)?

67 views (last 30 days)
I am using ACTXSERVER to communicate with Excel. The Excel that I will write to contains a macro that creates and opens up another workbook. Then, I cannot write on the original workbook because the newly created Excel file is the one active.

Accepted Answer

MathWorks Support Team
MathWorks Support Team on 12 Sep 2013
In order to make a particular Excel workbook active from MATLAB, you will need to use the method ACTIVATE.
The following code is an example of how to use it. In this example MATLAB writes to an empty Excel file 'Book1.xlsm' which contains a macro named 'AddNew' that creates a new Excel file called 'test.xls'.
If the code is run, it is possible to see that first, the Excel file 'Book1.xlsm' is open and then, from MATLAB, the macro is run. This macro creates and opens the new Excel file 'test.xls', which becomes the active workbook. Then, to make 'Book1.xlsm' the active workbook, the command 'Workbook.Activate' is used. Finally, the code makes the second sheet active and writes on it, saves the workbook and closes Excel.
%%Specify file name
file = 'path_to_file\Book1.xlsm'; % This must be full path name
%%Open Excel Automation server
Excel = actxserver('Excel.Application');
Workbooks = Excel.Workbooks;
%%Make Excel visible
Excel.Visible = 1;
%%Open Excel file
Workbook = Workbooks.Open(file);
%%Run Macro
Excel.Run('AddNew');
%This macro will create a new Excel file called test.xls
%%Specify sheet number, data, and range to write to
sheetnum=1;
data=rand(4); % use a cell array if you want both numeric and text data
range = 'F10:I13';
%%Make first workbook open the active one
Workbook.Activate
%%Make the second sheet from the active workbook active
Sheets = Excel.ActiveWorkBook.Sheets;
sheet2 = get(Sheets, 'Item', 2);
invoke(sheet2, 'Activate');
Activesheet = Excel.Activesheet;
%%Put MATLAB data into the active Excel
ActivesheetRange = get(Activesheet,'Range',range);
set(ActivesheetRange, 'Value', data);
%%Save file
invoke(Workbook,'Save')
%%Close Excel and clean up
invoke(Excel,'Quit');
delete(Excel);
clear Excel;

More Answers (0)

Tags

No tags entered yet.

Products


Release

R2012a

Community Treasure Hunt

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

Start Hunting!