Excel throws Exception from HRESULT: 0x800AC472 when preforming list.Contains()-CodePudding

I'm writing a Windows Form App on VS2019 that will open an excel file and perform nested loop.

Basically, there are two lists of integer. The code is going to loop through every row to check if the value of cell A exist in any of the lists, then assign a string to cell B.

The list in the original code has around 400 elements, but I cut it down to post here. Then something interesting happens. When I run the original code with 400 elements, it loops the row and stopped halfway, then it gives me the "Exception from HRESULT: 0x800AC472". However, when I run the codes below it has no error.

So I thought if the huge list is the problem. I wonder if any of you had met this scenario before and how you solve it.

[Update] I've added more elements to the list and tried to run it. It did throw the same exception. I also tried reducing the amount of the elements and run again and it worked without error. So I guess it's because of the amount of element.

Private Sub generateZone(ByVal worksheet As Object)
    'Initialise Excel Object
    xlsApp = New Excel.Application
    'Open file
    xlsWorkBook = xlsApp.Workbooks.Open("File location")
    'Open worksheet(according to the spreadsheet)
    xlsWorkSheet = xlsWorkBook.Worksheets("sheet1")

    'Excel interaction setting
    With xlsApp
        .Visible = True
        .Application.Visible = True
        .DisplayAlerts = False
        .EnableEvents = False
    End With        

    Dim emZone1 As New List(Of Integer)(New Integer() {87371, 87390, 94614, 92000, 82898, 96500, 99124, 93260, 82496, 97858, 90323, 88083, 80770, 84186, 86318, 91922, 85987, 80635, 84079, 96691, 85578, 83108, 96081, 87642, 96703, 96692, 99193, 93039, 97003, 89374, 99252, 82305, 87907, 90966, 80517, 88471, 92395, 86109, 87112, 92849, 93853, 91136, 90512, 97143, 96105, 93966, 81136, 97218, 97816, 82525, 97714, 98175, 94940, 97262, 81750, 92075, 98905, 96199, 94072, 83841, 88243, 98375, 84142, 92818, 83527, 97446, 88632, 86542, 84768, 86283, 84910, 88986, 92802, 99145, 81487, 84729, 80010, 90896, 99418, 87545, 95937, 89904, 88073, 85255, 87285, 88442, 86325, 90223, 92048, 85160, 98768, 80283, 91273, 92077, 91043, 81409, 96042, 82536, 92726, 91980})


    Dim emZone2 As New List(Of Integer)(New Integer() {86634, 92330, 95970, 95577, 87510, 89481, 94248, 93860, 81857, 82810, 93228, 80095, 94437, 84887, 88766, 92706, 92264, 88109, 91992, 82751, 94767, 95397, 96066, 91667, 94059, 89419, 82796, 82310, 86961, 85681, 93969, 81736, 81009, 97445, 80741, 92154, 84923, 86182, 91660, 90665, 81388, 87722, 94031, 94678, 84074, 80550, 82953, 81317, 95132, 92163})


    'get last row
    Dim lastRow As Integer
    lastRow = worksheet.UsedRange.Rows.Count


    'loop through every row
    For i As Integer = 1 To lastRow
        'get pronvice from column J
        Dim province As String = worksheet.Range("J" & i).Value
        If province = "Sabah" Or province = "Sarawak" Then

            'check zone1
            For Each zone1 As Integer In emZone1
                If emZone1.Contains(worksheet.Range("L" & i).Value) Then
                    worksheet.Range("M" & i).Value = "Zone 1"

                Else
                    'check zone2
                    For Each zone2 As Integer In emZone2
                        If emZone2.Contains(worksheet.Range("L" & i).Value) Then
                            worksheet.Range("M" & i).Value = "Zone 2"
                            ' Exception from HRESULT: 0x800AC472 shows here

                        Else
                            'if not in both zone
                            worksheet.Range("M" & i).Value = "EM"
                        End If
                    Next
                End If
            Next

        Else
            'if not sabah and sarawak
            worksheet.Range("M" & i).Value = "WM"
        End If

    Next

    'releaseObject(xlsWorkSheet)
    'releaseObject(xlsWorkBook)
    'releaseObject(xlsApp)

End Sub

CodePudding user response：

Not sure if this is the same but another SO post with this same error was resolved with the following answer:

"In my case, the exception was been thrown because my excel Interop tools was displaying a modal dialog (associated with a expired licence key - shame on me). If I closed the dialog (which was been displayed in the background) and then hit 'continue' in Visual Studio, the program was able to connect to the xlsx file and retrive data succesfully." -mbmihura

Have you verified that no popups or anything are appearing?

Is your excel licensed as well?

If not this,

I would add a limit to your loop and try to find if there is a fixed number of iterations before the error, if so, then there is some default limit being imposed.

One final thought,

I have seen these interop excel calls not get properly disposed in a lot of examples which causes many excels to be stuck running in the background.

Check your task manager and ensure that isn't happening. If so, close them out and try your tests again.

CodePudding user response：

I think you just need to check for empty cells before reading the value. Excel will throw an exception when the cell is empty and you try to parse it as a string

Dim xlsRange As Excel.Range = Nothing

    ' In your loop
    xlsRange = DirectCast(xlsWorkSheet.Cells("J" & i), Excel.Range)
    If xlsRange.Value IsNot Nothing Then
        Dim province = xlsRange.Value.ToString()
    End If
    ' /Loop

' always release it at the end

If xlsRange IsNot Nothing Then Marshal.ReleaseComObject(xlRange)

I still prefer loading everything into objects and operating on them after releasing all the Excel objects