AlexPonnath
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
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()
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()
ASKER
Does not make a difference still doesn't delete it
ASKER
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
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()
ASKER
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
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
ASKER
This is the complete code required to do what I am trying to do.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great job, that one drove me nuts
You're Welcome Alex! Glad I was able to help.
Replace oSheet.Delete() to oSheet.Delete
Open in new window