0

I have a c# program that opens an excel worksheet, populates it with some data, and retrieves some information from the worksheet before closing the sheet. This happens several times inside of a loop. The program executes for some time completing the loop and then fails at random times during the loop with the following error:

System.Runtime.InteropServicdes.COMException (0x80080005): Retrieving the COM class factor for component with CLSID {xxx} failed due to the following error: 80080005 Server execution failed [Exception from HRESULT:0x80080005 (CO_E_SERVER_EXCEC_FAILURE)).

I have checked my reference for Microsoft.Office.Interop.Excel and it is the Microsoft Excel 16.0 Object Library version 1.9.0.0 and I have Excel 2016 installed. The code that is causing the problem is similar to the following: 

for (int i = 0; i < 100; i++)
{
     Excel.Application xlApp;
     Excel.Workbooks xlWorkBooks;
     Excel.Workbook xlWorkBook;
     Excel.Sheets xlWorksheets;
     Excel.Worksheet xlWorkSheet;
     object misValue = System.Reflection.Missing.Value;

     xlApp = new Excel.Application(); //line that causes error

     xlApp.Visible = false;
     xlApp.ScreenUpdating = false;
     xlWorkBooks = xlApp.Workbooks;
     xlWorkBook = xlWorkBooks.Open(IIHSTemplate.Text, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
     xlWorksheets = xlWorkBook.Worksheets;
     xlWorkSheet = xlWorksheets["Automatic-Data-Analysis"];
     IIHSDemerit = Convert.ToDouble((xlWorkSheet.Cells[30, 14] as Excel.Range).Value2);

      xlWorkBook.Close(false, misValue, misValue);
      xlApp.Quit();

      releaseObject(xlWorksheets);
      releaseObject(xlWorkSheet);
      releaseObject(xlWorkBooks);
      releaseObject(xlWorkBook);
      releaseObject(xlApp);
}


private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }

My program writes the results of the excel data to a text file and executes several times with no errors. After a random number of iterations (number is different each time I run the program) the program will fail with the error listed above. Any ideas on what is causing the error and how to correct it?

SmrtGrunt
  • 869
  • 12
  • 25
lhb
  • 21
  • 4
  • 2
    Is there a reason to create and release the Excel App inside of the loop? It seems un-necessary when you are simply opening and closing workbooks. This might be a contributing factor as the system is trying to start and stop copies of Excel very quickly. – SmrtGrunt May 28 '19 at 19:03
  • Traditional bug, those releaseObject() calls do nothing useful. Use Task Manager, Processes tab to see the enormous number of instances of Excel.exe you created. That does stop somewhere, Excel is a very expensive process. Get ahead by starting it just once and [correctly releasing it](https://stackoverflow.com/a/25135685/17034). Log out and log back in to quickly recover from the process explosion. – Hans Passant May 28 '19 at 20:39
  • @Hans Passant - I checked the task manager and for each run the Microsoft Excel (32) bit process is started and stopped. There is no accumulation of Excel.exe processes. – lhb May 29 '19 at 12:34
  • @SmrtGrunt - Thanks for suggestion. Didn't notice it before, but after reading your comment and thinking about it, it's kind of obvious. Trying it now. – lhb May 29 '19 at 20:24

1 Answers1

1

Solution was to move the instantiation and releasing of the Excel app object out of the loop since I only needed to be opening and closing workbooks inside the loop. Thanks to @SmrtGrunt for pointing that out in comments.

lhb
  • 21
  • 4