COM - .NET Interoperability in Visual Basic

Calling an Excel COM Object from VB.NET

COM, the Component Object Model, was the leading technology in the Microsoft world for over fifteen years. It's the core of Visual Basic 6.0 and VBA (Visual Basic for Applications) and it's still in leading edge products like Expression and Sharepoint. So it's not going to go away very soon.

But COM is not a native technology in .NET. As Shakespeare said, "Aye, there's the rub!"

There are a lot of reasons why you will want to combine the capabilities of .NET and COM.

This article will cover one side of the problem, using COM objects in VB.NET. The other side, using .NET objects in a COM based architecture, is significantly more difficult and will be in a later article.

(Note: The code in this article was tested on Framework 3.5 with VB.NET Express 2008 and Excel 2007. Earlier versions can require substantially different code due to changes in the way Microsoft handles COM interop. The code in this example is deliberately as short and simple as possible to make it as easy as possible to understand and use.)

Although there are a whole raft of reasons why COM might be used in .NET, the most common one is to use Microsoft Office objects in your VB.NET program. So that's the one we'll cover in detail. In particular, we're going to create an Excel 2007 spreadsheet in VB.NET, update it from VB.NET, and save it again.

--------
Click Here to display the illustration
Click the Back button on your browser to return
--------

The same basic techniques would be used on any COM objects that you might need to use in your VB.NET program. The main things that will change are the names of the actual COM objects and the libraries they're in; and the methods and properties of those objects of course.

Referencing the COM Library - Step Number One

Using an a programming object is all about communication. If my .NET program sends a piece of data to a COM object, the COM object must receive the same data that the .NET program sends. The problem comes in because the two technologies have different ways of defining fundamental data types.

To solve this problem, Microsoft invented what is called an interop assembly. When you compile your program, the result is an assembly. It's what we used to call an "executable". This is a special kind of assembly that's designed to allow .NET and COM to work together. (For more on .NET assemblies, you might try my article Assemblies in .NET.)

There are several ways that an interop assembly can be created, but the method you will probably use is to simply add a COM Reference to your VB.NET program. It's the same procedure you would use to add any other Reference, but you use the COM tab.

--------
Click Here to display the illustration
Click the Back button on your browser to return
--------

Visual Studio (or VB.NET Express) will create an interop assembly for you 'behind the scenes'. To test this, I added a reference to the COM object library from a non-Microsoft product, Acrobat Access Library, and the file Interop.AcrobatAccessLib.dll was added to the Obj folder for my project.

But utility software can only do so much to resolve all of the differences between COM and .NET. Some elements of COM objects simply should not be used in .NET and others can be made more efficient with some manual code tweaks. So Microsoft invented another kind of interop assembly called the Primary Interop Assembly or PIA. This is the "official" interop assembly and is installed automatically in the Global Assembly Cache (GAC) when some software is installed on your computer - most notably, Office. (To learn about the GAC, you might want to try my article, GACUTIL - Sharing your work in the Global Assembly Cache.)

--------
Click Here to display the illustration
Click the Back button on your browser to return
--------

When you add Office references to your project, Visual Studio .NET will check the GAC to see if an interop assembly is there.

If it is, that assembly will be used instead of building one. In the case of Office, these are PIA interop assemblies. This technique isn't just for Microsoft. Anybody could build a .NET PIA for their object library. But for the most part, only Microsoft actually does it.

Calling the COM Object - Step Number Two

After establishing the reference to the interop assembly, you can instantiate the COM object just like any .NET object. In the case of Excel, simply instantiate a new Application object.

Dim myExcelApp = New Excel.Application()

There are probably thousands of variations of the Workbooks.Add versus the Workbooks.Open methods. If you search for other sources, you'll find some procedures that are dozens of lines of code long. This can be necessary for previous versions and for special situations. Open, for example, has a possible 15 parameters to control various things like passwords and whether the spreadsheet is read only or not. For production code, it's a good idea to wrap this code in a Try-Catch block and do some validation checking to ensure, for example, that your workbooks and worksheets really exist.

The shortest code example I'm aware of looks like this:

Dim myExcelWorkBook = myExcelApp.Workbooks.Open( _
   "C:\ABExample.xlsx")
Dim myExcelSheet = _
   myExcelWorkBook.Worksheets("ABWorkSheet")

This gives you a worksheet object that you can use to update your spreadsheet.

myExcelSheet.Range("C5").Value += 54321.0

Now that the workbook is updated, all we need to do is save it and quit. That's pretty easy, but there are some things to look out for there too. That's explained on the next page, along with a relisting of all the source code.

The minimum last step is to save your workbook and then quit.

myExcelWorkBook.Save()
myExcelApp.Quit()

It's important to quit Excel before the program ends. If you don't, an excel.exe process will continue to run on your computer until it's ended or your computer is rebooted, whichever comes first. When you're debugging, this can become a problem because you will probably terminate your program before ending the excel.exe process.

In that case, you can end it in Task Manager.

--------
Click Here to display the illustration
Click the Back button on your browser to return
--------

It should be noted that, while this VB.NET program will run fine, it doesn't clean up COM at all. One of the big advantages of .NET is that memory is reclaimed using "garbage collection". So when you Quit your program, .NET will figure out that the memory isn't needed anymore. COM, by contrast, relies on counts that are incremented and decremented as objects are instantiated and released again. COM deletes an object when the count reaches zero. In some cases (such as non-terminating server processes), you may have to use a ReleaseComObject method to decrement the reference count of a runtime callable wrapper (RCW) to keep things straight.

Source Code

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
   Private Sub Button1_Click(...
      Dim myExcelApp = New Excel.Application()
      Dim myExcelWorkBook = myExcelApp.Workbooks.Open( _
         "C:\ABExample.xlsx")
      Dim myExcelSheet = _
         myExcelWorkBook.Worksheets("ABWorkSheet")
      myExcelSheet.Range("C5").Value += 54321.0
      myExcelWorkBook.Save()
      myExcelApp.Quit()
   End Sub
End Class