If you dynamically add worksheets, you may need to delete them also. How to do that ? Simple. You can write a simple function as :
public static void DeleteWorksheets(IExcel.Sheets worksheets, params string[] excludedNames)
{
for ( int i = worksheets.Count; i > 0; i-- )
{
IExcel.Worksheet sheet = worksheets[i] as IExcel.Worksheet;
if ( !excludedNames.Contains(sheet.Name) )
sheet.Delete();
}
}
How to use it ? Well if you are in a sheet code and that you want to delete all the sheets except the current one, you can do :
ExcelUtils.DeleteWorksheets(Globals.ThisWorkbook.Worksheets, this.Name);
Almost good. You will simply notice that each time Excel wants to delete a sheet, you will get a popup asking to confirm the deletion. How to bypass it ? Let's modify our function as follows :
public static void DeleteWorksheets(IExcel.Sheets worksheets, params string[] excludedNames)
{
bool displayAlerts = worksheets.Application.DisplayAlerts;
try
{
worksheets.Application.DisplayAlerts = false;
for ( int i = worksheets.Count; i > 0; i-- )
{
IExcel.Worksheet sheet = worksheets[i] as IExcel.Worksheet;
if ( !excludedNames.Contains(sheet.Name) )
sheet.Delete();
}
}
finally
{
worksheets.Application.DisplayAlerts = displayAlerts;
}
}
And here we are !