I would like to open the excel workbook and then change some formats and save back with the new file name.
When I use the following code, if there is no error, it's OK. Excel object is swept from the memory.
But if there are some errors, Excel Object is still the memory. Even though I use try..catch..finally block, it still happens. Help Me.
Thanks,
TW
Code:
public Excel.Application oXL = null;
public Excel._Workbook oWB = null;
public Excel.Sheets excelSheets = null;
try
{
oXL = new Excel.ApplicationClass(); // Run the Excel Application
string workbookPath = txtfile.Text; // Get the Filename
// Opening the Excel Workbook
oWB = oXL.Workbooks.Open(workbookPath,
0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
true, true, 0, true, false, false);
excelSheets = oWB.Worksheets;
// Changing the format of the Excel
foreach (Excel.Worksheet worksheet in excelSheets)
{
//Change the format of the Sheet.....
}
// Output Filename
string newFileName = "testing.xls";
// Saving the formatted Excel
oWB.SaveAs( newFileName,Excel.XlFileFormat.xlWorkbookNormal,
null,null,false,false,Excel.XlSaveAsAccessMode.xlShared,
false,false,null,null,null);
// Show Success Message
MessageBox.Show("Your file is successfully changed!","SUCCESS",MessageBoxButtons.OK,MessageBoxIcon.Information);
}
catch (Exception exc)
{
MessageBox.Show(exc.Message,"ERROR",MessageBoxButtons.OK,MessageBoxIcon.Error);
}
finally
{
// Need all following code to clean up and extingush all references!!!
oWB.Close(false,null,null);
oXL.Workbooks.Close();
oXL.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject (excelSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oWB);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oXL);
oWB = null;
excelSheets = null;
oXL = null;
GC.Collect(); // force final cleanup!
}