Skip to main content
deleted 1 characters in body
Source Link
to StackOverflow
  • 125.3k
  • 34
  • 212
  • 345

As others have pointed out, you need to create an explicit reference for every Excel object you use, and call Marshal.ReleaseComObject on that reference, as described in this KB article. You also need to use try/finally to ensure ReleaseComObject is always called, even when an exception is thrown. I.e. instead of:

Worksheet sheet = excelApp.Worksheets(1)
... do something with sheet

you need to do something like:

Worksheets sheets = null;
Worksheet sheet = null
try
{ 
    sheets = excelApp.Worksheets;
    sheet = sheets(1);
    ...
}
finally
{
    if (sheetsheets != null) Marshal.ReleaseComObject(sheets);
    if (sheet != null) Marshal.ReleaseComObject(sheet);
}

You also need to call Application.Quit before releasing the Application object if you want Excel to close.

As you can see, this quickly becomes extremely unwieldy as soon as you try to do anything even moderately complex. I have successfully developed .NET applications with a simple wrapper class that wraps a few specificsimple manipulations of the Excel object model (open a workbook, write to a Range, save/close the workbook etc). The wrapper class implements IDisposable, carefully implements Marshal.ReleaseComObject on every object it uses, and does not pubicly expose any Excel objects to the rest of the app.

But this approach doesn't scale well for more complex requirements.

This is a big deficiency of .NET COM Interop. For more complex scenarios, I would seriously consider writing an ActiveX DLL in VB6 or other unmanaged language to which you can delegate all interaction with out-proc COM objects such as Office. You can then reference this ActiveX DLL from your .NET application, and things will be much easier as you will only need to release this one reference.

As others have pointed out, you need to create an explicit reference for every Excel object you use, and call Marshal.ReleaseComObject on that reference, as described in this KB article. You also need to use try/finally to ensure ReleaseComObject is always called, even when an exception is thrown. I.e. instead of:

Worksheet sheet = excelApp.Worksheets(1)
... do something with sheet

you need to do something like:

Worksheets sheets = null;
Worksheet sheet = null
try
{ 
    sheets = excelApp.Worksheets;
    sheet = sheets(1);
    ...
}
finally
{
    if (sheet != null) Marshal.ReleaseComObject(sheets);
    if (sheet != null) Marshal.ReleaseComObject(sheet);
}

You also need to call Application.Quit before releasing the Application object if you want Excel to close.

As you can see, this quickly becomes extremely unwieldy as soon as you try to do anything even moderately complex. I have successfully developed .NET applications with a simple wrapper class that wraps a few specific manipulations of the Excel object model (open a workbook, write to a Range, save/close the workbook etc). The wrapper class implements IDisposable, carefully implements Marshal.ReleaseComObject on every object it uses, and does not pubicly expose any Excel objects to the rest of the app.

But this approach doesn't scale well for more complex requirements.

This is a big deficiency of .NET COM Interop. For more complex scenarios, I would seriously consider writing an ActiveX DLL in VB6 or other unmanaged language to which you can delegate all interaction with out-proc COM objects such as Office. You can then reference this ActiveX DLL from your .NET application, and things will be much easier as you will only need to release this one reference.

As others have pointed out, you need to create an explicit reference for every Excel object you use, and call Marshal.ReleaseComObject on that reference, as described in this KB article. You also need to use try/finally to ensure ReleaseComObject is always called, even when an exception is thrown. I.e. instead of:

Worksheet sheet = excelApp.Worksheets(1)
... do something with sheet

you need to do something like:

Worksheets sheets = null;
Worksheet sheet = null
try
{ 
    sheets = excelApp.Worksheets;
    sheet = sheets(1);
    ...
}
finally
{
    if (sheets != null) Marshal.ReleaseComObject(sheets);
    if (sheet != null) Marshal.ReleaseComObject(sheet);
}

You also need to call Application.Quit before releasing the Application object if you want Excel to close.

As you can see, this quickly becomes extremely unwieldy as soon as you try to do anything even moderately complex. I have successfully developed .NET applications with a simple wrapper class that wraps a few simple manipulations of the Excel object model (open a workbook, write to a Range, save/close the workbook etc). The wrapper class implements IDisposable, carefully implements Marshal.ReleaseComObject on every object it uses, and does not pubicly expose any Excel objects to the rest of the app.

But this approach doesn't scale well for more complex requirements.

This is a big deficiency of .NET COM Interop. For more complex scenarios, I would seriously consider writing an ActiveX DLL in VB6 or other unmanaged language to which you can delegate all interaction with out-proc COM objects such as Office. You can then reference this ActiveX DLL from your .NET application, and things will be much easier as you will only need to release this one reference.

Source Link
to StackOverflow
  • 125.3k
  • 34
  • 212
  • 345

As others have pointed out, you need to create an explicit reference for every Excel object you use, and call Marshal.ReleaseComObject on that reference, as described in this KB article. You also need to use try/finally to ensure ReleaseComObject is always called, even when an exception is thrown. I.e. instead of:

Worksheet sheet = excelApp.Worksheets(1)
... do something with sheet

you need to do something like:

Worksheets sheets = null;
Worksheet sheet = null
try
{ 
    sheets = excelApp.Worksheets;
    sheet = sheets(1);
    ...
}
finally
{
    if (sheet != null) Marshal.ReleaseComObject(sheets);
    if (sheet != null) Marshal.ReleaseComObject(sheet);
}

You also need to call Application.Quit before releasing the Application object if you want Excel to close.

As you can see, this quickly becomes extremely unwieldy as soon as you try to do anything even moderately complex. I have successfully developed .NET applications with a simple wrapper class that wraps a few specific manipulations of the Excel object model (open a workbook, write to a Range, save/close the workbook etc). The wrapper class implements IDisposable, carefully implements Marshal.ReleaseComObject on every object it uses, and does not pubicly expose any Excel objects to the rest of the app.

But this approach doesn't scale well for more complex requirements.

This is a big deficiency of .NET COM Interop. For more complex scenarios, I would seriously consider writing an ActiveX DLL in VB6 or other unmanaged language to which you can delegate all interaction with out-proc COM objects such as Office. You can then reference this ActiveX DLL from your .NET application, and things will be much easier as you will only need to release this one reference.