COM Interop – The Appetiser

So, you want to get your head around COM Interop? (You don’t? Well, just imagine you did.) You could read Adam Nathan’s 1500 page classic (which I thoroughly recommend) or you could start with something a little simpler.

I am hoping over the next couple of weeks to put together a set of blog posts discussing some tips on how to do COM Interop correctly – particularly in terms of cleaning up after yourself.

As part of this I will be starting with a block of code that isn’t doing what I want (it is based on some real production code). You can copy and paste the code into a new VB.NET project. The question I will be looking at over the coming blog posts is:

What code changes are required to (reliably) make sure Excel is not still running when the method finishes (without calls to GC.Collect)?

Make sure you add a reference to Excel in your project…

Option Strict Off
Option Explicit On
Imports msE = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices  
Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim wb As msE.Workbook
        Dim ws As msE.Worksheet
        Dim xlApp As msE.Application
        '==========================  
        xlApp = New msE.Application
        xlApp.DisplayAlerts = False
        xlApp.Interactive = False
        xlApp.Workbooks.Add()
        wb = xlApp.Workbooks(1)
        'Clear all but the first worksheet  
        For Each wrk As msE.Worksheet In wb.Worksheets
            If wrk.Index > 1 Then wrk.Delete()
        Next
        ws = wb.Sheets(1)
        xlApp.Visible = True
        Threading.Thread.Sleep(5000)    'So we can see Excel  
        wb.Close(False)
        xlApp.Quit()
        Marshal.ReleaseComObject(ws)
        Marshal.ReleaseComObject(wb)
        Marshal.ReleaseComObject(xlApp)
        'Your objective is for the EXCEL.EXE process to not be  
        'running by the time you get here...  
        'No use of GC.Collect allowed  
    End Sub
End Class

 

Feel free to post comments (or email me) with solutions, questions, comments etc…