none
How to save and close excel object in c# RRS feed

  • Question

  • Hi,

    I am creating an excel (2010) sheet in c#. The code is as follows:

     

                    Excel.Application appExcel = new Excel.Application();
                    appExcel.Visible = false;
                    Excel.Workbook workbook = appExcel.Workbooks.Add(1);
                    Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets[1];
    
                    workbook.SaveAs(save_file_name, Excel.XlFileFormat.xlWorkbookDefault);
                    appExcel.Workbooks.Close(); 
                    appExcel.Quit();
    
    

     

    I run into a few problems:

    1. In the save, it always prompts a warning as whether replace the old excel file. I need to replace old file by default without prompt.

    2. When opening the excel file, it prompts a box saying the saved excel file is not the correct format. It seems that program save excel file in .xls, while excel 2010 use .xlsx as default. So how to save excel into .xlsx?

    3. When system restarts, it opens up many excel windows with previously generated files by the program. It seems that excel objects in the program need be closed (removed) properly. How to do it in the program?

    Thanks.

     

     


    • Edited by york Z Wednesday, November 9, 2011 10:42 PM
    Wednesday, November 9, 2011 10:40 PM

Answers

  • workbook.save cannot specify file name.

    After changing code to:

                   object misValue = System.Reflection.Missing.Value;
                    Excel.Application appExcel = new Excel.Application();
                    appExcel.Visible = false;
                    Excel.Workbook workbook = appExcel.Workbooks.Add(misValue);
                    Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets[1];
    
                   workbook.SaveAs(save_file_name, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    
                    appExcel.Workbooks.Close(); 
                    appExcel.Quit();
                    Marshal.ReleaseComObject(worksheet);
                    Marshal.ReleaseComObject(workbook);
    

    2 and 3 are solved, but 1 remains. Does anyone know how to save excel file overwriting one with same name without prompting a box?

     

    • Marked as answer by york Z Thursday, November 10, 2011 4:20 AM
    Thursday, November 10, 2011 4:03 AM
  • You can check to see if the file exists, and if it does, delete it before saving this new one.
    • Marked as answer by york Z Thursday, July 5, 2012 10:30 PM
    Tuesday, July 3, 2012 9:25 PM

All replies

  • Hi York,

    1. Use workbook.save instead of saveas. This will save your changes overwriting the old version.

    2. the save should accept this and avoid the prompt.

    Note you can also use workbook.Close(true) to close and save changes.

    3. The Quit should be doing this. perhaps you have lost the reference somewhere? Try setting the visible property to true and watch what happens and if all workbooks are closed...

     


    Brian, ProcessIT- Hawke's Bay, New Zealand
    Wednesday, November 9, 2011 11:50 PM
  • Download Microsoft All-In-One Framework http://1code.codeplex.com/ then look at CSAutomateExcel folder under Visual Studio 2010 for a good example.
    KSG
    Thursday, November 10, 2011 4:03 AM
  • workbook.save cannot specify file name.

    After changing code to:

                   object misValue = System.Reflection.Missing.Value;
                    Excel.Application appExcel = new Excel.Application();
                    appExcel.Visible = false;
                    Excel.Workbook workbook = appExcel.Workbooks.Add(misValue);
                    Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets[1];
    
                   workbook.SaveAs(save_file_name, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    
                    appExcel.Workbooks.Close(); 
                    appExcel.Quit();
                    Marshal.ReleaseComObject(worksheet);
                    Marshal.ReleaseComObject(workbook);
    

    2 and 3 are solved, but 1 remains. Does anyone know how to save excel file overwriting one with same name without prompting a box?

     

    • Marked as answer by york Z Thursday, November 10, 2011 4:20 AM
    Thursday, November 10, 2011 4:03 AM
  • You can check to see if the file exists, and if it does, delete it before saving this new one.
    • Marked as answer by york Z Thursday, July 5, 2012 10:30 PM
    Tuesday, July 3, 2012 9:25 PM
  • appExcel.Workbooks.Close(true); 
    Thursday, March 23, 2017 6:29 PM