C# issues with Excel?

Hi, I am developing software to automate a bunch of processes contained in Excel Workbooks. The range of operations I perform is accessing data directly in cells, calling VBA Macros, and also accessing functionality in third party AddIns. What's troubling me is that more often than not the program works just fine, but a good amount of the time my program throws an exception (usually an unhandled COM exception) when trying to perform these basic operations. My workbook and Macros are using the FactSet AddIn which I feel may be causing the trouble (I haven't been able to find much documentation or resources). I am wondering perhaps there is some deeper processing issue I need to address? Something related to threading? This is what the gist of my code looks like:

using Microsoft.Office.Interop; using Excel = Microsoft.Office.Interop.Excel; //other usings.... namespace MyExcelProgram { class Program { static void Main(string[] args) { Excel.Application myExcelApp; Excel.Workbooks myWorkbooks; Excel.Workbook myWorkbook; Excel.Worksheet myWorksheet; myExcelApp = new Excel.Application(); myExcelApp.Visible = true; myExcelApp.EnableEvents = true; myWorkbooks = myExcelApp.Workbooks; myWorkbook = myWorkbooks.Open(strWorkbookName); myWorksheet = (Excel.Worksheet)myWorkbook.Worksheets["Sheet1"]; //sometimes this throws an exception strData = (myWorksheet.Cells[7, 12] as Excel.Range).Value.ToString(); //sometimes this throws an exception myExcelApp.Run("MacroThatUsesAddin"); //sometimes this throws an exception myWorkbook.Save(); //sometimes this throws an exception myWorkbook.Close(false); //sometimes this or any of the below throws an exception myWorkbooks.Close(); myExcelApp.Quit(); Marshal.ReleaseComObject(myWorksheet); Marshal.ReleaseComObject(myWorkbook); Marshal.ReleaseComObject(myWorkbooks); Marshal.ReleaseComObject(myExcelApp); myWorksheet = null; myWorkbook = null; myWorkbooks = null; myExcelApp = null; } } } 

Again, what really drives me crazy is that the SAME code randomly throws an exception, sometimes it's fine, sometimes no. Any ideas or suggestions would be appreciated.

by SmokesInTheNight via /r/csharp

Leave a Reply