This post is the third in my series on getting your head around COM Interop. My aim here is not to teach you all the innards of COM Interop, but instead to communicate a few key principles. These principles will (hopefully) make your life a little simpler when coding in .NET against COM components (particularly in relation to getting the COM objects to clean themselves up).
COM Interop Principle #2: Fear the Period
Last week’s post was about what not to do when dealing with COM Interop.
So, what should you do instead? Well, first and foremost you must Fear the Period.
The most powerful weapon in your arsenal when correctly doing COM Interop is Marshal.ReleaseComObject. You can see some examples of its use in the previous two posts in this series. Unfortunately, ReleaseComObject has a natural enemy – the period (or full stop).
So, what does Marshal.ReleaseComObject do? Well, it decrements the reference count of the RCW that you pass to it. Or, in English, it releases the underlying COM object (*).
So, what does the period have to do with anything? Well, lets look at some of the code from the original post…
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 xlApp As msE.Application '========================== xlApp = New msE.Application xlApp.DisplayAlerts = False xlApp.Interactive = False xlApp.Workbooks.Add() <---- Focus your attention on this line <snip> Marshal.ReleaseComObject(xlApp) End Sub End Class
Looking at the code we can see that xlApp is being cleaned up correctly at the end of the code. But have a look at:
xlApp.Workbooks.Add() <---- Focus your attention on this line
Let’s have a think about what that code is doing. We are calling a property on the xlApp object which is returning a ‘temporary’ Workbooks object. Then we are calling the Add method (**) on that ‘temporary’ Workbooks object.
But hold on, where are we cleaning up that ‘temporary’ Workbooks object? (Hint: We aren’t!)
This is why we must Fear the Period. To get the above code to work you must introduce an explicit Workbooks variable – this then allows you to explicitly clean it up. So the code would become:
Dim wbs As msE.Workbooks wbs = xlApp.Workbooks wbs.Add() Marshal.ReleaseComObject(wbs)
So, how do you Fear the Period in practice?
- Do not ever allow two periods to be consecutive (as in the above example of xlApp.Workbooks.Add).
- Do not ever allow any periods to appear in the ‘In’ section of a For Each declaration (as in For Each wrk As msE.Worksheet In wb.Worksheets).
So, lets incorporate this principle into the original code:
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 Dim wbs As msE.Workbooks wbs = xlApp.Workbooks wbs.Add() Marshal.ReleaseComObject(wbs) wb = xlApp.Workbooks(1) Dim wss As msE.Sheets wss = wb.Worksheets 'Clear all but the first worksheet For Each wrk As msE.Worksheet In wss If wrk.Index > 1 Then wrk.Delete() Next Marshal.ReleaseComObject(wss) 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) End Sub End Class
So, did that fix the issue?
Unfortunately not. While Principle #2 is definitely the most important principle when dealing with COM Interop, there are still a couple of gotchas in the original sample that we have yet to address…
COM Interop Principle #2: Fear the Period
Coming up next – COM Interop Principle #3: Fear the (Hidden) Period.
* This is an over-simplification, and there a number of finer points that I am purposely ignoring – to keep the discussion simple.
** Let’s ignore what the Add method actually does for now – it isn’t relevant to this post.