0

I'm trying to open an Excel Sheet, get the UsedRange and loop through it.

This already worked once, but then I had the exception

COM object that has been separated from its underlying RCW cannot be used

after using

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet);

Note that the exception kept coming up in the code below as well, not when actually releasing. I did this the first time and since then I couldn't get the code below to work. Sadly, I don't know at which line exactly he crashed anymore.

Because the exception kept comming up, I restarted my PC and since then when trying to access UsedRange, I get an Exception:

System.Runtime.InteropServices.COMException: "Ausnahme von HRESULT: 0x800401A8"

Here is the code:

using Excel = Microsoft.Office.Interop.Excel;
....
var xlApp = new Excel.Application();
var xlWorkBook = xlApp.Workbooks.Open(file);
Excel.Worksheet xlWorkSheet = xlWorkBook.Sheets["Tabelle1"];
for (int i = 1; i < xlWorkSheet.UsedRange.Rows.Count; i++)
{ ...

This worked once, so I'm thinking the COM object is still not working correctly. (Like in the first exception)

What can I do to make it work again?

Annabett
  • 69
  • 1
  • 8
  • 1
    There is zero need for `ReleaseComObject`. Just let .NET handle it for you. See https://stackoverflow.com/a/29081952/585968 and https://stackoverflow.com/a/25135685/585968. –  Oct 01 '19 at 10:49
  • @MickyD I got that by now. But I used it once and now I'm stuck with this error and I dunno how to make it work at all. – Annabett Oct 01 '19 at 11:42
  • By calling that method you are telling COM you explicitly want to control the object's lifetime (if done incorrectly can lead to that error), something that you don't need to worry about in .NET. This isn't say c/c++ –  Oct 01 '19 at 12:15

1 Answers1

1

What happened is that I accidentally closed the Workbook and quitted the App in the foor loop and not after it. I accidentally put it in front of the }.

for (int i = 1; i < xlWorkSheet.UsedRange.Rows.Count; i++)
{ 
  ...
  xlWorkBook.Close(false);
  xlApp.Quit();
}

Of course, that's supposed to go after the loop. You cannot access a variable in a workbook that isn't open anymore.

So if you've got the error, check if the workbook is actually really open.

Shame on me.

Annabett
  • 69
  • 1
  • 8
  • The fact that you are no longer calling `Marshal.ReleaseComObject` probably helps too. The fact you are no longer calling it `multiple times` _per object_ is even better. _[This method enables you to force an RCW reference count release so that it occurs precisely when you want it to. However, improper use of ReleaseComObject may cause your application to fail, or may cause an access violation](https://learn.microsoft.com/en-us/dotnet/api/system.runtime.interopservices.marshal.releasecomobject?view=netframework-4.8)_. Glad you got it working –  Oct 01 '19 at 12:13