Deleting Excel Sheet

[adambom]

I'm trying to programatically delete sheets from an excel workbook. When I run the following code, it runs through each line without any problem, but the sheets I want to delete are still there when I open the file. Why is this?

        Dim deleteWS As Microsoft.Office.Interop.Excel.Worksheet
        For i = 1 To wb.Sheets.Count
            deleteWS = wb.Sheets(i)
            If deleteWS.Name <> "Summary" Then
                deleteWS.Delete()
            End If
        Next

[javajawa]

Do you save the workbook? Are the select and activate calls necessary? Does Office have it's own error objects which you may have missed?
Perhaps you need to use wb.Sheets(i).Delete() (If it clones, which it shouldn't do) or wb.Sheets.remove(i) if that exists.

Those ar just some suggestions which may be worth trying...

[adambom]

1) Yes, I save the workbook

2) The select and activate calls are not necessary. I had them in there just to see if it would make a difference and I forgot to take them out before I posted.

3) If I go into the file and manually create a sheet, and then use the same code, it works just fine. When I try and delete programatically generated sheets, that's where the trouble starts.

4) wb.Sheets(i).Delete() gives the same effect; wb.Sheets.remove(i) doesn't exist.

[burningslash12]

Hi there. This code works for me. I created and excel file (with 3sheets) and renamed 1 sheet to "Summary". In the code below, i will add 2 more sheets so that it will be 5 sheets during run-time.

The difference in our code is i used the For Each Next syntax and not the For Next.

If you use this

 For i = 1 To wb.Sheets.Count

Once you delete a sheet, the actual Sheet count will also change, but then in the point of view of your For Next Loop, the wb.Sheets.Count is the number of sheets BEFORE you delete the other sheets. So in effect your counter "i" keeps incrementing up to the point where the actual worksheet count is less than "i" producing errors.

Sub DeleteWorksheet()
        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook

        Dim deleteWS As Microsoft.Office.Interop.Excel.Worksheet
        xlApp = New Microsoft.Office.Interop.Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Open("C:\test.xls")

        xlWorkBook.Sheets.Add() 'add new sheet
        xlWorkBook.Sheets.Add() 'add another one

        For Each deleteWS In xlWorkBook.Sheets
            If deleteWS.Name <> "Summary" Then
                deleteWS.Delete()
            End If
        Next

        xlWorkBook.Save()
        xlWorkBook.Close()
        xlApp.Quit()

        xlApp = Nothing
        xlWorkBook = Nothing
    End Sub

Hope this helps.

[kerrymarke]

Dim worksheets As Excel.Sheets = xlWorkBook.Worksheets
worksheets(1).Delete()

Full Source...Delete Worksheet

Kerry

I'm trying to programatically delete sheets from an excel workbook. When I run the following code, it runs through each line without any problem, but the sheets I want to delete are still there when I open the file. Why is this?

        Dim deleteWS As Microsoft.Office.Interop.Excel.Worksheet
        For i = 1 To wb.Sheets.Count
            deleteWS = wb.Sheets(i)
            If deleteWS.Name <> "Summary" Then
                deleteWS.Delete()
            End If
        Next

[Sliver.W]

I am using Spire.Xls, the worksheet can be easily deleted using sheet1.Remove(). In Microsoft.Office.Interop.Excel, just call worksheets(1).Delete() to remove the worksheet.

regards

[HanneSThEGreaT]

Thanks for helping!
Please note though, that this thread is over six years old. Reviving old threads are usually frowned upon around here