A first chance exception of type 'System.InvalidOperationException' occurred in System.Windows.Forms.dll
In the automation program in Visual Basic 2008, below, I aim to fill a list box with the names of the workbooks that are currently open in Excel 2003.
The program Form1 has two controls Button1 and ListBox1, and handles the Excel event Application.NewWorkbook. It was project referenced to use COM's Microsoft Excel 2003.
Opening Excel 2003, running this program, and then pressing Button1 causes a workbook to be added to Excel. The Excel event NewWorkbook is raised and triggers the event handler at the bottom of the program xlApp_NewWorkbook.
This event handler calls PopulateList() to fill the list box. This is where the problem occurs.
As soon as the list box is cleared, the message "A first chance exception of type 'System.InvalidOperationException' occurred in System.Windows.Forms.dll" is issued and the list is not updated.
Here's the program:
Imports Microsoft.Office.Core Imports Excel = Microsoft.Office.Interop.Excel Public Class Form1 WithEvents xlApp As Excel.Application Private Sub Form1_Load( ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load xlApp = GetObject(, "Excel.Application") End Sub Private Sub Form1_Closed(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Closed xlApp = Nothing End Sub Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim xlBook As Excel.Workbook xlBook = xlApp.Workbooks.Add() End Sub Private Sub PopulateList() Dim xlBook As Excel.Workbook ListBox1.Items.Clear() For Each xlBook In xlApp.Workbooks ListBox1.Items.Add(xlBook.Name) Next End Sub Private Sub xlapp_NewWorkbook(ByVal w As Excel.Workbook) Handles xlApp.NewWorkbook Call PopulateList() End Sub End Class
Debugging the program, the debugger craps itself out (loses control of the debug session) on line
and does not even return from this line. Control goes straight back to the application and the list box is not updated.
I have been investigating the cause of this and it seems that xlApp_NewWorkbook is run in a worker thread. Because of this, ListBox1, in the main thread, can be read but cannot be updated from the worker thread.
I've found several supposed solutions. Apparently, one needs to invoke a delegate that is run in the main thread. And this is where I need your help. I cannot find a simple and clear description using Visual Basic to invoke PopulateList in the main thread using a delegate. I cannot understand the help, in what regards delegates. And cannot get my mind around the whole delegate and invoke thing, at least enough to adapt the examples provided (I believe some I found are not even possible nowadays).
Could someone please help me, demonstrating how to declare and invoke the delegate in the above program, so that the list box is populated with the names of the existing workbooks in Excel?
Many thanks in advance, if you have a go at it.Sunday, March 21, 2010 1:20 AM
By the way, I've tried this solution http://social.msdn.microsoft.com/Forums/en-US/clr/thread/8a4aa5ec-f307-4bd0-888d-730e3025b366 changing xlApp_NewWorkbook to
Private Sub xlapp_NewWorkbook( _ ByVal w As Excel.Workbook) Handles xlApp.NewWorkbook ListBox1.Invoke(CType(AddressOf PopulateList, MethodInvoker)) End Sub
but this doesn't even seem to call PopulateList().
If you do this, then you also have to change:
xlBook = xlApp.Workbooks.Add()
xlBook = xlApp.Workbooks.Add
and get the message:
Step into: Stepping over non-user code 'WindowsApplication1.Form1.ListBox1.get'
and the debugger loses it again.Sunday, March 21, 2010 1:34 AM
You might want to take a look at the example below. It's done in C# but should be relatively easy to convert:
Paul ~~~~ Microsoft MVP (Visual Basic)Tuesday, March 23, 2010 7:29 PM