How To Capture an Excel VBA Runtime Error When Opening File Via Office Interop?
I was hoping someone had some insight to a little issue I have...
Basically, I am writing a WinForm app on Visual Studio .Net 2005 which will be a rudimentary "unit tester" for various MS Excel 2003 applications...basically, it will ensure the integrity of the VBA code which fires off in the event lifecycle of the workbook.
As a test for a "unit test failure," I created a simple workbook which generates a runtime error at the Workbook_Open event in the VBA code (an intentionally placed out-of-index call to an array).
I have no problem opening workbooks via the .Net WinForm using the Excel Object library. My problem is that the run-time error is not captured by the WinForm app -- instead, it throws the runtime error directly on the Excel thread which the WinForm app starts.
Is there a way for the WinForm app leveraging office interop to catch that excel runtime exception?
I've tried encasing the call in a Try-Catch block using a general Exception a COMException (via the System.Runtime.InteropServices library) catch...I've also tried instantiating the workbook directly using CreateObject(sPath) and instantiating an Excel Application, then using that to open the workbook...all with the same results...instead of the .Net app catching the exception, the runtime error bubbles up directly to the Excel application.
Is there any way, either on .Net or the VBA code, for the WinForm app to catch and handle an Excel run-time error?
Thanks for your help!