Deleting Excel Sheet
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 7 of 7

Thread: Deleting Excel Sheet

  1. #1
    Join Date
    Jun 2008
    Posts
    29

    Deleting Excel Sheet

    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?

    Code:
            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

  2. #2
    Join Date
    Aug 2005
    Location
    Imperial College London, England
    Posts
    490

    Re: Deleting Excel Sheet

    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...
    Help from me is always guaranteed!*
    VB.NET code is made up on the spot with VS2008 Professional with .NET 3.5. Everything else is just made up on the spot.
    Please Remember to rate posts, use code tags, send me money and all the other things listed in the "Before you post" posts.

    *Guarantee may not be honoured.

  3. #3
    Join Date
    Jun 2008
    Posts
    29

    Re: Deleting Excel Sheet

    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.

  4. #4
    Join Date
    Apr 2008
    Posts
    24

    Smile Re: Deleting Excel Sheet

    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
    Code:
     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.

    Code:
    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.
    Burningslash12 - Visual Basic 2005
    "We may rise and fall, but in the end, we'll meet our fate together..."

  5. #5
    Join Date
    Oct 2014
    Posts
    1

    Re: Deleting Excel Sheet

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

    Full Source...Delete Worksheet

    Kerry


    Quote Originally Posted by adambom View Post
    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?

    Code:
            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

  6. #6
    Join Date
    Apr 2014
    Posts
    14

    Re: Deleting Excel Sheet

    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

  7. #7
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,100

    Re: Deleting Excel Sheet

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center