Thread Subject: deleting an excel worksheet

Is there a way to delete an excel worksheet using MATLab?

On Mon, 26 Jan 2009 14:42:02 -0500, Travis <sinusoid2@hotmail.com> wrote:

> Is there a way to delete an excel worksheet using MATLab?

yes, you should be able to use the COM interface to Excel to do this.

To point you in the direction:

filename='<full path to the file>';
sname='name of sheet you want to delete';

e = actxserver ('Excel.Application')
Workbooks = e.Workbooks;
% Make Excel visible
%e.Visible=1;
% Open Excel file
Workbook=Workbooks.Open(filename);

%get a handle to the sheet
hsheet=e.Sheets.Item(sname);
hsheet.Delete

%This unfortunately pops up a (typical MS)
%dialog from Excel asking 'are you sure..'.
%You might be able to dig around in the
%Excel help for someway to override this automatically.

%<save and exit>

"Ashish Uthama" <first.last@mathworks.com> wrote in message <op.uodta3asa5ziv5@uthamaa.dhcp.mathworks.com>...
> On Mon, 26 Jan 2009 14:42:02 -0500, Travis <sinusoid2@hotmail.com> wrote:
>
> > Is there a way to delete an excel worksheet using MATLab?
>
> yes, you should be able to use the COM interface to Excel to do this.
>
> To point you in the direction:
>
> filename='<full path to the file>';
> sname='name of sheet you want to delete';
>
> e = actxserver ('Excel.Application')
> Workbooks = e.Workbooks;
> % Make Excel visible
> %e.Visible=1;
> % Open Excel file
> Workbook=Workbooks.Open(filename);
>
> %get a handle to the sheet
> hsheet=e.Sheets.Item(sname);
> hsheet.Delete
>
> %This unfortunately pops up a (typical MS)
> %dialog from Excel asking 'are you sure..'.
> %You might be able to dig around in the
> %Excel help for someway to override this automatically.
>
> %<save and exit>


Thanks, I will look to see if there is a way to disable that.

> > %This unfortunately pops up a (typical MS)
> > %dialog from Excel asking 'are you sure..'.
> > %You might be able to dig around in the
> > %Excel help for someway to override this automatically.
> >
> > %<save and exit>
>
>
> Thanks, I will look to see if there is a way to disable that.

Travis,
I'd like to delete a worksheet from an Excel file too. Did you ever figure out a way to disable the dialog box?
Thanks.
Greg

"Greg " <kittythebulldog-1@yahoo.com> wrote in message <gq8r51$gjv$1@fred.mathworks.com>...
> > > %This unfortunately pops up a (typical MS)
> > > %dialog from Excel asking 'are you sure..'.
> > > %You might be able to dig around in the
> > > %Excel help for someway to override this automatically.
> > >
> > > %<save and exit>
> >
> >
> > Thanks, I will look to see if there is a way to disable that.
>
> Travis,
> I'd like to delete a worksheet from an Excel file too. Did you ever figure out a way to disable the dialog box?
> Thanks.
> Greg

I never did, sorry

"Greg " <kittythebulldog-1@yahoo.com>
You can try adapting this code. It doesn't pop up a dialog box:

% DeleteEmptyExcelSheets: deletes all empty sheets in an xls-file
%
%==========================================================================
% Version : 1.0
% Author : hnagel
% Date : 27/04/2007
% Tested : 02/05/2007 (DR)
%==========================================================================
%
% This function looped 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.
%
% References: Torsten Jacobsen, "delete standard excel sheet"
%---------------------------------------------------------------------
%
% Input:
%
% fileName: name of xls file
%
%---------------------------------------------------------------------
%
% Output:
%
% none
%
%---------------------------------------------------------------------
%
% See also XLSWRITE
%---------------------------------------------------------------------
% Changes
%---------------------------------------------------------------------
%
% Name :
% Date :
% Description:
% Indicated :
function DeleteEmptyExcelSheets(fileName)
% Check whether the file exists
if ~exist(fileName,'file')
error([fileName ' does not exist !']);
else
% Check whether it is an Excel file
typ = xlsfinfo(fileName);
if ~strcmp(typ,'Microsoft Excel Spreadsheet')
error([fileName ' not an Excel sheet !']);
end
end

% If fileName does not contain a "\" the name of the current path is added
% to fileName. The reason for this is that the full path is required for
% the command "excelObj.workbooks.Open(fileName)" to work properly.
if isempty(strfind(fileName,'\'))
fileName = [cd '\' fileName];
end

excelObj = actxserver('Excel.Application');
excelWorkbook = excelObj.workbooks.Open(fileName);
worksheets = excelObj.sheets;
sheetIdx = 1;
sheetIdx2 = 1;
numSheets = worksheets.Count;
% Prevent beeps from sounding if we try to delete a non-empty worksheet.
excelObj.EnableSound = false;

% Loop over all sheets
while sheetIdx2 <= numSheets
% Saves the current number of sheets in the workbook
temp = 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(sheetIdx>1,numSheets-sheetIdx2>0)
% worksheets.Item(sheetIdx).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(sheetIdx).UsedRange.Count == 1
worksheets.Item(sheetIdx).Delete;
end
end
% Check whether the number of sheets has changed. If this is not the
% case the counter "sheetIdx" is increased by one.
if temp == worksheets.count;
sheetIdx = sheetIdx + 1;
end
sheetIdx2 = sheetIdx2 + 1; % prevent endless loop...
end
excelObj.EnableSound = true;
excelWorkbook.Save;
excelWorkbook.Close(false);
excelObj.Quit;
delete(excelObj);
return;

"Greg " <kittythebulldog-1@yahoo.com> wrote in message <gq8r51$gjv$1@fred.mathworks.com>...
> > > %This unfortunately pops up a (typical MS)
> > > %dialog from Excel asking 'are you sure..'.
> > > %You might be able to dig around in the
> > > %Excel help for someway to override this automatically.
> > >
> > > %<save and exit>
> >
> >
> > Thanks, I will look to see if there is a way to disable that.
>
> Travis,
> I'd like to delete a worksheet from an Excel file too. Did you ever figure out a way to disable the dialog box?
> Thanks.
> Greg

run this before your deletion:
e.Application.DisplayAlerts = false; % or =0

Yair Altman
http://undocumented-matlab.com
http;//ymasoftware.com
 

"Image Analyst" <imageanalyst@mailinator.com> wrote in message <gq8st9$k3n$1@fred.mathworks.com>...
> "Greg " <kittythebulldog-1@yahoo.com>
> You can try adapting this code. It doesn't pop up a dialog box:

Wow, works great! That's exactly what I wanted to do - delete all the empty worksheets. Thanks for posting the code!

Greg Reese

"Image Analyst" <imageanalyst@mailinator.com> wrote in message <gq8st9$k3n$1@fred.mathworks.com>...
> "Greg " <kittythebulldog-1@yahoo.com>
> You can try adapting this code. It doesn't pop up a dialog box:

Wow, works great! That's exactly what I wanted to do - delete all the empty worksheets. Thanks for posting the code!

Greg Reese