MATLAB Answers

How can I delete the default sheets Sheet1, Sheet2 and Sheet3 in Excel, when I use XLSWRITE?

432 views (last 30 days)
I would like to delete the default sheets Sheet1, Sheet2 and Sheet3 in Excel, when I use XLSWRITE.

Accepted Answer

MathWorks Support Team
MathWorks Support Team on 12 Aug 2009
You can delete the sheets that get created automatically by Excel when you use XLSWRITE with a new file name by using ActiveX functionality (Windows only).
Example:
excelFileName = 'Test.xls';
excelFilePath = pwd; % Current working directory.
sheetName = 'Sheet'; % EN: Sheet, DE: Tabelle, etc. (Lang. dependent)
% Open Excel file.
objExcel = actxserver('Excel.Application');
objExcel.Workbooks.Open(fullfile(excelFilePath, excelFileName)); % Full path is necessary!
% Delete sheets.
try
% Throws an error if the sheets do not exist.
objExcel.ActiveWorkbook.Worksheets.Item([sheetName '1']).Delete;
objExcel.ActiveWorkbook.Worksheets.Item([sheetName '2']).Delete;
objExcel.ActiveWorkbook.Worksheets.Item([sheetName '3']).Delete;
catch
; % Do nothing.
end
% Save, close and clean up.
objExcel.ActiveWorkbook.Save;
objExcel.ActiveWorkbook.Close;
objExcel.Quit;
objExcel.delete;
In this way, you will be able to delete the default sheets from the workbook. But as the workbook should at least contain one worksheet, you will not be able to delete the last remaining sheet. You can work around this by creating your own sheet first and then deleting the default sheets.

  1 Comment

Pappu Murthy
Pappu Murthy on 6 May 2016
It seemed to have worked but not very cleanly. I wanted a simpler solution but may be not possible. Not sure at all. Here are the messages I get.
Warning: Added specified worksheet. > In xlswrite>activate_sheet (line 292) In xlswrite/ExecuteWrite (line 258) In xlswrite (line 213) In ProofSimGUI>Save_Results_Callback (line 387) In gui_mainfcn (line 95) In ProofSimGUI (line 42) In matlab.graphics.internal.figfile.FigFile/read>@(hObject,eventdata)ProofSimGUI('Save_Results_Callback',hObject,eventdata,guidata(hObject)) Warning: Added specified worksheet. > In xlswrite>activate_sheet (line 292) In xlswrite/ExecuteWrite (line 258) In xlswrite (line 213) In ProofSimGUI>Save_Results_Callback (line 388) In gui_mainfcn (line 95) In ProofSimGUI (line 42) In matlab.graphics.internal.figfile.FigFile/read>@(hObject,eventdata)ProofSimGUI('Save_Results_Callback',hObject,eventdata,guidata(hObject)) >>

Sign in to comment.

More Answers (2)

Matthias
Matthias on 7 Jun 2016
Edited: Matthias on 7 Jun 2016
EDIT: this was too quick, although working on a first glance this is not a solution - an excel process keeps being opened :-( Nontheless this should not be too hard to cleanly implement (at least not by the mathworks ^^)
The option I thought would work is a modification of xlswrite.m. Replacing the last block in the function by this
% Export data to selected region.
set(Excel.selection,'Value',A);
% MRZ: delte default sheets in freshly created .xls
if bCreated
[~, sheets] = xlsfinfo(file);
sheetNames2remove = setdiff(sheets,sheet);
for i = 1:numel(sheetNames2remove)
ExcelWorkbook.Worksheets.Item(sheetNames2remove{i}).Delete;
end
end
ExcelWorkbook.Save
did the trick for me (almost). Should work in basic excel mode and is language independent :-) Btw.: I'm still using R2012a - no idea if this still works in later (or earlier) releases...

  0 Comments

Sign in to comment.


Pruthvi G
Pruthvi G on 13 Apr 2020
%%**********************************************************************************************************
% 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'})

  0 Comments

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!