Yugal Jindle Yugal Jindle - 1 year ago 56
Vb.net Question

Unable to delete sheet from excel and it even gives no error using VB.NET?

I have:

Public Class ExcelProcess
Private App As New Excel.Application
Private Books As Excel.Workbooks = App.Workbooks
Private WB As Excel.Workbook

Public Sub deleteSheets()
Dim sheet As Excel.Worksheet = getSheetToDelete()
End Sub

Private Function getSheetToDelete() As Excel.Worksheet
WB = Books.Open("file.xlsx")
Catch ex As Exception
End Try
Return WB.Sheets(1)
End Function

End Class

I ran the above code, and it ran successfully with absolutely no errors or exceptions!

But the sheet didn't get deleted!


I have also tried:


' Now, this gave me an exception:
Unhandled Exception: System.Runtime.InteropServices.COMException: Exception from HRESULT: 0x800A03EC
at Microsoft.Office.Interop.Excel._Worksheet.Select(Object Replace)
at DealerCapReport.ExcelProcess.deleteSheets()
at DealerCapReport.MainModule.Main()

< Another UPDATE >

I tried to check sheet.Delete() boolean for success of failure:

If sheet.Delete() Then ' Error: Expression does not produce a value.
Console.WriteLine("Can-not Delete")
End If

It says Error: Expression does not produce a value. in sheet.Delete().

The strange thing is that the Microsoft API reference says that it would produce a Boolean, but it doesn't as it is a Sub and not a function.

How and what is happening?

Am I doing something wrong?

Please help me fix this!

Answer Source

The following code works for me (EDITED to include some error checking):

Public Class ExcelProcess
    Private xlApp As Excel.Application
    Private xlBook As Excel.Workbook
    Private xlSheet As Excel.Worksheet

    Public Sub New(ByVal file As String)
        xlApp = New Excel.Application
        'xlApp.Visible = True 'for debugging
        xlApp.DisplayAlerts = False 'prevent user dialogs
        xlBook = xlApp.Workbooks.Open(file)
    End Sub

    Public Sub Quit()
        If Not IsNothing(xlBook) Then
        End If
        If Not IsNothing(xlApp) Then xlApp.Quit()
    End Sub

    Public Sub deleteSheet(ByVal Index As Integer)
        If Index > xlBook.Worksheets.Count Then
            Throw New Exception("You cannot delete a worksheet that does not exist")
            Exit Sub
        End If
        If xlBook.Worksheets.Count = 1 Then
            Throw New Exception("You cannnot delete the only worksheet in a workbook")
            Exit Sub
        End If
        xlSheet = CType(xlBook.Worksheets(Index), Excel.Worksheet)
    End Sub

End Class