-
August 4th, 2008, 01:57 PM
#1
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
-
August 4th, 2008, 02:01 PM
#2
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.
-
August 4th, 2008, 02:06 PM
#3
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.
-
September 9th, 2008, 01:53 AM
#4
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..."
-
October 28th, 2014, 02:40 AM
#5
Re: Deleting Excel Sheet
Dim worksheets As Excel.Sheets = xlWorkBook.Worksheets
worksheets(1).Delete()
Full Source...Delete Worksheet
Kerry
Originally Posted by 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?
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
-
October 29th, 2014, 09:58 PM
#6
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
-
November 4th, 2014, 01:06 PM
#7
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|