Results 1 to 12 of 12

Thread: Read from Excel file and release COM

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jan 2021
    Posts
    178

    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

  2. #2
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Read from Excel file and release COM

    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.
    Please remember next time...elections matter!

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Jan 2021
    Posts
    178

    Re: Read from Excel file and release COM

    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

  4. #4
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Read from Excel file and release COM

    Quote Originally Posted by mikeg71 View Post
    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.
    Please remember next time...elections matter!

  5. #5
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Read from Excel file and release COM

    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
    Please remember next time...elections matter!

  6. #6
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    845

    Re: Read from Excel file and release COM

    For me, it is because you use both

    Quote Originally Posted by mikeg71 View Post

    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)
    The best friend of any programmer is a search engine
    "Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
    “They did not know it was impossible so they did it” (Mark Twain)

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Jan 2021
    Posts
    178

    Re: Read from Excel file and release COM

    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

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Read from Excel file and release COM

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

  9. #9
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Read from Excel file and release COM

    Quote Originally Posted by mikeg71 View Post
    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.
    My usual boring signature: Nothing

  10. #10
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Read from Excel file and release COM

    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.
    Please remember next time...elections matter!

  11. #11
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Read from Excel file and release COM

    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
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    Jan 2021
    Posts
    178

    Re: Read from Excel file and release COM

    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width