Delete sheet number 1 in excel

I want to delete sheet number 1 in excel. Here is the code I wrote:

   newExcel = actxserver('excel.application');
   excelWB = newExcel.Workbooks.Open(filepath,0,false);
   newExcel.Sheets.Item(1).Delete;
   excelWB.Save();
   excelWB.Close();
   newExcel.Quit();
   delete(newExcel);

Can anyone help me to solve this issue.

[Guillaume]

Possibly, the sheet is protected. It's difficult to answer your question, since you're not actually telling us what the problem is, just that there is a problem.

[pr]

the code is executing without error and it is not deleting the sheet number 1. But i am able to delete all the empty sheets.

Answer 1

[Guillaume]

The problem is that by default Excel asks you to confirm that you really want to delete the worksheet. As excel is not visible, you don't see the confirmation dialog (a bit daft, I know!). You can verify this for yourself, by issuing:

newExcel.Visible = true;

before your delete command.

To fix your issue, simply tells excel to not display alerts with:

newExcel.DisplayAlerts = false;

before trying to delete the sheet.

Finally, instead of

newExcel.Sheets.Item(1).Delete;

I would use

excelWB.Sheets.Item(1).Delete;

The former delete a sheet in the active workbook, the later always in excelWB. The active workbook is most likely excelWB as you've just opened it, but it's always better to be explicit.

[pr]

It's working perfectly. Thank you for your solutions.

Answer 2

[Image Analyst]

What's wrong with that? Just off the top of my head, not testing, it looks like it should work.

I have a collection of Excel utilities in a class. One method is called DeleteEmptyExcelSheets(), and it uses code that look similar. Small snippet below:

worksheets = excelObject.sheets;
worksheets.Item(sheetIndex).Delete;

[pr]

I tried but unable to delete. Do I need to activate that sheet before deleting?

[Image Analyst]

I don't think so. I don't. Attach your workbook and tell us what sheet number you want to delete and I'll tell you if I can do it.

[pr]

I have attached my workbook please look at it.

[Image Analyst]

I tihnk Guillaume hit the nail on the head. That's one of the lines in my code. I include the full method below and you'll see the DisplayAlerts line. So go ahead and mark his answer as Accepted since I believe he solved it. If that still doesn't work, let us know.

    % --------------------------------------------------------------------
    % DeleteEmptyExcelSheets: deletes all empty sheets in the active workbook.
    % This function loops through all sheets and deletes those sheets that are empty.
    % Can be used to clean a newly created xls-file after all results have been saved in it.
    % Sample call
    % Excel_utils.DeleteEmptyExcelSheets(Excel);
    function DeleteEmptyExcelSheets(excelObject)
      try
        %   excelObject = actxserver('Excel.Application');
        %   excelWorkbook = excelObject.workbooks.Open(fileName);
        
        % Run Yair's program http://www.mathworks.com/matlabcentral/fileexchange/17935-uiinspect-display-methods-properties-callbacks-of-an-object
        % to see what methods and properties the Excel object has.
        %   uiinspect(excelObject);
        
        worksheets = excelObject.sheets;
        sheetIndex = 1;
        sheetIndex2 = 1;
        initialNumberOfSheets = worksheets.Count;
        % Prevent beeps from sounding if we try to delete a non-empty worksheet.
        excelObject.EnableSound = false;
        % Tell it to not ask you for confirmation to delete the sheet(s).
        excelObject.DisplayAlerts = false;
        
        % Loop over all sheets
        while sheetIndex2 <= initialNumberOfSheets
          % Saves the current number of sheets in the workbook.
          preDeleteSheetCount = worksheets.count;
          % Check whether the current worksheet is the last one. As there always
          % need to be at least one worksheet in an xls-file the last sheet must
          % not be deleted.
          if or(sheetIndex>1,initialNumberOfSheets-sheetIndex2>0)
            % worksheets.Item(sheetIndex).UsedRange.Count is the number of used cells.
            % This will be 1 for an empty sheet.  It may also be one for certain other
            % cases but in those cases, it will beep and not actually delete the sheet.
            if worksheets.Item(sheetIndex).UsedRange.Count == 1
              worksheets.Item(sheetIndex).Delete;
            end
          end
          % Check whether the number of sheets has changed. If this is not the
          % case the counter "sheetIndex" is increased by one.
          postDeleteSheetCount = worksheets.count;
          if preDeleteSheetCount == postDeleteSheetCount;
            % If this sheet was not empty, and wasn't deleted, move on to the next sheet.
            sheetIndex = sheetIndex + 1;
          else
            % sheetIndex stays the same.  It's not incremented because the current sheet got deleted,
            % and all the other sheets shift down in their sheet number.  So now sheetIndex will
            % point to the same number which is the next sheet in line for checking.
          end
          sheetIndex2 = sheetIndex2 + 1; % prevent endless loop...
        end
        excelObject.EnableSound = true;
      catch ME
        errorMessage = sprintf('Error in function DeleteEmptyExcelSheets.\n\nError Message:\n%s', ME.message);
        fprintf('%s\n', errorMessage);
        WarnUser(errorMessage);
      end
      return; % from DeleteEmptyExcelSheets
    end % of DeleteEmptyExcelSheets

[pr]

Thank you Image Analyst.