Add a new Excel sheet from Matlab

Question

Derek Jing 2011-3-6

2
链接

此问题的直接链接

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

评论： pravesh verma 2016-7-7

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

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

pravesh verma 2016-7-7

sir plz provide all matlab sheet

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Jiro Doke 2011-3-6

6
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2603-add-a-new-excel-sheet-from-matlab#answer_4096

在 MATLAB Online 中打开

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();

4 个评论
显示 2更早的评论隐藏 2更早的评论

Image Analyst 2013-3-20

编辑：Image Analyst 2013-3-20

在 MATLAB Online 中打开

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