Hi All,
I am working on some excel automation logic. Basically it writes some values into various cells and runs a macro. This is to be repeated for each record in the database. for e.g. if the database has 100 records the macro has to be run 100 times after pumping values into cells. My problem is the application runs fine upto around 80 records and from there it is giving the following error and finally application is not able to close the workbook. The Excel.exe process is still running.
If I give 20 records each and run in 5 batches no problems.
The error is
System.Runtime.InteropServices.COMException (0x80010105): The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))
at Microsoft.Office.Interop.Excel.WorkbookClass.Close(Object SaveChanges, Object Filename, Object RouteWorkbook)
The code looks something like this
Try
oExcel = GetExcelObject()
oWorkBook = GetWorkBook(oExcel, StrExcelFileName)
oWorkSheets = GetWorkSheets(oWorkBook)
'Loop through all the records
'Write values into cells
'run the macro
'End loop
Catch ex As Exception
Throw New Exception(ex.Message & ex.StackTrace)
Finally
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWorkSheets)
oWorkSheets = Nothing
oWorkBook.Close(False)
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWorkBook)
oWorkBook = Nothing
oExcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oExcel)
oExcel = Nothing
End Try
Any idea on how to resolve this. Please share your comments on this.
Thanks & Regards
Excel interop The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))
Erwin Richard
Hello Shery,
maybe it has to do with the garbage collector.
We had a similar problem with Exchange RPC Connections and Outlook Objects.
We use a workaround, we count the items processed and after e.g. 20 Items you do a GC.Collect().
So the Garbage Collector releases the references to COM Objects clearly, and you can go on with your work.
There's also another approach. Since Excel objects still are COM Objects you can call explicitly a Marshal.ReleaseComObject (object) when you are finished with your e.g. workbook, so it's release from memory also.
Hope this helps,
Greets, Helmut Obertanner
[http://www.x4u.de]
cobrak
You will notice that the exception is thrown from Workbook.Close.
Your main code throws some exception. You catch it and throw another exception. Now you call Close and that fails (I do not know why yet). If you want to know why the original data insertion failed, you would be better off eliminating the catch clause altogether and enabling first chance exceptions (Debug/Exceptions/Common Language Runtime). This way you will be able to see why the original exception was thrown.
iouri
---
This posting is provided as-is and confers no rights
dakotahnorth
Thanks for your posts. I have realized that exceptions in the macros in spreadsheets are not properly handled. The team is informed to take care of this. I will implement GC.Collect. Hope this solves the problem.
Thanks & Regards
Robert Feutl
I have been getting the same exception, but only on some computers and not on others. They all have the same OS and MS Office version. The program itself resides on a network drive and all users involved have full access to the folder.
Any help would be appreciated.
RW