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
Imports System.Runtime.InteropServices
<ComVisible(True)>
Public MustInherit Class ComEventCaster
Inherits StandardOleMarshalObject
Implements IDisposable
Private _Entries As New List(Of CPEntry)
Protected 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)))
Do
' Excel は S_FALSE(1) を返しつつ Count は 1
' COM によっては要素がないとき、S_FALSE を返し Count をゼロクリアしないことがある?
Marshal.WriteInt32(lpCount, 0)
Dim result As Integer = enmcp.Next(cp.Length, cp, lpCount)
Dim count As Integer = Marshal.ReadInt32(lpCount)
If result < 0 OrElse count = 0 Then
Exit Do
End If
For i As Integer = 0 To count - 1
Dim entry As New CPEntry(Me, cp(i))
_Entries.Add(entry)
Next
Loop
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)
End Using
Marshal.ReleaseComObject(app)
End Sub
Private Sub _App_NewWorkbook(Wb As Excel.Workbook)
Me.Text = Wb.Name
Marshal.ReleaseComObject(Wb)
End Sub
End Class