Read from Excel file and release COM

[mikeg71]

Hi, I am pretty new at opening and reading from Excel files in VB.NET. I have this code where I am simply reading in a few values from a single row and then closing the Excel file. My problem is that I continue to get an error:

System.Runtime.InteropServices.COMException: 'Exception from HRESULT: 0x800401A8'

It must be something about either opening or closing Excel but it is driving me crazy and I cannot seem to figure out what is happening. Here is the code that I have right now. Thanks for any help provided.

My error triggers on this line:

If worksheet.Cells(X, ColumnNumber).value = String2Search4 Then

Sub ExternalData()

        Dim APP As New Excel.Application
        Dim worksheet As Excel.Worksheet
        Dim workbook As Excel.Workbook
        Dim String2Search4 As String = txtPartNum.Text  'String to search for.
        Dim ColumnNumber As Integer = 1

        Try
            APP = CreateObject("Excel.Application")
            workbook = APP.Workbooks.Open("C:\temp\mag-kpi.xlsx")
            worksheet = workbook.Worksheets("Sheet1")
        Catch ex As Exception
            Dim unused = MsgBox("Error locating external data file. Locate file and try again!", vbExclamation, "")
            Exit Sub
        End Try

        'loop through each row
        For X As Integer = 1 To worksheet.Rows.Count Step 1
            'check if the cell value matches the search string.
            If worksheet.Cells(X, ColumnNumber).value = String2Search4 Then    <<<< this is where I get the above error.
                txtDataAnum.Text = String2Search4
                txtDataStart.Text = worksheet.Cells(X, ColumnNumber + 1).value
                txtDataComplete.Text = worksheet.Cells(X, ColumnNumber + 2).value
                txtDataQty.Text = worksheet.Cells(X, ColumnNumber + 3).value
                cmbLateStart.Text = dataReason

                If txtDataStart.Text < DateTime.Today And dataReason = "" Then
                    cmbLateStart.Visible = True
                    lblLateStart.Visible = True
                End If

            End If

            workbook.Close()
            APP.Quit()

        Next

        APP = Nothing
        worksheet = Nothing
        workbook = Nothing

    End Sub