I would just like to add this...
There is a document somewhere in the MSDN clarifying the COM interop. I
don't remember where exactly it was, but it says something about the runtime
creating only 1 reference to each object through a proxy, and reusing this
one afterwards. After some experiments, the simplest solution I found is
what I've posted below (And it's the ONLY case so far where I found it
useful to call the GC.Collect() explicitly). Keeping track of the objects
that are to be disposed is pretty hard, and after all, it might not work
well if you for example want to leave the excel open after doing whatever
you want to do. The GC.Collect() ways is pretty clean, as you really don't
have to care about any reference counting.
void DoSomethingWithExcel()
{
// do all your automation only in this method, leaving no references
around
..
...
...
}
void DoItNow()
{
DoSomethingWithExcel();
GC.Collect();
}
This way, you don't even need to bother about calling dispose, etc.
HTH,
Stefan
"Vagabond Software" <carlfenley-X-@-X-san.rr.com> wrote in message
news:uR**************@TK2MSFTNGP10.phx.gbl...
I was fortunate enough to resolve this problem this morning.
I created an ArrayList to store every Excel object that is created and use
my Dispose method to iterate through the ArrayList and get rid of all Excel
objects. I also added a GC.Collect call after the Dispose call in the
Reporter class. Here is the new code in the Dispose method of my Excel
class.
for (int i = 0; i < alXlObjects.Count; i++)
{
int referenceCount = 0;
do
{
referenceCount =
System.Runtime.InteropServices.Marshal.ReleaseComO bject(alXlObjects[i]);
alXlObjects[i] = null;
}
while (referenceCount > 0);
}
Thanks for the reply.
carl
"Colin R. R. Johnson" <cj@andjsolutions.com> wrote in message
news:wk*********************@news20.bellglobal.com ...
I think this is a bug in the COM for Excel.
I ran into the exact same problem doing reporting from a simple Access
database into a complex Excel spreadsheet.
I literally spent a couple of days trying to solve it, without any luck.
Vagabond Software wrote: Scenario:
The end-user selects File-Save from my applications (WinForm) menu,
specifies a filename, and waits waits for my app to write the contents
of a dataset to an Excel workbook. The SaveXLReport method completes
successfully, calls myXLClass.Dispose, and returns control to the user
form at a base state.
At this point, there is still an EXCEL.EXE in processes and the
specified workbook cannot be saved until that process is ended from Task
Manager or until the end-user exits my application, which also ends the
EXCEL.EXE process.
Here are relevant snippets from myXL:
public class MyXL : System.IDisposable
{
private excel9.Application xlapp;
public MyXL()
{
xlapp = null;
}
~MyXL()
{
if (xlapp != null)
Dispose(false);
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
public virtual void Dispose(bool disposing)
{
if (!this.disposed)
{
this.xlapp.Quit();
int referenceCount = 0;
do
{
referenceCount =
System.Runtime.InteropServices.Marshal.ReleaseComO bject(this.xlapp);
}
while (referenceCount > 0);
this.xlapp = null;
if (disposing)
{
GC.SuppressFinalize(this);
}
}
disposed = true;
}
}
Here is the SaveXLReport method from the Report class, which is a
windows form:
private bool SaveXLReport(DataSet ds)
{
bool failed = true;
string filename = ds.ExtendedProperties["ReportName"].ToString();
for (int i = 0; i < ds.Tables.Count; i++)
UpdateXLWorksheetData(filename, ds.Tables[i]);
if (cymerXL != null)
{
cymerXL.SaveOpenWorkbook();
cymerXL.Dispose();
failed = false;
}
return failed;
}
--
Colin R. R. Johnson
C&J Solutions
- Remember, everything you see on screen is but ones and zeroes.
