Add a new Excel sheet from Matlab

Hi,

I am using ActiveX server to control Excel spreadsheet. Now I am using Worksheet.Sheets.Add to add a new sheet, but it add before the currently active sheet. I do not know how to apply the 'After' option there.

In all, I want to add a new sheet to the end (the last sheet), and set a new for this new sheet. Anyone can tell me how to apply 'After' and 'name' options to the Matlab command?

Thanks in advance for your help.

Regards, Derek

[pravesh verma]

sir plz provide all matlab sheet

Answer 1

[Jiro Doke]

According to the VBA documentation on MSDN, to add after the active sheet, specify the active sheet as the second argument to the Add method:

Worksheet.Sheets.Add([], ActiveSheet)

Here's an example that adds a new sheet after the last worksheet (example assumes an Excel file called "Book1.xlsx" in the current folder):

% Connect to Excel
Excel = actxserver('excel.application');
% Get Workbook object
WB = Excel.Workbooks.Open(fullfile(pwd, 'Book1.xlsx'), 0, false);
% Get Worksheets object
WS = WB.Worksheets;
% Add after the last sheet
WS.Add([], WS.Item(WS.Count));
% Save
WB.Save();
% Quit Excel
Excel.Quit();

[Derek Jing]

It works like a charm, thanks a lot.

[Image Analyst]

Jiro: Note that you can have a problem adding a worksheet if multiple worksheets are selected. In that case you'll see [Group] in the title bar of Excel. In that case you need to Select only one worksheet first ( Activating the same sheet method doesn't do it for some reason).

destinationWorksheets.Item(1).Select

I had gotten a workbook from somebody who, for some reason, had saved it with multiple sheets selected, and I was trying to add new worksheets to it, and then transfer data from another workbook. That was different than he had done it before (when he had only one sheet selected), and it caused my code to break when it hit the .Add method. It's well explained here:

http://answers.microsoft.com/en-us/office/forum/office_2010-excel/unable-to-add-worksheet-tabs-in-microsoft-excel/7dd27a3a-f202-4c4c-b45d-0c695b194c8d

Here's my final code:

% Switch back to the final stats report workbook.
destinationWorkbook.Activate;
% You can have a problem with the add if two sheets are selected (and the workbook can be saved with sheets selected).
% The way to get around that is to "Select" sheet 1.  Activating sheet 1 does not work.  
% You must use the .Select method, not the .Activate method of that sheet (Item(1)).
% Ref: http://answers.microsoft.com/en-us/office/forum/office_2010-excel/unable-to-add-worksheet-tabs-in-microsoft-excel/7dd27a3a-f202-4c4c-b45d-0c695b194c8d
destinationWorksheets.Item(1).Select
% Add a new worksheet to the destination workbook.
if insertSheets
  % Insert a new sheet before existing destination sheets.
  % Activate the correct sheet.  We will insert BEFORE the active sheet.
        destinationWorksheets.Item(sheetToInsert2).Activate;
  % Insert a sheet before the active sheet if no arguments are passed to the Add method.
  % Reference: http://msdn.microsoft.com/en-us/library/office/ff838966%28v=office.14%29.aspx
  destinationWorksheets.Add;
else
  % Append a new sheet AFTER the last sheet.
  % Reference: Jiro Doke's answer in Answers Forum:
  % http://www.mathworks.com/matlabcentral/answers/2603-add-a-new-excel-sheet-from-matlab
  destinationWorksheets.Add([], destinationWorksheets.Item(destinationWorksheets.Count));
end

[Ilyas]

Wouldn't it be much easier if you provided a simple code? I don't understand what you are trying to do.

[Image Analyst]

Ilays, it IS simple. There are only 5 lines of code there. The rest is just comments to explain it.