Save excel 2003 file

Question

I am doing a simple c# application which is to edit the existing excel 2003 template(xlt) and save to a new *.xls file. It is like:

Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Open("\\my_template.xlt");
Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[0];

Then i modify some cells which are used in various formulas inside the file and save template to a new file:

workbook.SaveAs("newfile.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
workbook.Close(true, misValue, misValue);
app.Quit();

worksheet = null;
workbook = null;
app = null;

So the problem is that when the new file is successfuly saved i open it and see that the results of formulas, which are using cells i have edited, are like "#NAME" and so on. When i reopen file - everything becomes ok. Why does it happen?

Maybe I am saving file by incorrect way and that is why I see errors during it's first opening? This problem is actual for 2003 office only... as I have admitted.

Answer 1

As mentioned here, change the enumeration to xlExcel8 instead of xlWorkbookNormal, and this will save in the correct format.

Secondly, setting your reference values to null doesn't really do anything in .Net, it's not like VB6, the garbage collector is smart enough to figure out whenever a reference is no longer being used, and clean it up.

Thirdly, if you are using .Net 4 or above, you don't need to supply values for the optional arguments of the Workbook Close method, so this works just as well:

workbook.Close(true);

Finally, you don't need to cast your Worksheet as a Worksheet, you can just do this:

Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.Sheets[0];

One final thought, use the marshal to release the application object once you are done, or Excel will hang around in your task manager forever(maybe do what you need to do inside a try block, and then do this in a finally block):

Marshal.ReleaseComObjct(app);

Updated version of method is below:

Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();

try
{
    Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Open(@"\my_template.xlt");
    Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.Sheets[0];

    //Do stuff

    workbook.SaveAs("newfile.xls", Microsoft.Office.Interop.Excel.XlFileFormat..xlExcel8);
    workbook.Close(true);
    app.Quit();
}
finally
{
    Marshal.ReleaseComObject(app);
}