The Code Net

Automating Office Programs with VB.Net / COM Interop

written by Mike Rosenblum - Last updated Sep 2005

Introduction

This tutorial is designed to cover the basics required to control MS Office programs from a VB.Net application. The examples utilized here involve the Automation of Microsoft Excel, but the principles would be the same for Automating any of the MS Office programs such as Access, Word or Outlook.

Do be aware that execution speed will be similar to, or slightly slower than, it was under VB6 Automation, which is considerably slower than VBA executes natively. Another issue to consider is Deployment; VB.Net can be tricky to implement for MS Office versions below the current version of 11.0. (Execution speed and deployment issues are discussed at the end of the tutorial.)

Ok, let's get started!

Adding The Project Reference

The first step in Automating a Microsoft Office Program with VB.Net is to have the required Project Library referenced by your VB.Net application.

Here we will be Automating Excel, so one would need to add a reference to the Microsoft Excel Object Library. The procedure to do this is almost the same as it was in legacy VB 6.0:

(1) Go to Alt | Project | Add Reference... What is different at this point is that you must now click on the COM Tab at the top, for Excel is a legacy COM Project. Now scroll down until you find the "Microsoft Excel 11.0 Object Library" (which could be 10.0, 9.0 or 8.0, depending on your system).

(2) Next, click the <Select> button and then click <OK>. The Reference to the Excel Object Library is now loaded.

Basic Shell

The next step is to write your code. The basic shell for Automating Excel, for example, could look something like this:

Sub MySub() 
    Dim oApp As New Excel.Application 
    Dim oWB As Excel.Workbook = oApp.Workbooks.Add 
    Dim oWS As Excel.Worksheet = CType(oWB.Worksheets(1), Excel.Worksheet) 
     
    ' Your Code Goes Here 
    ' Your Code Goes Here 
    ' Your Code Goes Here 
    ' Your Code Goes Here 
     
Cleanup: 
    oWB.Close(SaveChanges:=FALSE) 
    oApp.Quit() 
     
    oApp = Nothing 
    oWB = Nothing 
    oWS = Nothing 
     
    GC.Collect()  ' <-- *** Important! *** 
End Sub

The code above is the basic "shell", if you will, for creating and then safely disposing of your Excel Application object. What one might place in the "Your Code Goes Here" block is up to the reader, but is also discussed in the "Your Code Goes Here" section, later on.

Cleanup

Cleanup, as it pertains to .Net Automation, is a tricky business. The difficulty derives from the fact that .Net is operating through the COM Interop, which is an interconnection between two technically incompatible worlds: (1) Legacy COM and (2) the .Net Framework.

There are many complex techniques for releasing a COM Object in .Net, but the above example is showing what we believe is the cleanest arrangement (with special thanks to XL-Dennis for coming up with the initial formulation). For a discussion of Microsoft's recommended approach, which makes use of Marshal.ReleaseCOMObject(), please see MSKB 317109, but the result of MSFT's technique is some very cumbersome code. Also, not mentioned in that article is that Marshal.ReleaseCOMObject() will not release variables declared 'WithEvents', in which case GC.Collect() would be absolutely required.

In many ways, the above Cleanup does not look more onerous than we are typically used to, other than the addition of GC.Collect(), which forces .Net to dispose of all unused Object References immediately.

But do not let this apparent simplicity fool you. If GC.Collect() is omitted or if any of the Object Variables are not set = Nothing, then the Excel Application instance will hang. This is not necessarily fatal, but if a routine that is Automating Excel is accessed repeatedly and is not properly shutting down its references, then multiple versions of Excel will be present in the Task Manager and memory resources of the computer will be wasted.

Note that when not using the COM Interop, one can typically be more casual about setting one's variables equal to Nothing. Neglecting to set an Object Reference to Nothing is usually not a big problem, for the variable would go out of scope on its own when the Sub ends.

However, when using Automation through the COM Interop, GC.Collect() must be called only after all of the object variables are set = Nothing. GC.Collect() will not release any COM objects that are still being referenced. The fact that the referencing object variables become Nothing when the Sub terminates is too late because GC.Collect() is called before the routine ends.

The conclusion is that one must be absolutely dedicated about setting one's COM Object references = Nothing and then finish with GC.Collect().

Do be aware that a potential negative to this technique is that calling CG.Collect() repeatedly within small, frequently called routines could result in a drag on performance. But again, the alternative, using MSFT's use of Marshal.ReleaseCOMObject(), is very cumbersome to use. See MSKB 317109 for a discussion.

CType() and Option Strict On

Note that under 'Option Strict On', one may have to make use of the CType() function to cast an Object Reference to the precise Object Type required. Note the 3rd line in the code above, which reads:

Dim oWS As Excel.Worksheet = CType(oWB.Worksheets(1), Excel.Worksheet)

This is declaring the oWS variable to be an Excel.Worksheet reference and then setting it to reference the 1st Worksheet in oWB. If 'Option Strict' is not being used, then the use of CType() is not necessary, but 'Option Strict' is very highly recommended, so an occasional use of CType() will be required.

(One would think that the Worksheets Collection could only return a Worksheet Object, but old-school "Macro Sheets" dating back to Excel 5.0 are part of the Worksheets Collection and, technically, make the Worksheets Collection polymorphic.)

It can be hard to know ahead of time where and when one might need to use CType(), but it tends not to be very frequent. Fortunately, the compiler will tell you if you need to add it, so there is no need to use it everywhere and anywhere, for your code would become awkward to read.

"Your Code Goes Here"

