This post is the fourth 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 #3: Fear the (Hidden) Period
Last week’s post covered the key principle of COM Interop – Fear the Period (aka Full Stop).
As a result, we had the following 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
In the above code, we have diligently followed the two key ways that we learnt to Fear the Period:
- 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).
Or have we?
The Hidden Period
While not evident at first glance, it actually turns out that in the above code there are hidden periods.
Huh? What are you talking about?
Well, lets take a look at one of the lines of code…
ws = wb.Sheets(1)
The code is simple enough. We want to get the first worksheet of the workbook – so we are calling the Sheets function on the wb (Workbook) object, passing 1.
Or are we?
Whoops, it seems that Sheets isn’t a function – it is actually a Default Property. This means that:
ws = wb.Sheets(1)
is just shorthand for:
ws = wb.Sheets.Item(1)
See – there it is – our hidden period.
So, lets’s add a third practical way to Fear the Period (hidden or otherwise):
- 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).
- Do not ever call a Default Property.
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()
wb = wbs.Item(1)
Marshal.ReleaseComObject(wbs)
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
ws = wss.Item(1)
Marshal.ReleaseComObject(wss)
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. I know the tension must be killing you, but there is still one more gotcha to address…
So:
COM Interop Principle #3: Fear the (Hidden) Period
Coming up next – COM Interop Principle #4: Watch the Loops.
