-
Feb 22nd, 2021, 12:51 PM
#1
Thread Starter
Addicted Member
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
-
Feb 22nd, 2021, 02:18 PM
#2
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!
-
Feb 22nd, 2021, 02:25 PM
#3
Thread Starter
Addicted Member
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
-
Feb 22nd, 2021, 02:29 PM
#4
Re: Read from Excel file and release COM
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.
Please remember next time...elections matter!
-
Feb 22nd, 2021, 02:35 PM
#5
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!
-
Feb 22nd, 2021, 03:53 PM
#6
Re: Read from Excel file and release COM
For me, it is because you use both
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)
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)
-
Feb 22nd, 2021, 04:44 PM
#7
Thread Starter
Addicted Member
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
-
Feb 22nd, 2021, 04:53 PM
#8
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.
-
Feb 22nd, 2021, 05:37 PM
#9
Re: Read from Excel file and release COM
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.
My usual boring signature: Nothing
-
Feb 23rd, 2021, 05:45 AM
#10
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!
-
Feb 23rd, 2021, 08:38 AM
#11
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.
-
Feb 23rd, 2021, 09:39 AM
#12
Thread Starter
Addicted Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|