In the example above, the block that reads 'Your Code Goes Here' is where you would execute your commands. Typically one is reading and writing values into a spreadsheet, so a very simple example might look something like the following:

Dim oRng1, oRng2 As Excel.Range 
 
oRng1 = oWS.Range("A1") 
oRng1.Value = "Hello World" 
 
oRng2 = oWS.Range("B2:E5") 
oRng1.Copy(Destination:=oRng2) 
 
oWB.SaveAs("C:My DocumentsMyWorkbook.xls") 
 
oRng1 = Nothing   ' <-- Don't forget! 
oRng2 = Nothing   ' <-- Or Excel will hang...

The code above will place the words "Hello World" in Cell A1 and then copy it to the Range B2:E5. It then saves the workbook as "MyWorkbook.xls" in the "My Documents" folder. Lastly, it makes certain to set oRng1 and oRng2 both equal to Nothing. (GC.Collect() is called only once, at the very end of the routine.)

Note that the above is also careful to call '.Value' explicitly when needing to get or change the value that is held in a Range. In VBA, or even in VB6 Automation, this would not be required, for the default property of the Range object is in fact the .Value property. However, .Net does not recognize any default property that does not take a parameter.

Contrast this with the Worksheets collection, which does take a parameter for its default .Item() property:

oRng.Value = "Hello"                ' Legal 
oRng = "Hello"                      ' Compile-Time error! 
 
oWS = oWB.Worksheets.Item("Sheet1") ' Legal 
oWS = oWB.Worksheets("Sheet1")      ' Also Legal!

The 2nd line in the above would generate a compile-time error, because one cannot set a Range = "Hello". This is because "Hello" is a String (not a Range). The correct approach is to use oRng.Value = "Hello".

The last line calling oWS = oWB.Worksheets("Sheet1") is valid because the default property of the Worksheets Collection is the .Item() property, which takes a parameter (while Range.Value does not).

Again, if there is no parameter provided (as with Range.Value) then the property must be explicitly stated, even if it is, technically, the "default". But since a Worksheets Collection's .Item() property does take a parameter, it can be utilized as the default.

Execution Speed

Execution speed is rarely an issue for MS Office programs like Word, Outlook or PowerPoint, but can be for Excel or Access, which often are doing numerous calculations before posting results.

If one does care about execution speed, then be aware that Automation via the COM Interop can be slow. As with VB 6.0 Automation, your .Net Application will be running out-of-process with respect to the Excel Application. That is, your code and the Excel program are not sharing the same address space. This slows down execution speed with respect to native VBA by a factor of about 50x or more. In addition, the COM Interop adds overhead, so your Automation code may run a bit slower than it did with VB 6.0 Automation.

With VB 6.0, execution speed can be addressed by compiling to a COM DLL, which runs within the MS Office Application - not your VB App. This allows one's code to run "in process" and therefore run at native VBA's speed.

VB.Net cannot take advantage of this technique. VB.Net does allow one to compile to what is called a Managed COM "DLL", but it is not a true DLL. It is still within the .Net Framework - using JIT Compilation - and the COM Interop is cleverly designed to have your MS Office Application fooled into thinking that your code is a true DLL. While this approach by Microsoft is impressive, there is a considerable degradation in execution speed relative to native VBA and your code will exhibit speeds more typically experienced with out-of-process Automation.

The overall conclusion is that if Automating MS Office programs from a VB.Net Application, then your execution speeds should be similar to or perhaps slightly slower than was your speed under VB 6.0 Automation. But if the goal is to create a DLL to be run within Excel - and if execution speed is a concern - then one may wish to consider developing in VB 6.0 or even directly within VBA.

Deployment Issues

Another issue to consider is Deployment. VB.Net can be tricky to implement for MS Office versions below the current version of 11.0.

Automation of Office 10.0 can be accommodated by downloading the proper Primary Interop Assemblies (PIAs) from MSFT, but installation can be difficult. See Microsoft Office XP PIAs Are Available for Download, but its not always as smooth to install as that article may suggest. Another link that can be helpful is Office XP Primary Interop Assemblies Known Issues.

For versions 9.0 and below, there does not seem to be any documentation support at all, but importing the Namespace with

Imports Microsoft.Core 
Imports Microsoft.Office.Interop

appears to be required for the Release Build even if not necessarily required for running within the VBIDE.

And of course, the target computer must also have the .Net Framework installed. See How to Get the .NET Framework 1.1 for more info.

To sum up, it may be wise to create a very small .Net App using Automation and then test that on the platform(s) that will be required before jumping into a large project. It is also important to test a Release Build and to not just test within the VBIDE, for the Automation may run within the VBIDE, but still not operate as a Resease Build / stand-alone App.

Closing

While installation and deployment caveats abound, and there are some cleanup issues to consider, Automation in VB.Net is not considerably different than it was under Legacy VB 6.0.

And while the examples in this tutorial utilize the Excel Object Model, the concepts are 100% parallel when Automating any MS Office program such as Word, Access or Outlook. Even if the Methods and Properties within those object models are different, the structure for creating your Application instance, referencing it, and cleaning up is the same.

But do consider your needs with respect to execution speed and deployment issues before undergoing a large VB.Net project that is to Automate an MS Office program. Since MS Office still operates under Legacy COM, it is often more efficient and easier to utilize Legacy VB 6.0 and/or VBA.

Good luck out there...!,/p>

, Mike

Special Thanks

To XL-Dennis for coming up with the CG.Collect() technique and for his extensive help and collaboration on this tutorial. Thanks Dennis.

Name:
Password:
Remember me?
Register

Login