7

I have an excel workbook with many, many sheets. I want to delete all the sheets except for three of them.

Specifically, i would like to know if there is a way to remove the sheets using sheet name instead of ordinals (sheet number).

I am using excel interop and C# to work with Excel.

Microsoft.Office.Interop.Excel.Application xlApp = null;
Excel.Workbook xlWorkbook = null;
Excel.Sheets xlSheets = null;
Excel.Worksheet xlNewSheet = null;
TheBoyan
  • 6,802
  • 3
  • 45
  • 61
user1144596
  • 2,068
  • 8
  • 36
  • 56

3 Answers3

11
xlApp.DisplayAlerts = false;
for (int i = xlApp.ActiveWorkbook.Worksheets.Count; i > 0 ; i--)
{
    Worksheet wkSheet = (Worksheet)xlApp.ActiveWorkbook.Worksheets[i];
    if (wkSheet.Name == "NameOfSheetToDelete")
    {
        wkSheet.Delete();
    }
}
xlApp.DisplayAlerts = true;
Jeremy Thompson
  • 61,933
  • 36
  • 195
  • 321
  • 2
    This is wrong, I believe. Shouldn't you be going downward in your loop instead, since you're deleting from within the collection of worksheets? `for (int i = WorkSheets.Count; i > 1; i--)` – Ken White May 02 '13 at 01:49
  • 2
    No `C#` expert but `xlApp.DisplayAlerts = false` etc should reside outside the loop. – brettdj May 02 '13 at 02:33
  • 1
    Per @KenWhite's comment, yes, you have to loop backwards on the delete, otherwise you will end up with an `i` that is out of bounds -- if you delete even *one* worksheet, you will never be able to reach `.Worksheets.Count` since by that time, that sheet index no longer exists in the `Sheets` collection. Otherwise you could do a `For each wksheet` loop, and delete based on the sheet's `.Name`. – David Zemens May 02 '13 at 02:49
  • @KenWhite I changed the for-loop to decrement, but do note the code here only deletes one sheet so the direction of the loop doesn't matter. Same goes for the `xlApp.DisplayAlerts = false` its never called more than once so there is no real difference putting it outside the loop - but I did anyway... – Jeremy Thompson May 02 '13 at 03:29
  • @JeremyThompson it is called more than once. `I want to delete **all** the sheets except for three of them` – brettdj May 02 '13 at 05:32
  • @brettdj I saw that but I thought the real question was "specifically" `if there is a way to remove the sheets using sheet name`. Cheers:) – Jeremy Thompson May 02 '13 at 05:39
2

I know this is old but I just use the fallowing

workBook.Sheets["Sheet1"].Delete();

JusTinMan
  • 110
  • 2
1

I know this thread is really old but for future visitors, if you know the names of the sheets you want to delete, a better way is to loop over the names of the sheets like this:

Workbook book = excelApp.Workbooks.Open("filePathHere");
string[] sheetsToDelete = {"s1", "s2"};
excelApp.DisplayAlerts = false; 
foreach(string sheetToDelete in sheetsToDelete )
{
    book.Worksheets[sheetToDelete].Delete();
}
excelApp.DisplayAlerts = true;

It's always good practice to avoid deleting items in a collection while iterating through it.

Joseph Nuttall
  • 21
  • 4