Excel/MyApp Threading Issue, pls help

[q1w2e3r4t7]

Trying to launch excel from within my application, and have any changes (registered from the changed event) feed directly back into my application.

Excel is opened and whatever text that is in the textbox is entered into cell A1.

On the cell change event, i want to pick the value from A1 and put it back into my application, however i get the message:

Cross-thread operation not valid: Control TextBox1 accessed from a thread other than the thread it was created on.

Public Class Form1

    Dim WithEvents excel As Microsoft.Office.Interop.Excel.Application
    Dim WithEvents wb As Microsoft.Office.Interop.Excel.Workbook
    Dim WithEvents ws As Microsoft.Office.Interop.Excel.Worksheet

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        excel = New Microsoft.Office.Interop.Excel.Application
        excel.Visible = True
        wb = excel.Workbooks.Add
        ws = wb.ActiveSheet
        ws.Range("A1").Value = TextBox1.Text
    End Sub

    Private Sub excel_WorkbookActivate(ByVal Wb As Microsoft.Office.Interop.Excel.Workbook) Handles excel.WorkbookActivate
        Me.wb = Wb
    End Sub

    Private Sub wb_SheetChange(ByVal Sh As Object, ByVal Target As Microsoft.Office.Interop.Excel.Range) Handles wb.SheetChange
        Me.ws = Sh
    End Sub

    Private Sub ws_Change(ByVal Target As Microsoft.Office.Interop.Excel.Range) Handles ws.Change
        Try
            TextBox1.Text = ws.Range("A1").Value

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

End Class

I have tried the use of delages etc as per other multi-threading technics, however still have the same error.

If anyone can help it would be greatly appreciated

 

[q1w2e3r4t7]

I got the following working in my test application. I will apply this to the full app and confirm if it solves my problem.

Public Class Form1

    Dim WithEvents excel As Microsoft.Office.Interop.Excel.Application
    Dim WithEvents wb As Microsoft.Office.Interop.Excel.Workbook
    Dim WithEvents ws As Microsoft.Office.Interop.Excel.Worksheet

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        excel = New Microsoft.Office.Interop.Excel.Application
        excel.Visible = True
        wb = excel.Workbooks.Add
        ws = wb.ActiveSheet
        ws.Range("A1").Value = TextBox1.Text
    End Sub

    Private Sub excel_WorkbookActivate(ByVal Wb As Microsoft.Office.Interop.Excel.Workbook) Handles excel.WorkbookActivate
        Me.wb = Wb
    End Sub

    Private Sub wb_SheetChange(ByVal Sh As Object, ByVal Target As Microsoft.Office.Interop.Excel.Range) Handles wb.SheetChange
        Me.ws = Sh
    End Sub

    Private Sub ws_Change(ByVal Target As Microsoft.Office.Interop.Excel.Range) Handles ws.Change
        TextBox1.BeginInvoke(New UpdateTextDelegate(AddressOf UpdateText), ws.Range("A1").Value.ToString)
    End Sub

    Public Delegate Sub UpdateTextDelegate(ByVal value As String)
    Sub UpdateText(ByVal value As String)
        TextBox1.Text = value
    End Sub

End Class

 

[Mike_R]

This is a really interesting issue. Ive never seen this before. On the other hand, I dont use Excel Events much using .NET....

Your idea to use a Delegate to pierce from one thread to the other is exactly the right idea. Nice job. I dont really know why this is happening in the first place, however. My guesses are that either:

(a) COM events in Excel are handled in a different thread?, or

(b) Its possible that because you are using out of process automation that the event call-back is marshalled through a different thread. That is, I dont know if this would be hapening from within an in-process Managed COM Add-in?


The other thing is that because you are using WithEvents variables, you will likely have trouble releasing your Excel Application instance. You should consider either:

(a) Using AddHandler and RemoveHandler to hook the events instead of WithEvents, or

(b) If you use WithEvents variables as you are now, then you should use of a cleanup routine similar to the following:

Sub CloseUpShop()

    GC.Collect()
    GC.WaitForPendingFinalizers()

     ReleaseAnyCOMObject(CObj(ws))

    wb.Close(SaveChanges:=False)
    ReleaseAnyCOMObject(CObj(wb))

     xlApp.Quit()
    ReleaseAnyCOMObject(CObj(xlApp))
End Sub

Sub ReleaseAnyCOMObject(ByRef o As Object)
    Dim tempVar As Object = o
    o = Nothing
    Marshal.FinalReleaseComObject(tempVar)
    tempVar = Nothing
End Sub

I hope this helps... And let us know how it goes!

Mike