Closing Excel application with Excel Interop witho

Question

I am working with Excel Interop COM object. I am writing a method in which I am opening and closing an Excel application followed by opening an Excel workbook and sheet. After I'm done working on them I am closing both the application and workbook. My problem is that this method can be called repeatedly several times and when the sheet and app are closing I get from the Excel application a message if I want to save the changes I've made. The method get stuck until I press "No" but I can't tell the user to press it every time.

How can I close the application without receiving this message or at least answer it by code?

These are the methods I'm using to close the application and workbook:

private void FreeWorkSheetResources(Excel.Worksheet sheet)
{
  Marshal.ReleaseComObject(sheet);
  sheet = null;
}

private void FreeWorkBookResources()
{
  if (_excelSheets != null)
  {
    Marshal.ReleaseComObject(_excelSheets);
    _excelSheets = null;
  }
  _excelWorkBook.Close();
  Marshal.ReleaseComObject(_excelWorkBook);
  _excelWorkBook = null;
}

private void FreeApplicationResources()
{
  _app.Quit();
  Marshal.ReleaseComObject(_app);
  _app = null;
  GC.Collect();
}

And this is the way I'm using them:

if (_app == null)
  {
    _app = new Excel.Application();
  }
  if (_excelWorkBook == null)
  {
    _excelWorkBook = GetWorkBook(_filePath);
  }
  ....
  ....
  FreeWorkBookResources();
  FreeApplicationResources();

Answer 1

My problem was similar. I needed to generate Excel Sheet, then convert it to PDF. My problem was that the Excel App was displaying and notifying me to save before Exit. The solution was setting .Visible = flase and .DisplayAlerts = False

Thanks to @adil

Answer 2

I had this issue where the save dialog box was still displaying after opening and reading an Excel file without edit.

Setting

xlApplication.Visible = false;
xlApplication.DisplayAlerts = false;

and closing the workbook with the SaveChanges parameter as false

xlWorkbook.Close(false, Missing.Value, Missing.Value);

didn't work either. I was working with an Excel file type of .xlsb (binary with macros), when I saved as .xlsx (without macros) the .Close method worked. Saving as .xslm (xslx with macros) gave me the same problem of the save dialog displaying.

I turned to the MS support site on VBA for Excel and found this article, https://support.microsoft.com/en-us/help/213428/how-to-suppress-save-changes-prompt-when-you-close-a-workbook-in-excel detailing how to accomplish the close without save in a VBA macro. Adapting to C# I explicitly set the Saved property to true. The below worked for me using .xlsx, .xlsm, .xlsb file types.

        Excel.Application xlApp = new Excel.Application();
        xlApp.DisplayAlerts = false;
        xlApp.Visible = false;
        var xlWorkbook = xlApp.Workbooks.Open(fileInfo.FullName);

        // do work

        xlWorkbook.Saved = true;
        xlWorkbook.Close(false, Missing.Value, Missing.Value);
        xlApp.Quit();

Answer 3

Try this:

excelApp.DisplayAlerts = False;
//save and close your workbook
excelApp.DisplayAlerts = True;

Answer 4

While Closing Your Excel WorkBook you can use following Statements:

object misValue = System.Reflection.Missing.Value;
xlWorkBook.Close(false, misValue, misValue);

Answer 5

Do this when closing workbook.

_excelWorkBook.Close(true);