MATLAB Answers

2

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

I would like to delete the default sheets Sheet1, Sheet2 and Sheet3 in Excel, when I use XLSWRITE.

2 Answers

Answer by MathWorks Support Team on 12 Aug 2009
 Accepted Answer

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

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.


Answer by Matthias on 7 Jun 2016
Edited by 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.