Microsoft Office and .NET Interoperability
November 1, 2001
By now, you have most likely heard about the new features that the Microsoft® .NET platform brings to solutions development. I am sure you are wondering how you can take advantage of these .NET features with your Microsoft Office solutions. In this month's column, I introduce you to how Office and .NET work together from a code perspective.
If you are new to .NET, please see last month's column, Introducing .NET to Office Developers, for more information on .NET and its relationship to Office.
How Do Office and .NET Interoperate?
Microsoft Office Visual Basic® for Applications (VBA, based on the Component Object Model (COM)) code and .NET cannot natively communicate with each other. However, a .NET feature called COM interop provides callable wrappers to allow .NET and COM to interoperate, as shown in Figure 1.
Figure 1. COM and .NET interoperability using callable wrappers
A runtime callable wrapper (RCW) allows a COM component (for example, an Office VBA object library) to be used by .NET (for example, a Visual Basic .NET application). In Microsoft Visual Studio® .NET, for example, when you click Add Reference from the Project menu, click the COM tab, double-click a type library file, and click OK, Visual Studio .NET converts the objects and members in the COM type library file into equivalent .NET assemblies (see Figure 2). After the .NET assemblies are generated, you should be able to instantiate classes and call members from Visual Studio .NET as if the COM objects and members were native .NET classes and members.
Figure 2. Adding a reference to a COM object library from Visual Studio .NET
A COM callable wrapper (CCW) allows a .NET assembly to be used by COM (for example, Office VBA). The Assembly Registration Tool (RegAsm.exe), a .NET Framework SDK tool, reads the metadata in a .NET assembly (equivalent to a COM dynamic-link library (DLL) or executable file (EXE)) and makes registry entries that allow COM to call the classes and members in the .NET assembly. After running RegAsm.exe, you should be able to set a reference to the generated COM type library file through the References command on the Tools menu in the Office Visual Basic Editor, and create COM objects and call members as usual.
To run RegAsm.exe, from the Microsoft Visual Studio Command Prompt (on the Start menu, point to Programs, point to Microsoft Visual Studio.NET 7.0, point to Visual Studio.NET tools, and click Visual Studio.NET Command Prompt), type the following:
regasm AssemblyPath /tlb:FileName.tlb
Where AssemblyPath is the path to the target assembly, and FileName is the name you want to give your type library. For example:
regasm "C:\WINNT\Microsoft.NET\Framework\v1.0.2914\mscorlib.dll" /tlb:"C:\MyOfficeProjects\MyOfficeProject1\mscorlib.tlb"
This inserts information about the mscorlib.dll .NET assembly into the registry, and creates the mscorlib.tlb type library file so that it can be accessed from the References dialog box (Tools menu) in the Office Visual Basic Editor.
You will need to distribute the .NET assembly, and an Office solution based on that assembly, to your users. See Packaging and Deploying .NET Solutions below for more information.
An ActiveX callable wrapper allows .NET to use a Microsoft ActiveX® control (such as an Office Web Component) to be used by .NET. When you right-click the Visual Studio .NET Toolbox, click Customize Toolbox, select an entry on the COM Components tab, and click OK (as shown in Figure 3). Visual Studio then generates the code to use the ActiveX control from the Toolbox just like any other .NET control.
Figure 3. Adding an ActiveX control to the Toolbox in Visual Studio .NET
Calling the .NET Framework Class Libraries from Office VBA
Although Office VBA can make some calls into the functionality provided by the .NET Framework class libraries, there are currently some serious limitations with this approach because COM (on which Office VBA is based) does not recognize the following in the .NET Framework class libraries:
- Parameterized constructors: Any class in the .NET Framework class library with a constructor that takes one or more parameters will generate a run-time error if any instance of that class is set equal to New in COM (even though these classes are visible in the Office Visual Basic Object Browser). For example, we could never code something like
Dim objWS As New Excel.Worksheet(Caption:="Sheet1")in Office VBA because the code
(Caption:="Sheet1")does not follow COM coding syntax.
- Non-public and static (shared) members: Any .NET Framework class library properties, methods, or events marked as Private, Protected, or Static (Shared) are not accessible to COM and will not appear in the Office Visual Basic Object Browser. Even if a .NET Framework class library member is marked as Public Instance, it will not appear in the Office Visual Basic Object Browser if there are any parameterized constructors for the member's class.
If you study the .NET Framework class library documentation, you will notice very few COM-callable classes and members. This is because many of the .NET Framework class library classes take parameterized constructors, and those that do not have parameterized constructors have very few public instance members.
Because of these limitations, I will move on to a more interesting and useful option, which is calling the Office VBA object libraries and ActiveX controls from .NET.
Calling the Office VBA Object Libraries from Visual Basic .NET
A new and exciting opportunity in Office solutions development is to integrate the functionality of the Office VBA object libraries from .NET applications. I will show you how to do this by using Microsoft Visual Basic .NET, a member of Visual Studio .NET and a revolution to the Visual Basic language. Visual Basic .NET is somewhat similar to Office VBA, but there are several key differences that I will explain after I present the code.
Calling the Outlook Object Library Using a Console Application
Console applications are useful for solutions that do not need the overhead of a complex user interface. Let's demonstrate this by creating a new console application in Visual Studio .NET that reports on some Microsoft Outlook® data:
- On the File menu, point to New, and click Project.
- In the Project Types pane, click Visual Basic Projects.
- In the Templates pane, click Console Application.
- In the Name box, type olquery.
- In the Location box, type a location that is easy for you to remember, and click OK.
- On the Project menu, click Add Reference.
- On the COM tab, click Microsoft Outlook 10.0 Object Library (assuming you have Microsoft Outlook 2002 installed on your computer), click Select, and click OK.
- When the message "...Would you like to have a wrapper generated for you?" appears, click Yes. Because the Outlook object library references additional object libraries, Visual Studio .NET will create CCW assemblies for these object libraries as well. So, in the \bin folder in your application's solution folder using Microsoft Windows® Explorer, you should see two CCW assemblies—Interop.Outlook.dll (representing the Outlook object library), and Interop.Office.dll (representing the Microsoft Office 10.0 Object Library). You can now call the functionality of these libraries just as you would for any .NET class libraries.
- Edit the code in the Module1.vb file to look like the following code:
Imports Outlook.OlDefaultFolders Imports Microsoft.VisualBasic.ControlChars Module Module1 Sub Main() Dim objApp As Outlook.Application Dim objNameSpace As Outlook.NameSpace Dim objMAPIFolder As Outlook.MAPIFolder Dim objSubFolder As Outlook.MAPIFolder Dim strFolder As String Dim strAgain As String Dim intFolderEnum As Integer Dim lngItemCount As Long Dim lngAppointmentCount As Long Dim lngContactCount As Long Dim lngDistListCount As Long Dim lngJournalCount As Long Dim lngMailCount As Long Dim lngNoteCount As Long Dim lngPostCount As Long Dim lngTaskCount As Long Dim lngOtherCount As Long objApp = New Outlook.Application() objNameSpace = objApp.GetNamespace(Type:="MAPI") Do ' Reset counters from previous loops. lngItemCount = 0 lngAppointmentCount = 0 lngContactCount = 0 lngDistListCount = 0 lngJournalCount = 0 lngMailCount = 0 lngNoteCount = 0 lngPostCount = 0 lngTaskCount = 0 lngOtherCount = 0 Console.Write("Outlook folder name (case-sensitive): ") strFolder = Console.ReadLine() ' Map user's entry to an OlDefaultFolders constant. Select Case strFolder Case "Deleted Items" intFolderEnum = 3 Case "Outbox" intFolderEnum = 4 Case "Sent Mail" intFolderEnum = 5 Case "Inbox" intFolderEnum = 6 Case "Calendar" intFolderEnum = 9 Case "Contacts" intFolderEnum = 10 Case "Journal" intFolderEnum = 11 Case "Notes" intFolderEnum = 12 Case "Tasks" intFolderEnum = 13 Case "Drafts" intFolderEnum = 16 Case Else Console.Write("'" & strFolder & "'" & _ "is not a valid folder. " & _ "Valid folders are Calendar, " & _ "Contacts, Deleted Items, " & _ "Drafts, Inbox, Journal, Notes, " & _ "Outbox, Sent Mail, " & _ "and Tasks. Press any key to exit...") Console.Read() Exit Sub End Select objMAPIFolder = _ objNameSpace.GetDefaultFolder(FolderType:=intFolderEnum) Console.Write("Calculating folder contents..." & CrLf) ' Examine each item in the folder. Increment ' appropriate counter and continue. For lngItemCount = 1 To objMAPIFolder.Items.Count Select Case _ TypeName(VarName:=objMAPIFolder.Items.Item(lngItemCount)) Case "AppointmentItem" lngAppointmentCount = lngAppointmentCount + 1 Case "ContactItem" lngContactCount = lngContactCount + 1 Case "DistributionListItem" lngDistListCount = lngDistListCount + 1 Case "JournalItem" lngJournalCount = lngJournalCount + 1 Case "MailItem" lngMailCount = lngMailCount + 1 Case "NoteItem" lngNoteCount = lngNoteCount + 1 Case "PostItem" lngPostCount = lngPostCount + 1 Case "TaskItem" lngTaskCount = lngTaskCount + 1 Case Else lngOtherCount = lngOtherCount + 1 End Select Next lngItemCount ' Report results of tally. Console.Write("Total items in this folder: " & _ objMAPIFolder.Items.Count & CrLf & _ "Appointments: " & lngAppointmentCount & CrLf & _ "Contacts: " & lngContactCount & CrLf & _ "Distribution lists: " & lngDistListCount & CrLf & _ "Journal entries : " & lngJournalCount & CrLf & _ "E-Mail messages: " & lngMailCount & CrLf & _ "Notes: " & lngNoteCount & CrLf & _ "Posts: " & lngPostCount & CrLf & _ "Tasks: " & lngTaskCount & CrLf & _ "Other items: " & lngOtherCount & CrLf) Console.Write("Try again? (y/n): ") strAgain = Console.ReadLine().ToString Loop While strAgain = "y" End Sub End Module
- To run this code sample, click Run (or press F5) on the Debug menu. Figure 4 shows the finished console application.
Figure 4. The olquery.exe console application
Here is how the code works:
- The Imports statement is shorthand for writing out a fully qualified reference to a .NET namespace. Just as in Office VBA, we code references like
Office.Outlook.Applicationto distinguish the use of the Application object, in Visual Basic .NET, we can code
Imports Outlook.OlDefaultFoldersto keep us from having to type
Outlook.OlDefaultFolders.olFolderInboxlater, using just
- The Module...End Module statement defines an instance of a module—in this case, Module1. The interesting thing about Visual Basic .NET is that you can cram an entire console application into one .vb file, including class modules, code modules, and so on. The Module...End Module statement isolates the code for the class from other code components in the file.
- Sub Main is the main subroutine for this application. In a console application, program execution must start beginning with the Main subroutine.
- The Console.Write method is similar to the Debug.Print method in Office VBA in that it prints information—in this case, to the command prompt. The Console.Read method is similar to the Office VBA InputBox function in that it can get information from users at the command prompt.
- The rest of the code is very straightforward for Outlook developers. One item to note is that the MAPIFolder object's Items property returns an Items object, not an Items collection (there is no Items collection in Outlook). Therefore, because the Items object does not support the .NET IEnumerable interface to be treated like a collection, we must iterate through the Outlook items by index number, not by using the familiar
For Each objItem In objMAPIFolder.Items.
- In the interest of space, I am not using any error-handling code in this solution. See the next section for details on how to create error-handling code.
Although this is a simple example, it is easy to see how console applications can add power and flexibility to Office solutions. For instance, you could create an Office automation application that runs on a recurring schedule through the Scheduled Tasks program in Control Panel. You can also write cross-application Office macros, applications that do not require a sophisticated user interface, and so on.
Calling the Word Object Library Using a Windows Forms Application
Now let me introduce you to Windows Forms in Visual Studio .NET, which are similar to VBA UserForms. Here is how to create a new Windows Forms application in Visual Studio .NET that reports on some document properties in Microsoft Word:
- On the File menu, point to New, and click Project.
- In the Project Types pane, click Visual Basic Projects.
- In the Templates pane, click Windows Application.
- Fill in the Name and Location boxes, and click OK.
- On the Project menu, click Add Reference.
- On the COM tab, click Microsoft Word 10.0 Object Library (assuming you have Word 2002 installed on your computer), click Select, and click OK.
- When the message "...Would you like to have a wrapper generated for you?" appears, click Yes.
- On the View menu, click Toolbox. On the Windows Forms tab, double-click the Button control and the OpenFileDialog control to add a button and Open file dialog box to Form1.
- Right-click the OpenFileDialog1 icon below Form1 and click Properties.
- In the Filter box, type Microsoft Word Documents (*.doc)|*.doc.
- In the Title box, type Select Word Document.
- Double-click Button1. The Code window for the form appears.
- Edit the code in the Code window to match the code listed below.
- To run this code sample, on the Debug menu, click Run.
- Click Button1 on Form1 and select a Word document from your local file system (to see the Output window in Visual Studio .NET, press CTRL+ALT+O). Figure 5 shows what the finished application looks like.
Figure 5. The Windows Forms application at work
Here is the code that opens a selected Word document and reports on some of its document properties:
Imports Microsoft.VisualBasic.ControlChars Imports System.Runtime.InteropServices Imports Word.WdBuiltInProperty Public Class Form1 Inherits System.Windows.Forms.Form ' Windows Form Designer generated code Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim strFileName As String Dim wdApp As New Word.Application() Dim objDoc As Word.Document If OpenFileDialog1.ShowDialog() = DialogResult.OK Then Try strFileName = OpenFileDialog1.FileName objDoc = wdApp.Documents.Open(FileName:=strFileName) With objDoc ' Print information in a message box. MessageBox.Show(Text:="Title: " & _ .BuiltInDocumentProperties(wdPropertyTitle).value & _ CrLf & "Author: " & _ .BuiltInDocumentProperties(wdPropertyAuthor).value & _ CrLf & _ .BuiltInDocumentProperties(wdPropertyPages).value & _ " pages.") ' Print information to the Output window. Console.Write(Format:="Contents of " & _ strFileName & " are:" & CrLf) Console.Write(Format:=.Content.Text) End With Catch ex As COMException ' "File not found" error. MessageBox.Show(Text:="Can't find the file '" & _ strFileName & "'. Please try again.") End Try End If End Sub End Class
Here is how the code works:
- The Imports statement allows us to code
Word.wdBuiltInPropertyto keep us from having to type
Word.wdBuiltInProperty.wdPropertyAuthorlater, using just
- The Class...End Class statement defines an instance of a class—in this case, a Windows Form. The Class...End Class statement isolates the code for the class from other code components in the file.
- The Inherits statement indicates that Form1 inherits, or takes on the behaviors and characteristics of, a generic Windows Form. This would include things like the form's caption at the top, the icon in the upper-left hand corner, minimize/restore/close buttons in the upper-right hand corner, and so on.
- The Windows Form Designer-generated code in this example does not need to be viewed or modified.
- The Sub Click...End Sub statement is familiar to VBA programmers as an event. However, this event is known in .NET terminology as a delegate, because it is delegated to handle the Button1.Click method. When Button1 on Form1 is clicked, the code in this event runs.
- The Try...Catch statement is a new version of the familiar VBA On Error GoTo statement (you can still use On Error GoTo if you want to, but you should begin using the .NET equivalent). To use Try...Catch, you surround any code that might generate errors in the Try block, and you handle any errors in the Catch block. You can have multiple Catch blocks, which are similar to the Case statements in a Select Case Err.Number construct in Office VBA. Additionally, you can add a Finally block to perform any clean-up work (similar to a FunctionName_End convention in standard Office VBA error handling code).
- The Dim statements introduce the concept of initializers. In Visual Basic .NET, you can declare and initialize variables all in one line of code. Notice also that you do not use the Set keyword anymore (it is no longer available).
- The MessageBox.Show method replaces the Office VBA MsgBox function (you can still use the MsgBox function if you want to, but you should begin using the .NET equivalent). In this case, the MessageBox.Show method displays the document's title, author, and number of pages. The CrLf enumerated constant replaces the Office VBA vbCrLf enumerated constant (although you can still use the vbCrLf enumerated constant if you want to, but you should begin using the .NET equivalent).
- The Console.Write method replaces the VBA Debug.Print method (the Debug.Print method is no longer available in Visual Basic .NET). In this case, the Console.Write method lists the document's contents in the Output window.
Some changes you will notice in Visual Studio .NET include the following:
- The default development environment behavior is for a general Visual Studio developer. For example, the default shortcut key combination for viewing the Object Browser is CTRL+ALT+J, and the default shortcut key combination for a Step Into action is F11. To associate the F2 key with the Object Browser and the F8 key with a Step Into action, click the Start Page tab, click My Profile, and click Visual Basic Developer in the Profile list.
- There is no Immediate window. The Command window (on the View menu, point to Other Windows and click Command Window) provides an Immediate mode so that you can issue commands as you used to do in the Immediate window (type immed at the > prompt for Immediate mode; type >cmd in Immediate mode to switch back to Command mode). In Command mode, you can send commands directly to Visual Studio .NET instead of using menus. For example, type Help in the Command window to expose the various Help commands. Furthermore, the Output window (on the View menu, point to Other Windows and click Output) is used to print out information as you used to do in the Immediate window using the Debug.Print statement.
- Solution (.sln) files are used instead of Visual Basic Project Workgroup (.vbg) files. A solution may contain one or more project (.proj) files, which are used instead of Visual Basic Project (.vbp) files. All Visual Basic code files are contained within .vb files.
- Some Office objects are converted to interfaces, and some Office events are converted to delegates. This doesn't change your ability to use these objects and events, it's just that you might see them referred to differently within the Visual Studio .NET Object Browser.
Using ActiveX Controls in .NET Solutions
There will be plenty of occasions where you will want to embed Office ActiveX controls on .NET forms. Let's demonstrate this by adding an instance of the Microsoft Outlook View control (assuming you have the control installed on your computer) to our existing Windows application:
- With the Windows application from the previous section open, and the Form Designer visible, on the Toolbox, right-click and click Customize Toolbox.
- On the COM Components tab, select the check box next to Microsoft Outlook View Control, and click OK.
- Click the OVCtl icon in the Toolbox, and, using the mouse, draw an instance of the Outlook View Control on Form1.
- Run the application by clicking Start on the Debug menu.
Note Be aware that to call ActiveX control functionality in .NET, you need to know the control name that Visual Studio .NET generates for you (in our case, AxViewCtl1). You can, of course, rename this from the Properties window in the Form Designer.
To package and deploy a simple client-based solution, you can copy the files in the \bin directory of the solution's application folder to a file share or source media, request that users copy and paste these files to a single local folder on their computer, and run the application's .exe file. For a simple Web application, you provide users with the path to the application's root .aspx file.
However, if you want to perform special setup options, such as installing files into specific folders, registering assemblies in the global assembly cache, placing entries on the user's Programs menu or desktop, registering COM DLLs, and so on, you should create a .NET setup and deployment project in Visual Studio .NET. To do so, follow these steps:
- With the target solution open, on the File menu, point to New, and click Project.
- In the Project Types pane, click Setup and Deployment Projects.
- In the Templates pane, click Setup Wizard.
- Fill in the Name box, select Add to Solution, and click OK.
- Complete the steps in the Setup Wizard. For example, for a simple Windows Forms application, you can leave the defaults, except for Create a setup for a Windows application (step 2 of 5), and Primary output from ProjectName and Content Files from ProjectName (step 3 of 5).
- On the Build menu, click Build SetupProjectName.
- When finished, there should be a file named SetupProjectName.msi in either the \Debug or \Release folder of the setup project's application folder.
- Distribute this single SetupProjectName.msi file to users, who simply open the .msi file on their local computer.
To add files to the user's file system, add registry entries, add file types, change the look and feel of the user's installation experience, or add custom actions or launch conditions during installation, you need to add information to the .msi file and then rebuild the .msi file. With the setup solution open, right-click the setup solution in the Solution Explorer window, point to View, click the appropriate menu commands, and follow the on-screen directions (see Figure 6). Then, on the Build menu, click Rebuild SetupProjectName to create a new SetupProjectName.msi file.
Figure 6. Modifying a Visual Studio .NET setup project to edit the files added to a user's file system on install
Where to Go for More Information
General .NET Topics
Office and .NET Interoperability
- BETA-HOWTO: Automate Microsoft Word to Perform a Mail Merge from Visual Basic .NET (technical support article)
- BETA-HOWTO: Automate Microsoft Excel from Visual Basic .NET (technical support article)
- BETA-HOWTO: Automate Excel From Visual Basic .Net To Fill or Obtain Data In a Range Using Arrays (technical support article)
Visual Studio .NET
Visual Basic .NET
COM and .NET Interoperability
- .NET Interop: Get Ready for Microsoft .NET by Using Wrappers to Interact with COM-based Applications (technical article)
.NET Application Setup and Deployment
- .NET Framework Deployment Basics (technical reference)
- Deployment Scenarios (technical reference)
And, as always, check in regularly at the Office Developer Center for information and technical articles on Office solution development.
Paul Cornell works for the MSDN Online Office Developer Center and the Office developer documentation team. He spends his free time with his wife and 18-month-old daughter.