Top / .NET備忘録 / 99.小ネタ / 15.Excel のイベント

以下のようなプログラムを書いてデバッグ実行すると、Me.Text = Wb.Name のところで System.InvalidOperationException が発生します。

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim app As New Excel.Application
        AddHandler app.NewWorkbook, AddressOf _App_NewWorkbook
        Dim wbs As Excel.Workbooks = app.Workbooks
        Dim wb As Excel.Workbook = wbs.Add()
        app.Quit()
        Marshal.ReleaseComObject(wb)
        Marshal.ReleaseComObject(wbs)
        Marshal.ReleaseComObject(app)
    End Sub

    Private Sub _App_NewWorkbook(Wb As Excel.Workbook)
        Me.Text = Wb.Name                   ' ←System.InvalidOperationException が発生する
        Marshal.ReleaseComObject(Wb)
    End Sub

End Class

これは、フォームのスレッドと違うスレッドでイベントが動作しているからです。

イベントをフォームのスレッドで動作させるには、ちょっと工夫が必要です。

わかってしまえば簡単なのですが、IConnectionPoint インターフェイスの Advise メソッドを実行する際、第一引数のシンクオブジェクトを StandardOleMarshalObject から派生したものにすればいいだけです。

まず、シンクオブジェクトの基底クラスを作ります。

Imports System.Runtime.InteropServices

<ComVisible(True)>
Public MustInherit Class ComEventCaster
    Inherits System.Runtime.InteropServices.StandardOleMarshalObject
    Implements IDisposable

    Private _Entries As New List(Of CPEntry)

    Public Sub New(ByVal comObject As Object)
        Dim cpc As ComTypes.IConnectionPointContainer = DirectCast(comObject, ComTypes.IConnectionPointContainer)
        Dim enmcp As ComTypes.IEnumConnectionPoints = Nothing
        cpc.EnumConnectionPoints(enmcp)
        Dim cp(10) As ComTypes.IConnectionPoint
        Dim lpCount As IntPtr = Marshal.AllocHGlobal(Marshal.SizeOf(GetType(Integer)))
        While enmcp.Next(cp.Length, cp, lpCount) >= 0
            Dim count As Integer = Marshal.ReadInt32(lpCount)
            If count > 0 Then
                For i As Integer = 0 To count - 1
                    Dim entry As New CPEntry(Me, cp(i))
                    _Entries.Add(entry)
                Next
            Else
                Exit While
            End If
        End While
        Marshal.FreeHGlobal(lpCount)
        Marshal.ReleaseComObject(enmcp)
    End Sub

    Public Sub Dispose() Implements IDisposable.Dispose
        For Each cp As CPEntry In _Entries
            cp.Dispose()
        Next
        _Entries.Clear()
        GC.SuppressFinalize(Me)
    End Sub

    Private Class CPEntry
        Implements IDisposable

        Public ConnectionPoint As ComTypes.IConnectionPoint
        Private Cookie As Integer

        Public Sub New(ByVal owner As ComEventCaster, ByVal cp As ComTypes.IConnectionPoint)
            Me.ConnectionPoint = cp
            cp.Advise(owner, Cookie)
        End Sub

        Public Sub Dispose() Implements IDisposable.Dispose
            If Me.ConnectionPoint IsNot Nothing Then
                Me.ConnectionPoint.Unadvise(Cookie)
                Marshal.ReleaseComObject(Me.ConnectionPoint)
                Me.ConnectionPoint = Nothing
            End If
            GC.SuppressFinalize(Me)
        End Sub

    End Class
End Class

それを継承して Excel.Application のシンクオブジェクトクラスを作ります。

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices

<ComVisible(True)>
Public Class AppEventCaster
    Inherits ComEventCaster

    Public Sub New(ByVal comObject As Object)
        MyBase.New(comobject)
    End Sub

    <DispId(1565)>
    Public Sub RaiseNewWorkbook(wb As Excel.Workbook)
        RaiseEvent NewWorkbook(wb)
    End Sub

    Public Event NewWorkbook As Excel.AppEvents_NewWorkbookEventHandler

End Class

使ってみましょう。正常に実行できるはずです。

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices

Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim app As New Excel.Application
        Using caster As New AppEventCaster(app)
            AddHandler caster.NewWorkbook, AddressOf _App_NewWorkbook
            Dim wbs As Excel.Workbooks = app.Workbooks
            Dim wb As Excel.Workbook = wbs.Add()
            app.Quit()
            Marshal.ReleaseComObject(wb)
            Marshal.ReleaseComObject(wbs)
            Marshal.ReleaseComObject(app)
        End Using
    End Sub

    Private Sub _App_NewWorkbook(Wb As Excel.Workbook)
        Me.Text = Wb.Name
        Marshal.ReleaseComObject(Wb)
    End Sub

End Class



トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   最終更新のRSS
Last-modified: 2017-08-10 (木) 16:35:54 (101d)