How do I delete worksheets in my Excel file using MATLAB ?

When I use XLSWRITE a file with three worksheets is created by default. I save data to only one worksheet and do not intend to use the other two blank worksheets. I want to delete the unused worksheets.

Answer 1

[MathWorks Support Team]

The following code runs through an Excel file and deletes sheets based on user input. Note that the code will produce an error if you delete all the sheets in an Excel file because Excel requires valid files to have at least one worksheet.

The code requires the user to have Microsoft Excel and uses ActiveX.

% This example operates on an Excel file called test.xls in the

% current directory. The test.xls file has 3 worksheets by default. This file can be 

% created by creating a new Excel file via Microsoft Excel and saving it as test.xls.

% Get information returned by XLSINFO on the workbook

XL_file = [pwd '\test.xls'];

[type, sheet_names] = xlsfinfo(XL_file);

% First open Excel as a COM Automation server

Excel = actxserver('Excel.Application'); 

% Make the application invisible

set(Excel, 'Visible', 0);

% Make excel not display alerts

set(Excel,'DisplayAlerts',0);

% Get a handle to Excel's Workbooks

Workbooks = Excel.Workbooks; 

% Open an Excel Workbook and activate it

Workbook=Workbooks.Open(XL_file);

% Get the sheets in the active Workbook

Sheets = Excel.ActiveWorkBook.Sheets;

index_adjust = 0;

% Cycle through the sheets and delete them based on user input

for i = 1:max(size(sheet_names))

      inp_prompt = sprintf('Do you want to delete the Worksheet called %s [y/n]?',sheet_names{i});

      user_inp = lower(input(inp_prompt,'s'));

      switch user_inp

          case{'y'}

              current_sheet = get(Sheets, 'Item', (i-index_adjust));

              invoke(current_sheet, 'Delete')

              out_string = sprintf('Worksheet called %s deleted',sheet_names{i});

              index_adjust = index_adjust +1;

          otherwise

              out_string = sprintf('Worksheet called %s ***NOT*** deleted',sheet_names{i});

      end

      disp(out_string);

      disp(' ');

end

% Now save the workbook

Workbook.Save;

% Close the workbook

Workbooks.Close;

% Quit Excel

invoke(Excel, 'Quit');

% Delete the handle to the ActiveX Object

delete(Excel);

Note that one must exercise caution when using ActiveX to modify Excel as this could lead to potentially serious errors if used incorrectly. An error in ActiveX instructions can not only crash Excel but also adversely affect MATLAB. Therefore, we recommend that critical ActiveX code executed through MATLAB should be used by those who understand ActiveX to the degree that the programmer can undo any particular ActiveX instructions.

Answer 2

[Pruthvi G]

Download Link :: https://in.mathworks.com/matlabcentral/fileexchange/71329-delete-excel-workbook-sheets-sheet-1-delete-using-matlab

%%**********************************************************************************************************
%   Name          : Delete_sheets_Excel
%   Author        : Pruthvi Raj G  ::  (9677066394 :: www.prudhvy.com )
%   Version       : Version 1.0 - 2011b Compactible
%   Description   : Deleting Excel Sheets required after writing data to Excel. 
%   Input         : File_Name with path included , Sheet_name / Sheet_names.
%   Date          : 22-April-2019
%  
%   Examples      : Delete_sheets_Excel('D:\Pruthvi\Test_file.xls',{'Sheet1','Sheet2'})  %To delete 2 sheets
%                   Delete_sheets_Excel('D:\Pruthvi\Test_file.xls','Sheet1')
%                   Delete_sheets_Excel('D:\Pruthvi\Test_file.xls') % Takes 'Sheet1' as Default
%************************************************************************************************************

Use the Below Lines of Code ::

Delete_sheets_Excel('D:\Pruthvi\Test_file.xls',{'Sheet1','Sheet2'})