none
Excel crashes when attempting to close one of multiple workbooks RRS feed

  • Question

  • Just an FYI for anyone else that may have encountered this issue.  I found, the hard way, that if I have a single workbook open, the following code in my add-in works fine:

                try
                {
                    toWorkbook.Close();
                }
                catch (System.Exception ex)
                {
                    writeLog(ex.Message);
                }

    However, if there are multiple workbooks, this same code will close the current workbook; Excel displays another workbook (correctly) if only temporarily; but then Excel crashes with a memory access violation (sometimes this occurs when you attempt to click or otherwise access a cell in the currently displayed worksheet).  This occurs whether or not i tell Excel to save or discard changes the the Close() method and irrespective of the saved state of the workbook that was closed.

    However, if I change the code as follows, there is no crash:

                try
                {
                    saveWorkbook(toWorkbook);
                    toWorkbook.Close();
                }
                catch (System.Exception ex)
                {
                    writeLog(ex.Message);
                }

    Thus, forcing a save (whether wanted or not) for the workbook to be closed and then closing it avoids the subsequent memory access violation.  I have no idea as to why this works; it just does.  This behavior is the same for Excel 2007, 2010, 2013, and 2016.

    Wednesday, January 6, 2016 7:20 PM

All replies

  • >>>However, if there are multiple workbooks, this same code will close the current workbook; Excel displays another workbook (correctly) if only temporarily; but then Excel crashes with a memory access violation (sometimes this occurs when you attempt to click or otherwise access a cell in the currently displayed worksheet).  This occurs whether or not i tell Excel to save or discard changes the the Close() method and irrespective of the saved state of the workbook that was closed.<<<

    According to your description, since there are no complete information, I have just created a sample to try to reproduce your issue, unfortunately, I can't, you could refer to below code:

    1. Add these codes into ThisAddin.cs file

    this.Application.Workbooks.Open("D:\\Examples.xlsx");            


    2. Add->New Item->Ribbon(Visual Designer),then drag two Buttons on it

    Excel.Workbook toWork = null;
    private void btnOpen_Click(object sender, RibbonControlEventArgs e)
    {
         string xlsxPath = "D:\\OfficeDev.xlsx";
         toWork = Globals.ThisAddIn.Application.Workbooks.Open(xlsxPath);
    
    }
    
    private void btnClose_Click(object sender, RibbonControlEventArgs e)
    {
         toWork.Close();
    }
    

    Otherwise could you provide full sample codes, that will help us reproduce and resolve your issue.
    Thanks for your understanding.

    Thursday, January 7, 2016 2:20 AM
  • I did not try your code, but here's code that will show you what occurs (this is the ThisAddin.cs file):

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Xml.Linq;

    using Excel = Microsoft.Office.Interop.Excel;
    using Office = Microsoft.Office.Core;
    using Microsoft.Office.Tools.Excel;

    namespace ExcelAddIn1
    {
        public partial class ThisAddIn
        {

            Excel.Workbook m_goWorkbook;

            private void ThisAddIn_Startup(object sender, System.EventArgs e)
            {
                writeLog("setExcelEventsHanlder");
                try
                {
                    Application.WindowActivate +=
                        new Excel.AppEvents_WindowActivateEventHandler(Application_WindowActivate);
                    Application.WorkbookActivate +=
                        new Excel.AppEvents_WorkbookActivateEventHandler(Application_WorkbookActivate);
                    Application.WorkbookBeforeSave +=
                        new Excel.AppEvents_WorkbookBeforeSaveEventHandler(Application_BeforeSave);
                    Application.WorkbookBeforeClose +=
                        new Excel.AppEvents_WorkbookBeforeCloseEventHandler(Application_BeforeClose);
                    Application.WorkbookOpen +=
                        new Excel.AppEvents_WorkbookOpenEventHandler(Application_WorkbookOpen);
                }
                catch (System.Exception ex)
                {
                    writeLog(ex.Message);
                }
            }

            private void BeforeClose(ref bool tbCancel)
            {
                try
                {
                    writeLog("BeforeClose " + m_goWorkbook.FullName.Trim());
                    Application_BeforeClose(m_goWorkbook, ref tbCancel);
                }
                catch (System.Exception ex)
                {
                    writeLog(ex.Message);
                }
            }

            private void BeforeSave(bool tbSaveAsUI, ref bool tbCancel)
            {
                try
                {
                    writeLog("BeforeSave " + m_goWorkbook.FullName.Trim());
                    Application_BeforeSave(m_goWorkbook, tbSaveAsUI, ref tbCancel);
                }
                catch (System.Exception ex)
                {
                    writeLog(ex.Message);
                }
            }

            public void Application_WorkbookOpen(Excel.Workbook toWorkbook = null)
            {
                try
                {
                    writeLog("Application_WorkbookOpen " + toWorkbook.FullName.Trim());
                    m_goWorkbook = toWorkbook;
                    m_goWorkbook.BeforeSave += BeforeSave;
                    m_goWorkbook.BeforeClose += BeforeClose;
                }
                catch (System.Exception ex)
                {
                    writeLog(ex.Message);
                }
            }

            public void Application_WindowActivate(Microsoft.Office.Interop.Excel.Workbook toWorkbook,
                Microsoft.Office.Interop.Excel.Window toWindow)
            {
                try
                {
                    writeLog("Application_WindowActivate " + toWorkbook.FullName.Trim());
                }
                catch (System.Exception ex)
                {
                    writeLog(ex.Message);
                }
            }

            public void Application_WorkbookActivate(Microsoft.Office.Interop.Excel.Workbook toWorkbook)
            {
                try
                {
                    writeLog("Application_WorkbookActivate " + toWorkbook.FullName.Trim());
                }
                catch (System.Exception ex)
                {
                    writeLog(ex.Message);
                }
            }

            public void Application_BeforeSave(Excel.Workbook toWorkbook, bool tbSaveAsUI, ref bool tbCancel)
            {
                try
                {
                    writeLog("Application_BeforeSave " + toWorkbook.FullName.Trim());
                    toWorkbook.Save();
                }
                catch (System.Exception ex)
                {
                    writeLog(ex.Message);
                }
            }

            public void Application_BeforeClose(Excel.Workbook toWorkbook,
                ref bool tbCancel)
            {
                try
                {
                    writeLog("Application_BeforeClose " + toWorkbook.FullName.Trim());
                    Application.WorkbookBeforeClose -=
                        new Excel.AppEvents_WorkbookBeforeCloseEventHandler(Application_BeforeClose);
                    //toWorkbook.Save();
                    toWorkbook.Close(false);
                    Application.WorkbookBeforeClose +=
                        new Excel.AppEvents_WorkbookBeforeCloseEventHandler(Application_BeforeClose);
                }
                catch (System.Exception ex)
                {
                    writeLog(ex.Message);
                }
            }

            public void writeLog(String tsMessage)
            {
                if (tsMessage != String.Empty)
                {
                    try
                    {
                        //
                        //  Append new text to an existing file.
                        // The using statement automatically flushes AND CLOSES the stream and calls  
                        // IDisposable.Dispose on the stream object.
                        //
                        using (System.IO.StreamWriter file =
                            new System.IO.StreamWriter(@"C:\Nataero\SimpleWriteLog.txt", true))
                        {
                            file.WriteLine(System.DateTime.Today.ToString() + ": " +
                                tsMessage.Trim());
                        }
                    }
                    catch (System.Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }
            }

            private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
            {
            }

            #region VSTO generated code

            /// <summary>
            /// Required method for Designer support - do not modify
            /// the contents of this method with the code editor.
            /// </summary>
            private void InternalStartup()
            {
                this.Startup += new System.EventHandler(ThisAddIn_Startup);
                this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
            }
            
            #endregion
        }
    }

    if you leave the toWorkbook.Save() commented out, Excel crashes.  make that line active, Excel works.

    Thanks.

    Gary

    Thursday, January 7, 2016 8:50 PM
  • Hi,kptools

    According to your description and sample code, I have create a Excel Add-in project to try to reproduce your issue, unfortunately, I can't.
    So I suggest that you could run your Excel Add-in project on other machine, then make sure whether this issue can be reproduced.

    In addition if you have other Excel Add-ins, you could disable them.

    Friday, January 8, 2016 8:29 AM
  • Actually, i have used this code to create Excel add-ins under VS2010, VS2013, and VS2015 on three separate machines (WIN7, WIN8, and WIN10) and then used that add-in for Excel 2007, Excel 2010, Excel 2013, and Excel 2016 on four separate machines.  The memory access error is consistent across the machines.  If you run this Addin code, open two separate workbooks, close one without saving it, Excel will crash.
    Friday, January 8, 2016 2:01 PM
  • in fact, try this code (I did on a Win7 and Win 8 set of machines, one with VS2010 and one with VS2015) and Excel 2010:

    using System;
    using System.Windows.Forms;

    using Excel = Microsoft.Office.Interop.Excel;

    namespace ExcelAddIn2
    {
        public partial class ThisAddIn
        {

            Excel.Workbook m_goWorkbook;

            private void ThisAddIn_Startup(object sender, System.EventArgs e)
            {
                try
                {
                    Application.WorkbookBeforeClose +=
                        new Excel.AppEvents_WorkbookBeforeCloseEventHandler(Application_BeforeClose);
                }
                catch (System.Exception ex)
                {
                }
            }

            public void Application_BeforeClose(Excel.Workbook toWorkbook,
                ref bool tbCancel)
            {
                try
                {
                    Application.WorkbookBeforeClose -=
                        new Excel.AppEvents_WorkbookBeforeCloseEventHandler(Application_BeforeClose);
                    toWorkbook.Save();
                    toWorkbook.Close(Type.Missing);
                    Application.WorkbookBeforeClose +=
                        new Excel.AppEvents_WorkbookBeforeCloseEventHandler(Application_BeforeClose);
                }
                catch (System.Exception ex)
                {
                }
            }

            private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
            {
            }

            #region VSTO generated code

            /// <summary>
            /// Required method for Designer support - do not modify
            /// the contents of this method with the code editor.
            /// </summary>
            private void InternalStartup()
            {
                this.Startup += new System.EventHandler(ThisAddIn_Startup);
                this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
            }

            #endregion
        }
    }

    nothing fancy; no ribbon; there are no other Addins in my Excel.

    then do the following:

    1.  change these lines as follows:

                    //toWorkbook.Save();
                    toWorkbook.Close();

    2. open two workbooks.  close one. attempt to access a cell in the still open workbook (it may crash before that).

    3.  change  toWorkbook.Close() to toWorkbook.Close(Type.Missing) and repeat.

    4. change  toWorkbook.Close() to toWorkbook.Close(true) and repeat.

    5. change  toWorkbook.Close() to toWorkbook.Close(false) and repeat.

    6. now, just uncomment the toWorkbook.Save() and you can verify that (a) Excel no longer crashes and (b) it does not matter how .Close() is set.

    Friday, January 8, 2016 10:03 PM
  • Hi, kptools

    Excel Version: 15.0.4779.1002
    Visual Studio Professional 2013: 12.0.21005.1 REL

    Based on your description and sample code, I have create a sample to try to reproduce your issue, unfortunately, I fail again.

    >>>2. open two workbooks.  close one. attempt to access a cell in the still open workbook (it may crash before that).

    Are your sample codes full? I can't find any codes about that attempt to access a cell in the still open workbook.

    Tuesday, January 12, 2016 6:24 AM
  • yes my codes are "full." all that is required is to click a cell with the mouse.  in my case, this is what is posted to the Application event log on my Windows 7 / Excel 2007 computer (the other errors are similar):

    Faulting application name: EXCEL.EXE, version: 12.0.6739.5000, time stamp: 0x5643f6e7
    Faulting module name: EXCEL.EXE, version: 12.0.6739.5000, time stamp: 0x5643f6e7
    Exception code: 0xc0000005
    Fault offset: 0x00049935
    Faulting process id: 0xd64
    Faulting application start time: 0x01d14ca22224e030
    Faulting application path: C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE
    Faulting module path: C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE
    Report Id: 8bae1df0-b895-11e5-80af-00219762b6e5

    Tuesday, January 12, 2016 4:25 PM
  • Hi, kptools

    Sorry I am not able to reproduce this issue, do you install and run this Excel Add-in on an other machine? I suggest that you could reinstall Office.

    Thursday, January 14, 2016 9:37 AM
  • this add-in was run on four or five separate machines with Excel 2007-2016 installed (one instance per machine, each machine with a difference instance).  It was also run on a test bed machine with Win 7 Pro and Office 2007.  It was run on my test LAN and at a beta site.  In each case, Excel crashes unless I have the workbook.save() invoked before the workbook.close().  Reinstalling Office is not an option and is not a solution.  this is a bug in Excel and, as far as I can tell, not in my code.
    Thursday, January 14, 2016 6:47 PM
  • Try this link to the simple project:

    https://onedrive.live.com/redir?resid=2FD4BEABC2B6E735!5199&authkey=!AM5IgNFOhbdwZwQ&ithint=folder%2c

    Saturday, January 16, 2016 6:16 AM
  • Hi,kptools

    Thanks you to share your project, I could get this log information from SimpleWriteLog.txt, Excel don't crash, refer to below:

    1/16/2016 12:00:00 AM: setExcelEventsHanlder
    1/16/2016 12:00:00 AM: Application_WorkbookOpen D:\OfficeDevCase.xlsx
    1/16/2016 12:00:00 AM: Application_WorkbookActivate D:\OfficeDevCase.xlsx
    1/16/2016 12:00:00 AM: Application_WindowActivate D:\OfficeDevCase.xlsx
    1/16/2016 12:00:00 AM: Application_WorkbookOpen D:\Students.xlsx
    1/16/2016 12:00:00 AM: Application_WorkbookActivate D:\Students.xlsx
    1/16/2016 12:00:00 AM: Application_WindowActivate D:\Students.xlsx
    1/16/2016 12:00:00 AM: Application_WorkbookActivate D:\OfficeDevCase.xlsx
    1/16/2016 12:00:00 AM: Application_WindowActivate D:\OfficeDevCase.xlsx
    1/16/2016 12:00:00 AM: BeforeClose D:\Students.xlsx
    1/16/2016 12:00:00 AM: Application_BeforeClose D:\Students.xlsx
    1/16/2016 12:00:00 AM: BeforeClose D:\Students.xlsx
    1/16/2016 12:00:00 AM: Application_BeforeClose D:\Students.xlsx
    1/16/2016 12:00:00 AM: Application_BeforeClose D:\Students.xlsx
    1/16/2016 12:00:00 AM: Application_WorkbookActivate D:\OfficeDevCase.xlsx
    1/16/2016 12:00:00 AM: Application_WindowActivate D:\OfficeDevCase.xlsx
    1/16/2016 12:00:00 AM: Application_BeforeClose D:\OfficeDevCase.xlsx
    1/16/2016 12:00:00 AM: Application_BeforeClose D:\OfficeDevCase.xlsx
    1/16/2016 12:00:00 AM: Exception from HRESULT: 0x800401A8

    Do you mean that you could get 0x800401A8 exception?




    Saturday, January 16, 2016 7:50 AM
  • the error code I get is as indicated above and, in my case, Excel stops working and restarts.  I do not get a writeLog error about the exception.  T

    he error I get is retrievable from the Events log (here's a error that Excel 2010 triggered on a machine running Windows 8.1 when I ran the add-in from Visual Studio 2015 under the debugger):

    Faulting application name: excel.exe, version: 14.0.7165.5002, time stamp: 0x567bcbee
    Faulting module name: excel.exe, version: 14.0.7165.5002, time stamp: 0x567bcbee
    Exception code: 0xc0000005
    Fault offset: 0x0016a96f
    Faulting process id: 0x1bbc
    Faulting application start time: 0x01d150723bc65a73
    Faulting application path: C:\Program Files (x86)\Microsoft Office\Office14\excel.exe
    Faulting module path: C:\Program Files (x86)\Microsoft Office\Office14\excel.exe
    Report Id: 91d3b25a-bc65-11e5-be74-0018f330ccd6
    Faulting package full name:
    Faulting package-relative application ID:

    Saturday, January 16, 2016 3:29 PM
  • Hi, kptools

    According to your description, I suggest that you could do the same things with VBA code. Could you reproduce this issue?

    For more information, click here to refer about Application Events (Excel)

    Monday, January 18, 2016 9:05 AM
  • using VBA is not an option for this application
    Monday, January 18, 2016 1:00 PM
  • Hi, kptools

    If you could use VBA reproduce this issue, you could make sure that this issue is related to Excel, so I suggest that you could submit feedback about Excel from link below:

    https://excel.uservoice.com/

    Thanks for your understanding.
    Tuesday, January 19, 2016 8:13 AM