MATLAB Answers

Delete sheet number 1 in excel

68 views (last 30 days)
pr
pr on 10 Oct 2014
Commented: pr on 10 Oct 2014
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.
  2 Comments
pr
pr on 10 Oct 2014
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.

Sign in to comment.

Accepted Answer

Guillaume
Guillaume on 10 Oct 2014
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.
  1 Comment
pr
pr on 10 Oct 2014
It's working perfectly. Thank you for your solutions.

Sign in to comment.

More Answers (1)

Image Analyst
Image Analyst on 10 Oct 2014
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;
  5 Comments
pr
pr on 10 Oct 2014
Thank you Image Analyst.

Sign in to comment.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by