How can I copy 1 template Excel sheet into multiple Excel sheets in the same workbook? Using actXserver

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.

Answer 1

[Mario Malic]

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.

[Steven Manz]

That worked! Thank you! I wish there were better documentation and examples on the ActXServer.

[Mario Malic]

Well, it wasn't super easy, as I had to enable Excel visibility and check whether command is working as intended, and of course it wasn't, at first. This type of programming is object oriented programming, there is a beginner class/webinar in MATLAB on YouTube that can help. The reason your code didn't work is probably because Activesheet object is within ActiveWorkbook object, but you indexed it directly from hExcel. If you enter methods(hExcel) you can see all available methods and once you find one of your interest, use the Excel documentation to find out how to use it.

[Steven Manz]

Got it. Thank you for your help.