MATLAB: How to copy 1 template Excel sheet into multiple Excel sheets in the same workbook? Using actXserver

activesheetactiveworkbookactxservercopydynamicexcelfor loophexcelinvokesheetworkbookworkbooks

Ok so the goal here is to take an Excel spreadsheet that already has formatting and use it as a template. I want to know if it is possible to add sheets into the same workbook depending on the number of items needed? As an example, say the sheet in which my template is on is named 'item 1'. I have 5 items so I need 5 sheets named 'item 1', 'item 2', etc. I want to also make this dynamic by placing it into a for loop so the number of sheets in the workbook is equal to the number of items. Here is what I have come up with so far:
for i = 1:2 % 2 is the number of items. I am just using 2 for now to save time.
    
            % Create an Excel object.
            hExcel = actxserver('Excel.Application');
            % Open the worksheet.
            Workbooks = hExcel.Workbooks;
            Workbook = Workbooks.Open('C:\Users\smanz\Documents\TLM\Andreas\example.xlsx');
            % Make the first sheet active.
            eSheets = hExcel.ActiveWorkbook.Sheets;
            eSheet1 = eSheets.get('Item',1); % I assume this takes the first sheet but I am not sure.
            eSheet1.Activate
            % Read the data back into MATLAB, where array B is a cell array.
            a = get(hExcel.Activesheet);
            % Copy Sheet1 into the next sheet after it.
            invoke(a,'Copy',[],a)
            
            % To preface the next line of code, The first sheet is just the template, so the next sheets after will hold the data.
            hExcel.ActiveSheet.Name = ['ITEM ' num2str(i)];
            
            % Rename the template to a user specified name.
            newname = '\foo.xlsx';
            % Save the workbook in a file.
            SaveAs(Workbook,strcat(pwd, newname))
            % If the Excel program displays a dialog box about saving the file, select the appropriate response to continue.
            % If you saved the file, then close the workbook.
            Workbook.Saved = 1;
            Close(Workbook)
            % Quit the Excel program and delete the server object.
            Quit(e)
            delete(e)
end
The error code I receive when trying to accomplish this is the following:
Undefined function 'invoke' for input arguments of type 'struct'.
Error in TLM_Andreas (line 353)
            invoke(a,'Copy',[],a)
See the attached file for ann idea of the template. And please keep in mind, I do not simply want to add sheets, but I want to copy the entire template in the original sheet to the next sheets after dependent on how many items I have.

Best Answer

Hello Steven,
% Create an Excel object.
hExcel = actxserver('Excel.Application');
% Open the worksheet.
Workbooks = hExcel.Workbooks;
Workbook = Workbooks.Open('C:\Users\smanz\Documents\TLM\Andreas\example.xlsx');
% hExcel.Visible = 1;
for i = 1:3 % 2 is the number of items. I am just using 2 for now to save time.
    hExcel.ActiveWorkbook.ActiveSheet.Copy([],  hExcel.ActiveWorkbook.ActiveSheet); % *Note 1
    % To preface the next line of code, The first sheet is just the template, so the next sheets after will hold the data.
    hExcel.ActiveWorkbook.ActiveSheet.Name = sprintf("ITEM %d", i);
end
Workbook.SaveAs(string(pwd) + "\foo2.xlsx"); % everyone here would highly advise you to use fullfile 
Workbook.Close
hExcel.Quit
delete(hExcel)
There's no need to re-create actxserver and to delete it within the loop, it's a time saver as well.
Note 1:Copy is the ActiveSheet method, for input arguments we need specify sheet before or after we want to copy it - link: https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.copy
After copying it, copied sheet becomes the active one, so we directly change the property of ActiveSheet.
It can be quite confusing to distinguish between available methods from ActiveWorkbook, ActiveSheet, Sheet.
Related Question