Last year in March 2003, I got a chance to write an article for PC Quest Magazine. The article was on “Office XP PIA from Winforms”
USING OFFICE XP PIA from WinForms
Learn how you can use hundreds of objects found in OfficeXP, extend and automate them in your Winforms projects.
Writing a new .NET WinForms application? Need a spell checker or a formula is driven grid control? When I said I am sure you thought of MS Word and MS Excel. Won’t it be great if you can reuse the huge functionalities found in MS Office and also control them from your .NET application?. That’s exactly what Office XP Automation and Office XP PIA allow you to do.
Using Office built-in objects has benefits for everyone. As a developer, you don’t need to buy any expensive third-party controls. Your end users are already familiar with Office Interface and there is no relearning for them.
Office COM Interfaces
MS Office for years has provided hundreds of reusable COM (Microsoft Common Object Model) APIs. Developers used these objects to extend or Automate Office in countless ways. But how on earth, can you use these COM objects in your .NET projects?. Clearly .NET compilers don’t understand COM type libraries (like the common TLB files, found in Visual Basic 6.0), in fact, they only understand .NET metadata. Therefore, to make a COM component available in the .NET world, we need to create “metadata” information. Generating metadata manually for all Office XP objects is going to be a daunting task.
That’s why we need to automate this. There are two ways to do this:
- The .NET Framework SDK provides a tool called the type library importer (TLBIMP.EXE). This utility accepts as input a COM type library file and outputs a .DLL file containing .NET Metadata.
- You use the Add Reference option to reference the COM component from Visual Studio.NET.
Regardless of which method you use, they each produce a new assembly with a .DLL extension. This assembly is used by your .NET application to automate Office. It is important to note that the interop assembly (that’s what is this assembly officially called) only contains definitions of COM types and does no modification on the actual binary code of Office. In other words, an interop assembly contains the needed metadata information that allows .NET managed code to call unmanaged code in Microsoft Office. It does this by wrapping the original Microsoft Office COM-based object model APIs.
Primary Interop Assembly
All of this sounds like a lot of work, that’s why Microsoft recently released Office XP Primary Interop Assembly (PIA). The Office XP PIA contains the official description (read as .NET Metadata) for type library files of all Office applications including Access, Excel, PowerPoint, Word and so on.
Is there a difference between interop assembly that you create with TLBIMP and that of PIA? For the most part, they are almost identical except that the PIA is the official interop assembly released by Microsoft and has been customized for ease of use from managed code.
Installing Office XP PIA
You need to download the Office XP PIA from Microsoft MSDN website (also provided in the accompanying CD). After downloading, copy it to a folder and run the register.bat file.
Sample 1: How to Word Spell Checker in your Application?
Let us plunge right into Visual Studio.NET and create a new Windows application that uses MS Words’ built-in Spell Checker. Once you have your project right click on References (below your project name) and Add a reference to the Word PIA DLL (Microsoft.Office.Interop.Word.dll) as shown in the figure below.
Now you can use any of the objects in Word as you would use a built-in .NET object like System.Console, etc. You can see this in the code snippet below:
Dim oMSWord As New Microsoft.Office.Interop.Word.ApplicationClass() Dim oSuggestionsCollection As Microsoft.Office.Interop.Word.SpellingSuggestions Dim oSuggestion As Microsoft.Office.Interop.Word.SpellingSuggestion … oMSWord.Documents.Add() … oSuggestionsCollection = oMSWord.GetSpellingSuggestions(sWord)
This is all, that it is needed to use the complete Word’s Spell Checker from your application.
Excel, PowerPoint & Outlook Samples
What we have seen with Word PIA is only tip of the iceberg. As the full source for this VS.NET solution (found in the accompanying CD) will show, there is lot more you can do with Office XP PIA. The Excel sample shows how you can create and populate a sheet from your Application. You will also see how to automate the creation of a slidedeck and run the slide show with PowerPoint PIA. Similarly, the Outlook PIA sample shows you how to get the statistics of your Outlook folders.
Office and .NET is a perfect marriage. While .NET and Windows Forms offers the best development tools for Windows Development, Office XP is the most popular end-user application in the world. Go on enjoy programming Office XP objects from your .NET Projects.