Visual Studio OrderedTests: COM object that has been separated from its underlying RCW cannot be used

Question

First off, the technologies being used here include: Visual Studio, C#, Selenium WebDriver, Google Chrome, and MS Excel.

I'm in the process of building an automation framework to test multiple web applications. I'm using an Excel spreadsheet to pull variable data in so that other testers have a much easier time using the tests. During our smoke test, we run through several of the Unit Tests that I've built in order. Therefor, I figured it would be a good idea to create an Ordered Test. When running each test individually, they work just fine. However, once I start running the Ordered Tests, the first test in the list will run, but then I get this error:

System.Runtime.InteropServices.InvalidComObjectException: COM object that has been separated from its underlying RCW cannot be used.

I create the references to my Excel spreadsheet in a class called ReadFile:

static Excel.Application xlApp = new Excel.Application();
static Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"SPREADSHEET LOCATION");
static Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
static Excel.Range xlRange = xlWorksheet.UsedRange;

When my tests pass or fail, they call the CleanUp method, which consists of quitting the chromedriver and calling the ReadFile.Close() method:

xlWorkbook.Save();

xlWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);

xlApp.Quit();

GC.Collect();
GC.WaitForPendingFinalizers();

I've done quite a bit of research and found that there may be need to invoke Mashall.FinalReleaseComObject. I've tried using this as well, but it doesn't seem to make a difference. I have also noticed that the Excel process continues to run for a few seconds after the first test finishes. So I tried sleeping the thread to make it wait for Excel to completely close before the next test starts, but it also immediately throws the exception when Excel is removed (even if the sleep timer hasn't completed).

Anyways, at this point I'm looking for any guidance possible and it is greatly appreciated. Please keep in mind that I'm not an expert with any of these technologies.

Answer 1

Other than that, there are now good open source libraries (nuget packages) you coud use to read excel files, almost the same way you do with excel interop, but without excel, thus avoiding all possibe COM hurdles. Take a look at EPPlus or ExcelDataReader for example.