none
Avoid error in VB.NET when I refer to a property of an Excel object that has been deleted? RRS feed

  • Question

  • In my code I try to refer to: 

    CurrentExcelWorksheetEventsInstance.WorksheetObj.Parent.FullName

    but it raises an error. This happens after the Worksheet object in question has been deleted.

    Can I perform some kind of test before the code above that allows me to refer to properties only when they do not perform errors.

    I have tried writing:

    if not isnothing(CurrentExcelWorksheetEventsInstance) then

    CurrentExcelWorksheetEventsInstance.WorksheetObj.Parent.FullName

    end if


    But that doesn't help because CurrentExcelWorksheetEventsInstance is not nothing even after all properties give errors. What is a better test or approach I can use?



    • Edited by JP3O Monday, March 16, 2015 10:26 AM
    Monday, March 16, 2015 10:25 AM

Answers

  • Hi JP3O,

    >> But that doesn't help because CurrentExcelWorksheetEventsInstance is not nothing even after all properties give errors. What is a better test or approach I can use?

    In my option, if you refer to the property of the Worksheet object which has been deleted, the error would be raised as expected. I think you could use the try catch statement to deal with this situation. Some key code like below:

            Dim oxl As Excel.Application
            Dim owb As Excel.Workbook
            Dim osheet As Excel.Worksheet
            Dim owbname As String
            oxl = New Excel.Application
            owb = oxl.Workbooks.Open("D:\Test\VSC#\03\Winform\VBWinform\Test.xlsx")
            osheet = owb.Worksheets("sheet1")
            Try
                osheet = owb.Worksheets.Add()
                owb.Close(False)
                owbname = owb.Name
            Catch comex As System.Runtime.InteropServices.COMException
                If comex.ErrorCode = -214682246 Or comex.Message.Contains("Object has been deleted") Then
                    ' handle deleted object
                Else
                    Throw comex
                End If
            End Try

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, March 20, 2015 5:00 AM
  • Use the ActiveSheet property of the Application class to get an object that represents the active sheet (the sheet on top) in the active workbook or in the specified window or workbook.

    Why do you need to keep the CurrentExcelWorksheetEventsInstance object alive all the time?

    Monday, March 16, 2015 1:32 PM

All replies

  • Use the ActiveSheet property of the Application class to get an object that represents the active sheet (the sheet on top) in the active workbook or in the specified window or workbook.

    Why do you need to keep the CurrentExcelWorksheetEventsInstance object alive all the time?

    Monday, March 16, 2015 1:32 PM
  • Thanks, but that doesn't answer my question. Do you have any idea about what test I can perform to see if the underlying Excel object to the WorksheetObj has been deleted?
    Tuesday, March 17, 2015 6:41 AM
  • Hi JP3O,

    try

    if not isnothing(CurrentExcelWorksheetEventsInstance.WorksheetObj) then

    CurrentExcelWorksheetEventsInstance.WorksheetObj.Parent.FullName

    end if

    Could you share us a simple demo to reproduce your issue?

    Regards,

    Tony


    Help each other

    Wednesday, March 18, 2015 7:28 AM
  • Hi JP3O,

    >> But that doesn't help because CurrentExcelWorksheetEventsInstance is not nothing even after all properties give errors. What is a better test or approach I can use?

    In my option, if you refer to the property of the Worksheet object which has been deleted, the error would be raised as expected. I think you could use the try catch statement to deal with this situation. Some key code like below:

            Dim oxl As Excel.Application
            Dim owb As Excel.Workbook
            Dim osheet As Excel.Worksheet
            Dim owbname As String
            oxl = New Excel.Application
            owb = oxl.Workbooks.Open("D:\Test\VSC#\03\Winform\VBWinform\Test.xlsx")
            osheet = owb.Worksheets("sheet1")
            Try
                osheet = owb.Worksheets.Add()
                owb.Close(False)
                owbname = owb.Name
            Catch comex As System.Runtime.InteropServices.COMException
                If comex.ErrorCode = -214682246 Or comex.Message.Contains("Object has been deleted") Then
                    ' handle deleted object
                Else
                    Throw comex
                End If
            End Try

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, March 20, 2015 5:00 AM