Link to home
Create AccountLog in
Avatar of AlexPonnath
AlexPonnathFlag for United States of America

asked on

vb.net deleting excel sheet in workbook

I am using some very basic code to test my app where i open a Excel Workbook, count the nbr of sheets in beginning
then delete 1 sheet and then count sheets again and save. For some reason i can not actually delete sheet, the sheet
count is same before and after delete and after save the sheet is still in work book. Hope someone can see what i am doing wrong here

Dim oExcel As Object = CreateObject("Excel.Application")
Dim oBook As Object = oExcel.Workbooks.Open("C:\Users\Documents\acme.xls")

Dim oSheet As Object = oBook.Worksheets(2)

MsgBox(oBook.Worksheets.count)

oSheet.Delete()

MsgBox(oBook.Worksheets.count)

oBook.SaveAs("C:\Users\Documents\ACME-New.xls",)
oExcel.Quit()

Open in new window

Avatar of Shums Faruk
Shums Faruk
Flag of India image

Hi,

Replace oSheet.Delete() to oSheet.Delete
Dim oExcel As Object = CreateObject("Excel.Application")
Dim oBook As Object = oExcel.Workbooks.Open("C:\Users\Documents\acme.xls")

Dim oSheet As Object = oBook.Worksheets(2)

MsgBox(oBook.Worksheets.count)

oSheet.Delete

MsgBox(oBook.Worksheets.count)

oBook.SaveAs("C:\Users\Documents\ACME-New.xls")
oExcel.Quit()

Open in new window

Try below:

Dim oExcel As Object = CreateObject("Excel.Application")
Dim oBook As Object = oExcel.Workbooks.Open("C:\Users\Documents\acme.xls")

Dim oSheet As Excel.Sheets = oBook.Worksheets

MsgBox(oBook.Worksheets.count)

oSheet(2).Delete()

MsgBox(oBook.Worksheets.count)

oBook.SaveAs("C:\Users\Documents\ACME-New.xls")
oExcel.Quit()

Open in new window

Avatar of AlexPonnath

ASKER

Does not make a difference still doesn't delete it
Tried also your new code and still same sheet count after delete and the saved workbook still has all sheets in it. Also tried deleting
differences index and it makes no difference
Try below then:
Dim oExcel As Excel.Application = New Microsoft.Office.Interop.Excel.Application()
Dim oBook As Excel.Workbook = oExcel.Workbooks.Open("C:\Users\Documents\acme.xls")

Dim oSheet As Excel.Sheets = oBook.Worksheets

MsgBox(oBook.Worksheets.count)

oSheet(2).Delete()

MsgBox(oBook.Worksheets.count)

oBook.SaveAs("C:\Users\Documents\ACME-New.xls")
oExcel.Quit()

Open in new window

Still doesn't do it...
I enclosed the Excel shhet i am trying to do this with maybe there is something wrong with the doc
LSR.xls
Please post your full code
This is the complete code required to do what I am trying to do.
ASKER CERTIFIED SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Great job, that one drove me nuts
You're Welcome Alex! Glad I was able to help.