Excel COM addin crash and weird behaviour

Vote count: 0

I am making this COM add-in for Excel 2013 & 2016 that works with XLSM templates (with their own VBA macros and formulas inside already).

I have buttons in the ribbon that trigger custom addin functionality, and sometimes they crash Excel in a way where I'm unable to track what happened wrong. It happens mostly, when the add-in has been used for a while, opening various files, writing into them and closing them, BUT it can also happen on a fresh start of Excel.

public void NewInfoButtonClicked(Office.IRibbonControl control)
    {
        Excel.Application excelApp = Globals.ThisAddIn.Application;
        Excel.Workbook FirstWorkbook = excelApp.ActiveWorkbook;
        Excel.Workbook SecondWorkbook;
        Excel.Worksheet FirstWB_In = FirstWorkbook.Worksheets["F_Info_In"];
        try
        {
            if (!IsEditing(excelApp)) //checks if any of the cells are in edit mode
            {
                String PathToNewFile = (FirstWB_In.Cells[9, 16] as Excel.Range).Value2;
                String FileName = (FirstWB_In.Cells[10, 16] as Excel.Range).Value2;
                if (File.Exists(PathToNewFile))
                {
                    //if not open
                    if (!OpenArchiveDialog.IsOpened(FileName))
                    {
                        SecondWorkbook = excelApp.Workbooks.Open(PathToNewFile);
                        SecondWorkbook.Activate();
                        try 
                        {
                            ((Excel.Worksheet)excelApp.ActiveWorkbook.Sheets[1]).Select(); //tried it with and without this
                        }
                        catch { }
                    }
                    else
                    {
                        //it's already opened- never happens
                        SecondWorkbook = excelApp.Workbooks[FileName];
                    }
                    Excel.Worksheet SecondWB_In = SecondWorkbook.Sheets["S_Info_In"];

                    //Do stuff in the newly opened workbook
                    SecondWorkbook.Unprotect("password");

                    (SecondWB_In.Cells[1, 2] as Excel.Range).Value2 = "This is updated";
                    (SecondWB_In.Cells[1, 6] as Excel.Range).Value2 = 1;

                    SecondWorkbook.Protect("password");

                    SecondWorkbook.SaveAs(QuoteSystemHandlerRibbon.OutputFilePath + "Newfilename" + ".xlsm", Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled);
                }
                FirstWorkbook.Close(true);
            }

        }
        catch(Exception ex)
        {
            //show exception / log
        }
        finally
        {
            Marshal.ReleaseComObject(FirstWorkbook);
            Marshal.ReleaseComObject(FirstWB_In);
        }
    }

Any insight, on what I could be doing wrongly?

I've tried with/without the Marshal.ReleaseComObject part, with no success. Tried not to use the 'double dotting' technique...

Am I referencing the cells in a bad way?

asked 48 secs ago

Laureant

Let's block ads! (Why?)

Excel COM addin crash and weird behaviour