Disconnect from Microsoft Excel Interop without crashing host C# program

Question

I have a C# program that is creating, writing, and saving an excel file using the Excel Interop. The problem is that if I don't have the application quit immediately after saving and closing the excel file then the c# application gets an unhandled exception and crashes. Has anyone found a way to do this while being able to keep the host c# application open and running.

Here is the code that handles everything involving the Excel Interop

 class CreateExcelDoc
{
    string newFormString = trialReportForm.newFormString;
   string fileString=trialReportForm.fileString;
   int sheetCount;
   string trialString = trialReportForm.trialString;
   string dateString = trialReportForm.dateString;
   string saveString = trialReportForm.saveSting;
   System.Windows.Forms.Timer excelTimer = new System.Windows.Forms.Timer();
    private Excel.Application app = null;
    private Excel.Workbook workbook = null;
    private Excel.Worksheet worksheet = null;
    private Excel.Range workSheet_range = null;
    public CreateExcelDoc()

    {
        createDoc();
    }

    public void createDoc()
    {

        try
        {
            app = new Excel.Application();
            //app.Visible = false;
            if (startForm.exportOwnerString == "Yes")
            {
                app.Visible = true;
                startForm.exportOwnerString = " ";
            }
            else
            {
                app.Visible = false;
            }
            if (startForm.excelActionFlag=="addNewTrialReport")
            {
                workbook = (Excel.Workbook) app.Workbooks.Add(1);
                //workbook.SaveAs(newFileForm.desktopPath + "\\" + "OB "+trialReportForm.otrClubNameString+" - "+trialReportForm.otrDateString);
                worksheet = (Excel.Worksheet)workbook.Worksheets[1];
                //fileNameString = newFileForm.desktopPath + "\\OB " + trialReportForm.otrClubNameString + " " + trialReportForm.otrDateString;
                workbook.Worksheets[1].Name = trialReportForm.trialReportDate+" Trial "+trialReportForm.trialReportTrialNumber;
            }
            else if (startForm.excelActionFlag == "ownerContacts")
            {
                workbook = (Excel.Workbook)app.Workbooks.Add(1);
                worksheet = (Excel.Worksheet)workbook.Worksheets[1];
                workbook.Worksheets[1].Name = "Owner Contacts";
            }
            else if (startForm.excelActionFlag == "newExcelResults")
            {
                string testFile = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\test";
                workbook = (Excel.Workbook)app.Workbooks.Add(1);

                worksheet = (Excel.Worksheet)workbook.Worksheets[1];
                workbook.Worksheets[1].Name = "Event 1 Results";
                //workbook.SaveAs(testFile, Missing.Value, Missing.Value, Missing.Value, false);
                //workbook.SaveAs(Environment.GetFolderPath(Environment.SpecialFolder.Desktop)+"\\Results", Missing.Value, Missing.Value, Missing.Value, false);



                //workbook.SaveAs(startForm.excelFileLocation, Missing.Value, Missing.Value, Missing.Value, false);

                //fileNameString = registrationForm.regFileLocation + "\\OB " + registrationForm.regClubName + " " + registrationForm.regDateString;
            }
            else if (dogForm.dogRegistrationExcel == "Yes")
            {
                workbook = (Excel.Workbook)app.Workbooks.Add(1);
                workbook.SaveAs(dogForm.filePath, Missing.Value, Missing.Value, Missing.Value, false);

            }
            else if (newFormString == "No")
            {

                //workbook = app.Workbooks.Open(fileString, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                // workbook.Close(true,fileString,Missing.Value);
                workbook = (Excel.Workbook)app.Workbooks.Open(fileString, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            }
            if (newFormString=="Yes"&registrationForm.formString=="OTR")
            {
                //string sheetName = "Trial Report - " + dateString + " " + trialString;
            worksheet = (Excel.Worksheet)workbook.Worksheets[1];
            workbook.Worksheets[1].Name =trialReportForm.otrDateString+" Trial " + trialReportForm.otrTrialString;
            //workbook.Worksheets[1].Name = "Trial Report - " + dateString + " " + trialString;
            //workbook.Worksheets[1].Name = "Hello";
            //Excel.Name name1 = worksheet.Names.Add("Trial Report - " + dateString + " " + trialString, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            //worksheet.Name = "Trial Report - " + dateString + " " + trialString;


            }
            else if (newFormString == "Yes" & registrationForm.formString == "Registration")
            {
                //string sheetName = "Trial Report - " + dateString + " " + trialString;
                worksheet = (Excel.Worksheet)workbook.Worksheets[1];
                workbook.Worksheets[1].Name = "Results: "+registrationForm.selectedEvent;
                //workbook.Worksheets[1].Name = "Trial Report - " + dateString + " " + trialString;
                //workbook.Worksheets[1].Name = "Hello";
                //Excel.Name name1 = worksheet.Names.Add("Trial Report - " + dateString + " " + trialString, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                //worksheet.Name = "Trial Report - " + dateString + " " + trialString;


            }
            else if (startForm.excelActionFlag == "addExistingTrialReport")
            {
                workbook = (Excel.Workbook)app.Workbooks.Open(startForm.excelFileLocation, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
               sheetCount = workbook.Worksheets.Count;
               int sheetCountPlusONe=sheetCount+1;
               worksheet = (Excel.Worksheet)workbook.Worksheets.Add(Missing.Value,workbook.Worksheets[sheetCount],Missing.Value,Missing.Value);
               workbook.Worksheets[sheetCountPlusONe].Name = trialReportForm.trialReportDate + " Trial " + trialReportForm.trialReportTrialNumber;

               //worksheet.Move(Missing.Value, workbook.Worksheets[sheetCount]);


            }
            else if (startForm.excelActionFlag == "existingExcelResults")
            {

                workbook = (Excel.Workbook)app.Workbooks.Open(startForm.excelFileLocation, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                sheetCount = workbook.Worksheets.Count;
                int sheetCountPlusONe = sheetCount + 1;
                worksheet = (Excel.Worksheet)workbook.Worksheets.Add(Missing.Value, workbook.Worksheets[sheetCount], Missing.Value, Missing.Value);
                workbook.Worksheets[sheetCountPlusONe].Name = "Event " + sheetCountPlusONe.ToString() + " Results";


                //worksheet.Move(Missing.Value, workbook.Worksheets[sheetCount]);


            }
            else if (dogForm.dogRegistrationExcel == "Yes")
            {
                worksheet = (Excel.Worksheet)workbook.Worksheets[1];
                workbook.Worksheets[1].Name = dogForm.dogUKCNumber;
            }
        }
        catch (Exception e)
        {
            Console.Write("Error");
        }
         /* if (trialReportForm.saveMe=="Yes")
            {
                workbook.Save();
                workbook.Close();
            }
        */


    }


    public void createHeaders(int row, int col, string htext, string cell1,
    string cell2, int mergeColumns, string b, bool font, int size, string
    fcolor)
    {
        worksheet.Cells[row, col] = htext;
        workSheet_range = worksheet.get_Range(cell1, cell2);
        workSheet_range.Merge(mergeColumns);
        switch (b)
        {
            case "BLUE":
                workSheet_range.Interior.Color = System.Drawing.Color.Red.ToArgb();
                break;
            case "GAINSBORO":
                workSheet_range.Interior.Color =
        System.Drawing.Color.Gainsboro.ToArgb();
                break;
            //case "Turquoise":
               // workSheet_range.Interior.Color =
        //System.Drawing.Color.Turquoise.ToArgb();
                //break;
            case "PeachPuff":
                workSheet_range.Interior.Color =
        System.Drawing.Color.PeachPuff.ToArgb();
                break;
            default:
                //  workSheet_range.Interior.Color = System.Drawing.Color..ToArgb();
                break;
        }

        //workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
        //workSheet_range.Borders = null;
        workSheet_range.Font.Bold = font;
        workSheet_range.ColumnWidth = size;
        //workSheet_range.HorizontalAlignment = ContentAlignment.BottomCenter;
        if (startForm.excelActionFlag == "existingExcelResults" | startForm.excelActionFlag=="newExcelResults")
        {
            workSheet_range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        }
        //workSheet_range.Cells.HorizontalAlignment = ContentAlignment.MiddleCenter;
        workSheet_range.Font.Color = System.Drawing.Color.FloralWhite.ToArgb();

    }

    public void addData(int row, int col, string data,
        string cell1, string cell2, string format)
    {
        worksheet.Cells[row, col] = data;
        workSheet_range = worksheet.get_Range(cell1, cell2);
        //workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
        workSheet_range.NumberFormat = format;
        workSheet_range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

        excelTimer.Tick += new EventHandler(excelTimer_Tick);
        excelTimer.Interval = 6000;
        excelTimer.Start();
    }
    void excelTimer_Tick(object sender, EventArgs e)
    {
        if (startForm.excelActionFlag != "ownerContacts")
        {
            if (startForm.excelActionFlag == "existingExcelResults" | startForm.excelActionFlag == "newExcelResults")
            {
               /* Excel.Range sortRange;
                sortRange = worksheet.get_Range("A14", "K32");
                Excel.Range scoreColumn;
                scoreColumn = worksheet.get_Range("C14", "C32");
                sortRange.Sort(scoreColumn, Excel.XlSortOrder.xlDescending);*/
                Excel.Range valueRange;
                Excel.Range placeRange;
                placeRange = worksheet.get_Range("A14", "A" + (14 + (registrationForm.numberofCompetitors - 1)).ToString());
                valueRange = worksheet.get_Range("A14", "K"+(14+(registrationForm.numberofCompetitors-1)).ToString());
                valueRange.Sort(valueRange.Columns[3, Type.Missing], Excel.XlSortOrder.xlDescending, Type.Missing, Type.Missing, Excel.XlSortOrder.xlAscending, Type.Missing, Excel.XlSortOrder.xlAscending, Excel.XlYesNoGuess.xlGuess, Type.Missing, Type.Missing, Excel.XlSortOrientation.xlSortColumns, Excel.XlSortMethod.xlPinYin, Excel.XlSortDataOption.xlSortNormal, Excel.XlSortDataOption.xlSortNormal, Excel.XlSortDataOption.xlSortNormal);
                placeRange.Sort(placeRange.Columns[1, Type.Missing], Excel.XlSortOrder.xlAscending, Type.Missing, Type.Missing, Excel.XlSortOrder.xlAscending, Type.Missing, Excel.XlSortOrder.xlAscending, Excel.XlYesNoGuess.xlGuess, Type.Missing, Type.Missing, Excel.XlSortOrientation.xlSortColumns, Excel.XlSortMethod.xlPinYin, Excel.XlSortDataOption.xlSortNormal, Excel.XlSortDataOption.xlSortNormal, Excel.XlSortDataOption.xlSortNormal); 
            }
            // workbook.Close(true, Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\Results.xls", Missing.Value);
            workbook.Close(true, startForm.excelFileLocation, Missing.Value);
            app.Quit();
            Application.Exit();

            /* workSheet_range = null;
             worksheet = null;
             workbook = null;
             app = null;*/
            //Thread.Sleep(5000);
            // File.Move(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\Results.xls", startForm.excelFileLocation);
        }

    }
}

It is certainly possible, I've done it several times. Can you post the code with your attempt to do it? — mfeingold, Oct 23 '12 at 19:39
I have also done this many, many times. Unfortunately, Excel does not return useful error codes (just one big generic error code which hides all other codes). The issue could very well be with Excel itself, but it's nearly impossible to diagnose the exact issue. — JDB, Oct 23 '12 at 19:42
Here is the exception I am getting: System.Runtime.InteropServices.InvalidComObjectException: COM object that has been separated from its underlying RCW cannot be used. at System.StubHelpers.StubHelpers.GetCOMIPFromRCW(Object objSrc, IntPtr pCPCMD, Boolean& pfNeedsRelease) at Microsoft.Office.Interop.Excel._Workbook.Close(Object SaveChanges, Object Filename, — user1546315, Oct 23 '12 at 20:09

score 1 · Answer 1 · edited May 23 '17 at 12:15

1

Several solutions including mine are presented How do I properly clean up Excel interop objects?

You can choose among the most sever (Killing Excel process) to more tender solutions (releasing COM object)

edited May 23 '17 at 12:15

Community

1
1

answered Oct 23 '12 at 19:44

Mohsen Afshin

13,273
10
65
90

score 1 · Accepted Answer · answered Oct 23 '12 at 20:46

I find that when saving with excel, I have more luck using the SaveAs method. For example:

            private static Microsoft.Office.Interop.Excel.Application xlApp = null;
            private static Microsoft.Office.Interop.Excel.Workbook xlWb = null;
            private static Microsoft.Office.Interop.Excel.Worksheet xlWs = null;


            //Your code and operations


            xlWb.SaveAs(filePath, XlFileFormat.xlExcel8, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange,
                        Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            xlApp.Quit();

            Marshal.ReleaseComObject(xlWs);
            Marshal.ReleaseComObject(xlWb);
            Marshal.ReleaseComObject(xlApp);

Disconnect from Microsoft Excel Interop without crashing host C# program

2 Answers2