I have a little function in my asp / C# site which exports some data through excel using PIA.
After some research along days about how to use properly all of this, sometimes I'm getting this error:
COM object that has been separated from its underlying RCW cannot be used.
at Microsoft.Office.Interop.Excel.ApplicationClass.set_DisplayAlerts(Boolean RHS)
After that, I always get this another error, but i supose that it's related/caused by the first error:
Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
at Microsoft.Office.Interop.Excel.ApplicationClass.Quit()
I'm feeling quite confused because I tested this in three computers, and works fine in two of them.
The relevant code is the following:
public static class Export{
public static Application objExcel;
public static Workbooks objBooks;
public static _Workbook objBook;
public static Sheets objSheets;
public static _Worksheet objSheet;
public static Boolean export (DataSet ds, String path)
{
try
{
objExcel = new Application();
objBooks = objExcel.Workbooks;
objBook = objBooks.Add(XlWBATemplate.xlWBATWorksheet);
objSheets = objBook.Worksheets;
objSheet = (_Worksheet)objSheets.get_Item(1);
Range objRange = null;
//Do things . . .
MergeCells(objExcel, objSheet);
//Do more things . . .
catch (Exception ex)
{
Global.ManageException(ex);
}
finally
{
if (objExcel != null)
objExcel.Quit();
ReleaseComObject(objExcel);
ReleaseComObject(objBooks);
ReleaseComObject(objBook);
ReleaseComObject(objSheets);
ReleaseComObject(objSheet);
ReleaseComObject(objRange);
}
RelecaseComObject() is the following:
private static void ReleaseComObject(object reference)
{
try
{
if(reference!=null)
while(System.Runtime.InteropServices.Marshal.ReleaseComObject(reference) > 0)
{}
}
catch (Exception e)
{
Global.ManageException(e);
}
}
And MergeCells() is something like this:
private static void MergeCells(Application objExcel, _Worksheet objSheet)
{
objExcel.DisplayAlerts = false;
// Do things
objExcel.DisplayAlerts = true;
}
Taking a look to the line of the error, may it be caused by calling objExcel.DisplayAlerts from inside of that function instead the main function? (I would like to try it but I have some limitations to deploy the code many times in the third computer).
How can I manage the code properly to make it safe and avoid this kind of errors?
Thanks in advance.