Excel - Sheets Rearranging through MATLAB

Hello,

I need to append sheets to existing excel file, but using following command it addes to the first. Say for example if their are three sheets namely "Sheet 1, Sheet 2 Sheet 3' the newly added Sheet 4 is coming in front of first sheet. But I want to append at the last, any possible way to do this. I am using ActiveX Commands.

ExcelWorkbook = Excel.workbooks.Add;
%For New Work Sheet
ExcelWorkbook.Sheets.Add();

    Thanks in advance

Answer 1

[Pavl M.]

Good question. Since as apparent, Excel driver treats the sheets sequence as linked list and so it by default adds it to the linked list head ( before the 1st sheet) as it is more efficient as know from Computer Science that to add new item at tail.

While there are complete workarounds as per your request.

Who is interesting to work with academic rigor?

2 ways to do it with actxserver driver

1.

exfilename = ... your file name
    your_new_sheet_name = ...
    % Connect to Excel
Excel = actxserver('excel.application');
Excel.visible = true;
% Get Workbook object
WB = Excel.Workbooks.Open(fullfile(pwd, exfilename), 0, false);
% Get Worksheets object
WS = WB.Worksheets;
% Add after the last sheet
WS.Add([], WS.Item(WS.Count));
WS.Item(WS.Count).Name = your_new_sheet_name;
% Save
WB.Save();
% Quit Excel
Excel.Quit();

See precisely similar threads found:

https://fr.mathworks.com/matlabcentral/answers/244494-how-to-add-a-new-page-in-an-excel-sheet-and-put-data-in-it

https://fr.mathworks.com/matlabcentral/answers/2603-add-a-new-excel-sheet-from-matlab

2.

e = actxserver('Excel.Application');
Add(e.Workbooks);
e.Visible = 1;
eSheets = e.ActiveWorkbook.Sheets;
nitemtoinsertafter = e.ActiveWorkbook.Count;
nitemtoinsertbefore = e.ActiveWorkbook.Count;
%Insert a sheet after the first item in the collection, eSheet1.
eSheet1 = Item(eSheets,nitemtoinsertafter);
%insert after:
eNewSheetafter = Add(eSheets,[],eSheet1);
%insert before
eNewSheetbefore= Add([],[],eSheet1);
%self.hCurrentSheet = %Microsoft.Office.Interop.Excel.Worksheet(self.hWorkbook.Worksheets.Add());
Quit(e)
delete(e)