Read from Excel file and release COM

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:

Code:

---

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:

Code:

---

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

---

Code:

---

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

---

Just for grins after a few tests look at the task manager and see if you have multiple versions of Excel running. That can cause odd errors.

Thanks TysonLPrice... I did check that and I do have multiples running in Task Manager. I will usually kill all of them and then test again, but then I get the same error. I have gone as far as testing on two separate PC's with different versions of MS Office with the hopes that would tell me something, but no luck. Same error

Quote:

---

Originally Posted by mikeg71

Thanks TysonLPrice... I did check that and I do have multiples running in Task Manager. I will usually kill all of them and then test again, but then I get the same error. I have gone as far as testing on two separate PC's with different versions of MS Office with the hopes that would tell me something, but no luck. Same error

---

The first thing I would do is get it so after you run it Excel has been properly closed (no longer running in Task manager). If you Google how to close Excel properly from .Net you will get lots of hits. If you use the search feature here you will get hits also.

Here is an example I found:

Code:

---

Imports Microsoft.Office.Interop

Public Class Form1
    Private xl As Excel.Application
    Private xlWorkBook As Excel.Workbook
    Private xlWorksheet As Excel.Worksheet

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click


        Dim xl As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing

        xl = New Excel.Application
        xl.DisplayAlerts = False
        xlWorkBooks = xl.Workbooks
        xlWorkBook = xlWorkBooks.Open("C:\pirs.xlsx")
        xlWorksheet = xl.Sheets(1)
        xlWorksheet.Activate()

        xl.Visible = False

        xlWorkBook.Close()
        xlWorksheet = Nothing

        xl.Quit()

        ReleaseComObject(xlWorkBook)
        ReleaseComObject(xlWorkBooks)
        ReleaseComObject(xl)

        GC.Collect()



    End Sub

    Private Sub ReleaseComObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
            Exit Sub
        Catch ex As Exception
            obj = Nothing
        End Try
    End Sub

End Class

---

For me, it is because you use both

Quote:

---

Originally Posted by mikeg71

Code:

---


        Dim APP As New Excel.Application
     
            APP = CreateObject("Excel.Application")

---

---

. Either the file is already open and you use

Code:

---

Dim XlApp As Object = CreateObject("excel.application")
Dim wb As Excel.Workbook
wb = XlApp.Workbooks.Open(your path here")

---

or the file is not already open and you use :

Code:

---

Dim xl As New Excel.Application
Dim wb As Excel.Workbook
wb = xl.Workbooks.Open(your path here)

---

Thanks for the help all... I was really hoping that was my entire issue. I think I am going crazy as it seems like it must be something ridiculous staring right at me and I don't see it. I re-wrote part of the code, re-ran it and still get the same error.

Code:

---

    Sub ExternalData()

        Dim String2Search4 As String = txtPartNum.Text
        Dim ColumnNumber As Integer = 1
        Dim xl As New Excel.Application
        Dim wb As Excel.Workbook
        Dim ws As Excel.Worksheet

        Try
            wb = xl.Workbooks.Open("C:\temp\mag-kpi.xlsx")
            ws = wb.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 ws.Rows.Count Step 1
            'check if the cell value matches the search string.
            If ws.Cells(X, ColumnNumber).value = String2Search4 Then
                txtDataAnum.Text = String2Search4
                txtDataStart.Text = ws.Cells(X, ColumnNumber + 1).value
                txtDataComplete.Text = ws.Cells(X, ColumnNumber + 2).value
                txtDataQty.Text = ws.Cells(X, ColumnNumber + 3).value
                cmbLateStart.Text = dataReason

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

                wb.Close(SaveChanges:=False)
                xl.Quit()

            End If

        Next

        ws = Nothing
        wb = Nothing
        xl = Nothing

    End Sub

---

Base your code on TysonLPrice's example, especially including ReleaseComObject and using that - as that is what should ensure that Excel closes.

Quote:

---

Originally Posted by mikeg71

I think I am going crazy as it seems like it must be something ridiculous staring right at me and I don't see it.

---

Oh, don't worry about that. You were already crazy. We know because you decided to automate Excel in your program;)

There are so many interesting little gotchas with Excel. Great program, and well loved by users, but it sure can cause you trouble. Opening and closing it is the first, and often the biggest, issue that people tend to have. So, instead of trying to do something useful, try opening Excel then immediately closing it. If that leaves it running (check Task Manager), then something is wrong. Get that part sorted before you attempt anything useful so that you can isolate where the issues you are seeing could be coming from. Then add on useful work between the opening and the closing.

I didn't mention it, and it is documented in another post here, but if you wrap the code I posted in a try/catch it will stop disposing of Excel. There were a few explanations given for that. I just took it out when I discovered that.

try it this way, you have to set the Range you want
as an example I used the Column A, but you can also set A:K (Columns A to K)

Code:

---

Option Strict On
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop

 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Try
            Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
            Dim xlWb As Microsoft.Office.Interop.Excel.Workbook
            xlWb = xlApp.Workbooks.Open("E:\vbexcel.xlsx")
            Dim xlSt As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWb.Worksheets("Tabelle4"), Worksheet)

            'search for:
            Dim StringToSearch = "Chris"
            'saerch in Column A
            Dim rngSearchValue As Range = xlSt.Range("A:A")

            'or in Columns A to K
            ' Dim rngSearchValue As Range = xlSt.Range("A:K")

            With xlSt
                Dim c As Excel.Range
                Dim firstAddress As String = String.Empty
                c = rngSearchValue.Find(StringToSearch, LookIn:=XlFindLookIn.xlValues)
                Do
                    If Not c Is Nothing Then
                        If String.IsNullOrEmpty(firstAddress) Then firstAddress = c.Address
                        c = rngSearchValue.FindNext(c)
                        'set a color on found Value
                        c.Interior.Color = Color.Gray
                        Debug.WriteLine(c.Address)
                    Else
                        Exit Do
                    End If
                Loop While c.Address <> firstAddress
            End With
            xlWb.Save()
            xlApp.Quit()
            xlApp = Nothing
        Catch g As Exception
            MessageBox.Show(g.ToString)
        End Try
    End Sub

---

Thanks all for the extensive help on this as well as your time, I greatly appreciate it. I am going to play around with everything that was sent here and see what kind of results I come up with. Thanks again