INFO: Develop Microsoft Office solutions with Visual Studio .NET (311452)

MORE INFORMATION

Introduction

The Microsoft Office family of products (including Access, Excel, MapPoint, Outlook, PowerPoint, Project, Visio, and Word) encompasses a wide range of productivity software that is designed for both corporate and small business users. As a suite of end-user tools, Office can aid in the efficient creation, sharing, and managing of your business information. Office can also help you to tailor the product to your organization, or to take advantage of Office technology to solve a particular issue.

Office development with Visual Studio .NET

There are a number of different ways to programmatically extend, to automate, to integrate, or to use Office in your development solution. If you are new to developing solutions with Office, see the MSDN Web site in the "Summary" section of this article, which covers many different forms of Office development and lists articles that are specific to each topic.

When you decide which type of Office development that you have to do, the next step is to decide what development tool to use. The purpose of this article is to discuss how to build Office solutions in Visual Studio .NET. It covers both unmanaged languages such as Microsoft Foundation Classes (MFC) and Active Template Library (ATL), and managed languages such as Visual Basic .NET and Visual C#. Which language you select depends on your project, your resources, and your familiarity with a particular language.

Microsoft has authored a number of Knowledge Base articles that show you how to perform common Office tasks in Visual Studio .NET. Because there are many articles, the purpose of this article is to help to organize that content, and to give you the important information that you must have to troubleshoot and to program more effectively when you work in .NET.

Important feature changes with the .NET Framework

Most developers who use Visual Studio .NET want to develop in languages that are closely tied to the Microsoft .NET Framework. There are some important changes to note when you use the .NET Framework with Office development. If you code in Visual Basic, Visual C#, or a third-party managed language for .NET, be familiar with these changes to know how you might have to structure your Office solution.

Note This section applies to managed languages only. If you are working in MFC or ATL, you can safely skip this section unless you plan to use Visual C++-managed extensions somewhere in your project. Read the following section if you are unfamiliar with the differences between managed and unmanaged languages.

The .NET Framework is about managed code. Managed code is code that is object-oriented, type-safe, memory-managed, secure, and (most of all) portable. This code is compiled on demand (or during install) from its native form, which is named Intermediate Language (IL), to machine code that can actually run on the processor. Additionally, Microsoft has defined a set of classes (with namespaces) that make up the runtime environment for managed code, which is named the common language runtime. The common language runtime is the programming backbone of any managed language in .NET, and gives you control over all of the elements that you must have to make your program.

Programs that are written with the .NET Framework are inherently safer to run, because they are more stable and securable than earlier programs. However, this power and flexibility comes at a price. The price is that there are structural differences in how the code is managed by the common language runtime. For developers who are familiar with Office development in Visual Studio 6.0, consider the following five important feature changes, and the effects that these feature changes have on your project:

1. COM interoperability In some respects, the .NET Framework is the evolution of the Component Object Model (COM) that was envisioned by Microsoft some time ago, and that was transformed for the Internet. The .NET Framework is very different from COM, and it is a radical break from the type of component architecture that you might be familiar with as a COM developer. Because Office was built around COM and OLE, you must still use COM interfaces when you work with Office. Microsoft recognized this problem early, and built support for previous COM communication (which .NET refers to as COM Interop) into the .NET Framework. The purpose of the interoperability layer is add a "shim" between your .NET code and the COM object that you plan to use so that neither side knows that it is talking to something that is not native to itself. In other words, the COM object appears similar to any .NET object to the common language runtime, and the .NET client appears similar to any COM client to the COM server. You must be familiar with COM Interop if you plan to perform Office development in Visual Studio .NET.
2. The death of the variant The .NET-managed languages use common data type system in the common language runtime. The important thing about this type system is that all data types inherit from a single "object" type which permits data to be handled polymorphically without the need for another outside type (for example, the COM or OLE Variant). Therefore, the .NET Framework does not use Variants. If you port Office code from Microsoft Visual Basic for Applications (VBA) or Visual Basic 6.0, you must retype Variants as Objects, and the new Object type can contain other data types than just Automation (IDispatch*) objects. Pay close attention to how your code uses these types, and add type checking as necessary.
3. Garbage collection Managed languages use garbage collector for memory management. This memory model is used to help .NET projects perform and scale better than if a reference model was used. It can also help reduce the number of accidental memory leaks that are caused by circular references. While this is good, the side effect that may be confusing to those who are familiar with Visual Basic 6.0 or VBA, is that the garbage collector is non-deterministic. When you release an object reference in VBA or Visual Basic 6.0, the release immediately frees the object that is referenced and allows it to shut down. This is no longer true in .NET. Because of the garbage collector, when you release an object does not indicate when it is cleaned up, and this can keep deterministic programs (like an Office COM server) in memory longer than you might expect.
4. Just-in-Time (JIT) compiling Managed code is kept in IL form to be platform neutral. This means that it must be compiled -on the fly- as it is executed the first time. Microsoft has developed a JIT process for managed languages that is fast and efficient, but this compile-on-demand requirement means that your client or component might be a little slower to load than if the code is unmanaged. Additionally, the common language runtime does both security and type checking when it handles the JIT, and may not run your code based on the execution environment.
5. Limits of OLE Conventional OLE is not a part of the common language runtime, and it is slowly being replaced by XML and HTML for "object" representation, and by Microsoft SOAP for linking to remote data sources. While there is not currently a full replacement for OLE (in all matters), .NET does not provide built-in support for traditional OLE containment or services in managed code. As a result, the new WinForms package (used by both Visual Basic and Visual C#) does not contain an OLE Container control. If you have a solution that requires Office OLE containment, develop an unmanaged host, or use the WebBrowser control (or a custom OLE host control) to handle OLE for you.

With these issues in mind, you can better approach the differences when you perform Office development in Visual Studio .NET compared to Visual Studio 6.0.

Automation of Office from Visual Studio .NET

Automating Microsoft Office is a convenient and flexible way to incorporate Office functionality into your custom solution. While future versions of Office will integrate with the .NET Framework, the current and past versions of Office do not. Much of what you must do to automate Office remains the same as what you did in Visual Studio 6.0. Office is COM based, so you must use COM to programmatically make changes to Office. Some of the details have changed, but the overall approach has not.

To automate Microsoft Office Applications from Visual Studio .NET, you will need to download and install the Primary Interop Assemblies (PIAs) for Microsoft Office XP. For more information about Office XP PIAs, click the following article number to view the article in the Microsoft Knowledge Base:

Developers who work with the PIAs for Office XP must be aware that the assemblies are slightly different from the Office object model. Therefore, you may have to make code changes if you are porting an existing application. Additionally, the PIAs exhibit behavior that is consistent with .NET as follows:

• nondeterministic release times
• lack of support for default methods or properties
• differences in how items are accessed in collections

For more information about Office XP PIAs known issues, visit the following Microsoft Web site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoxpta/html/odc_piaissues.asp

When you work on an Office Development solution, the first question you must ask is which versions of Office does this project have to work with? Compatibility is an important issue, and if you need your code to work with specific versions, you must do as much of your development and initial testing with the earliest version of Office that you need your code to be compatible with. For example, if you need a project that works with Office 2000 and later, use Office 2000 (and the Office 2000 type libraries) during your development. This way you can make sure that your project can run with Office 2000 and later (but not necessarily with Office 97). For more information, click the following article number to view the article in the Microsoft Knowledge Base: When you decide which versions of Office that you must work with, you must consider how you want to perform Automation in Visual Studio .NET:

• Use unmanaged code in Visual C++ the same as you would do in Visual C++ 6.0. -or-
  
  
• Use managed code in Visual Basic, Visual C#, or Visual C++ with managed extensions, and the COM Interop services of the common language runtime to handle COM calls for you.

Automation using unmanaged code (Microsoft Foundation Classes (MFC) and Active Template Library (ATL))

You can build unmanaged COM clients and servers with Visual C++ .NET just as you would in previous versions of Visual C++. The MFC and ATL frameworks have not substantially changed in regards to COM or OLE, so your existing code that uses Office Automation can be ported to Visual C++ .NET with very little trouble. Moreover, the manner in which you perform Automation (using MFC wrapper classes or #import) is similar to what you are already familiar with; therefore, there are no new technologies to learn.



Create class wrappers in MFC

The only real change for developers who are accustomed to working with MFC is the removal of ClassWizard for the Visual Studio IDE. This change effects how you create your MFC class wrappers for an Office application if you start a new project. For more information about how to create a new set of MFC wrapper classes from a type library in Visual C++ .NET, click the following article number to view the article in the Microsoft Knowledge Base: The resulting wrapper classes work much the same as those classes that are made by Visual Studio 6.0. You can expect Visual Studio .NET to create a separate file for each imported class. There are also some problems with the new class generator when you work with Office type libraries. For more information about these issues and the steps to take to avoid them, click the following article numbers to view the articles in the Microsoft Knowledge Base:

Automation samples Using MFC

For additional information about how to automate Office from Visual Studio .NET in MFC, click the article numbers below to view the articles in the Microsoft Knowledge Base:

Microsoft Excel
Microsoft Word
Microsoft PowerPoint
Shared Office features

Automation using managed code (Visual Basic or Visual C#)

Automation of Office from managed code is a little different from earlier Visual Basic programming. When you Automate in a managed language, you do not directly bind to the object. Your communication passes through an intermediate "wrapper" that translates native .NET data types and calls into COM types and calls (and vice versa).



COM interoperability in .NET

The COM Interop services of the common language runtime allow managed code that runs in .NET to communicate with earlier COM servers (such as Office) by using a wrapper between your managed code and the COM server. The wrapper is named an Interop Assembly. When you add a reference to a COM server (for example, its type library) in your .NET project, Visual Studio .NET uses the type information to generate code for a Runtime Callable Wrapper (RCW) which is then imported into your project. This managed code is compiled into a separate assembly (that is, the Interop Assembly), and is accessed by the .NET Framework as if it was any other .NET managed library. The RCW acts as the middle-man between your managed code and the COM server.

If a COM server is written to be .NET-aware, it can provide a Primary Interop Assembly (PIA) that is installed, and then is registered in the global assembly cache. These assemblies are shared, and are considered the best way to reference an earlier COM server in a .NET project. Currently, Microsoft provides PIAs for Office XP (and newer versions) only. These PIAs are available for download now, and can be redistributed with your project. For more information about Office XP PIAs, click the following article number to view the article in the Microsoft Knowledge Base: If your project calls for Automation to Office 2000, you must create your own Interop Assemblies (IAs) for Office/MSO, VBA Extensibility, and the Office application you want to automate. These custom IAs should be treated as a part of your application, and deployed to your application folder with the rest of your project. Although Visual Studio .NET can automatically make these assemblies for you by using the Tlbimp.exe utility, they are your custom assemblies, and should not be registered in the global assembly cache as global objects.

For additional information about COM Interop in .NET, see the COM Interop section in the Microsoft Developer Network (MSDN) Library at the following MSDN Web site:

Bind to Office applications from your runtime callable wrapper (RCW)

The Interop Assembly that you make from a type library uses early binding to communicate with the COM object. However, if you must keep the binding details generic (for example, if you need the code to Automate more than one type of Office application, but do not have to know which type explicitly), you can still do late binding in Visual Studio .NET. For more information about binding choices in .NET, click the following article numbers to view the articles in the Microsoft Knowledge Base: The advantage of late binding is that you do not have to use an RCW (or build/ship a custom Interop Assembly), and it is more version agnostic. The disadvantage is that it is more difficult to do this in Visual Basic .NET than it is in Visual Basic 6.0, and late binding still suffers the performance hit of having to find DISPIDs at runtime.

In additional to binding, another issue of importance is object instantiation. If you use an Interop Assembly, the RCW for the specific Office Application controls object instantiation when you use the New operator. Behind the scenes, the RCW calls the appropriate COM routine (for example, CoCreateInstance) to make a new instance of that application for your code to use. If you want to explicitly control object instantiation or bind to an already running instance of the application, you can use GetObject in Visual Basic .NET, or GetActiveObject in Visual C#. However, Office applications only register themselves in the Running Object Table (ROT) if they have gained and lost focus at least one time. If you are doing this in code, timing and focus are going to be big issues for your code to work. For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

Handle events from the RCW

The .NET Framework supports type safe, callback function pointers to invoke notification messages back to single client (such as a typical COM event) or to multiple clients (such as an asynchronous broadcast). The .NET object that handles this callback mechanism is called a Delegate, and for developers familiar with COM, consider Delegates as a way to handle COM events (although they can be used for other purposes).

When you make an RCW from a type library, any event methods that are described in the library are wrapped to form a Delegate class that you can use to handle the event. To sink an event, you implement the function to handle the event, make an instance of the delegate that is bound to that function, and then attach the delegate to the object that you want to receive events from. The following is an example:

// in Visual C# where <B>CellsChange</B> is a function that you have implemented:
 EventDel_CellsChange = new Excel.DocEvents_ChangeEventHandler( CellsChange);
 xlSheet1.Change += EventDel_CellsChange;

' in Visual Basic .NET:
 EventDel_CellsChange = New Excel.DocEvents_ChangeEventHandler(AddressOf CellsChange)
 AddHandler xlSheet1.Change, EventDel_CellsChange

Visual Basic developers can also take advantage of the WithEvents and Handles keywords to simplify this process and to make the code more readable. By declaring an object by using WithEvents, you can add the Handles keyword to your callback function, and Visual Basic .NET binds the Delegate for you automatically. The following is an example:

// in Visual Basic .NET:
 Private WithEvents xlSheet1 As Excel.Worksheet
 Private Sub xlSheet1_Change(ByVal Target As Excel.Range) Handles xlSheet1.Change
   Debug.WriteLine("You Changed Cells " + Target.Address + " on Sheet1")
 End Sub

For more information about using both approaches in Visual Basic .NET, click the following article number to view the article in the Microsoft Knowledge Base: If the RCW does not have a delegate for the event method that you want, if you have to provide for callbacks dynamically, or if you have to change them based on other considerations, you must use the COM IConnectionPoint and IConnectionPointContainer interfaces to set up your own sink. It is more work, but it may the only way to reliably sink events for some servers. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

Automation samples using managed code (Visual Basic or Visual C#)

For additional information about working in .NET, click the article numbers below to view the articles in the Microsoft Knowledge Base:

Microsoft Excel
Microsoft Word
Microsoft PowerPoint
Microsoft Access
Microsoft Visio
Microsoft MapPoint
Shared Office features


Known issues with automation from managed code

Because the common language runtime uses a garbage collector for memory management, and because your RCW is itself a managed object, the lifetime of your Automation object is not guaranteed to end in a deterministic fashion as soon as you release your reference to it. The RCW is marked for garbage collection and is released when the garbage collector wants to free more memory. This means that although you are no longer automating an Office application, the application may stay loaded because the garbage collector has not yet freed the RCW. For more information, click the following article number to view the article in the Microsoft Knowledge Base: For more information about problems you may experience when you work with Office objects from managed code, click the following article numbers to view the articles in the Microsoft Knowledge Base:

Office Web solutions with ASP.NET

ASP.NET is the new development platform in Visual Studio .NET that allows developers to create enterprise-class applications. ASP.NET allows you to develop Web applications in any .NET-enabled language and to take advantage of the rich functionality of the .NET Framework. ASP.NET Web forms allow you to build user interfaces for your Web applications. Although Web forms provide a user interface for your Web applications, the code-behind (code that runs to produce output for your page) for the page is still run on the server computer. Microsoft does not recommend that you automate the Office products from a Web Form, or that you automate from other non-interactive accounts. For more information, click the following article number to view the article in the Microsoft Knowledge Base: Other approaches to server-side automation include client-side automation, streaming Multipurpose Internet Mail Extension (MIME) content, and the Office Web Components. Each of these methods is described in the following section.

Use Office Web components in ASP.NET

Office Web Components (OWC) provide the best means to include Office functionality in your Web project. The components can be used in ASP.NET as client-side controls as they would be from any HTML page. Additionally, OWC 10 (Office XP) provides reasonable functionality to make it a good server-side component for limited size Web sites. For more information about how OWC 10 may be used in a server-side solution from ASP.NET, click the following article number to view the article in the Microsoft Knowledge Base:

Use Document Streaming from ASP.NET

If the Office Web Components do not have the functionality that you need, you can configure the HTML page to start the Office application on the client computer, and then to load the data from a Web server. The user can then use the full power of Office to modify, to edit, or to save the data that you returned.

You can use several methods to return Data to the client, including XML Web services and MIME content. The integration of XML Web services is a new addition to Visual Studio .NET. Visual Studio .NET allows developers to easily create and to consume XML Web services. XML Web services provide services to users through a standard Web protocol. Users can use a Visual Studio .NET application, or use the Microsoft SOAP Toolkit to connect to XML Web services. For additional information about the Soap Toolkit, visit the following Microsoft Developer Network (MSDN) Web site: Additionally, the Office XP Web Services Toolkit allows developers to reference an XML Web service directly from VBA without using the .NET Framework.

Another method to send data to a client computer is to stream the content by using MIME types. When you change the MIME type for a Web page to a specific value, the browser starts the associated application that displays the data. When you use this method, any type of data that can open in the Office application (such as Rich Text Format (RTF), HTML, and binary data) can be sent to the client after you set the content type. Because ASP.NET Web Controls generate HTML that is sent to the client, the content type for the Web page can be set to display the contents of the controls in the Office applications. For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

Office extensibility using Visual Studio .NET

Building extensible components to run inside Office has always been an important part of Office development. Both Office 2000 and Office XP allow for the programmatic extension of the host application by using COM add-ins. Office XP introduces capabilities to extend the document content through the use of a SmartTag Recognizer or an Action component. Because both of these solutions are heavily rooted in COM, consider the advantages of using a fully compiled COM component before you decide to build one in Visual Studio .NET.

However, if your design calls for using .NET features, or it can otherwise benefit from using .NET, you can create both types of add-ins in Visual Studio .NET. Developers who are familiar with how COM add-ins or SmartTags worked in Visual Studio 6.0 must keep the following in mind:

1. The add-in must be registered for COM. By default, managed DLLs that are made in Visual Basic or Visual C# do not act as COM components. To register them, you have to set the Register for COM Interop value to True under the Build options for Project Properties.
2. For Office to load your component correctly, strongly name your component, and then register it in the Global Assembly Cache (GAC). By default, a typical .NET DLL project does not do this.
3. Because managed code is not native, the COM registry entries made for your component point to the .NET runtime engine (mscoree.dll) and not to your assembly. Because mscoree.dll is not digitally signed and Office determines whether an add-in is safe based on a digital signature, your users may be prompted to enable or disable your add-in even if you digitally signed for your assembly. For additional details, see Office Security and Managed Code later in this article.

Remember that the current versions of Office were designed around COM and were not designed around .NET. If performance is a key factor to the success of your project, consider using an unmanaged language (such as MFC, ATL, or Visual Basic 6.0).

COM add-ins

COM add-ins are the most common type of extensibility solution that is used by developers. To fully integrate the add-in with the host application, developers have to automate the add-in from the application. For this reason, it is important that developers are familiar with the Automation of Office from Visual Studio .NET section earlier in this article.

For more information about how to provide the groundwork to make a COM Add-in in Visual Studio .NET, click the following article numbers to view the articles in the Microsoft Knowledge Base:

Smart tags

Smart Tags are a new technology that are introduced with Office XP. Smart Tags offer a new type of interaction with your documents. Currently, Smart Tags only work with Microsoft Word 2002 and Microsoft Excel 2002. For additional information about Smart Tags, see the Smart Tag Software Development Kit (SDK) at the following Microsoft Developer Network (MSDN) Web site: For more information about how to develop a SmartTag add-in using Visual Studio .NET, click the following article numbers to view the articles in the Microsoft Knowledge Base:

Office security and managed code

Because managed code is not native, the COM registry entries made for an Office component that you build with .NET point to the .NET runtime engine (mscoree.dll) and not your assembly. Because mscoree.dll is not digitally signed and Office determines whether an add-in is safe based on a digital signature, your users may receive a macro warning dialog box that prompts them to enable or disable your add-in, even if you digitally signed for your assembly. To avoid this macro warning, you can use a "shim" for your component. For additional information about developing and deploying a shim solution, see the following articles on the Microsoft Developer Network (MSDN) Web site: back to the top

OLE documents And storage

Visual Studio .NET does not provide any new functionality for editing or viewing Office documents inside a Windows application. In fact, for some developers who are familiar with Visual Basic, it may appear like a step back because you no longer have the familiar OLE Container Control. For more information, click the following article number to view the article in the Microsoft Knowledge Base: However, there is a reason for this. OLE is COM-based, and .NET is not. To live up to the cross-platform design for the common language runtime, OLE solutions cannot be tightly bound to the framework. If your solution requires OLE capabilities, you can use the WebBrowser control or a custom-built OLE Document viewer. For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base: For more information about how to use the existing OLE features in an unmanaged MFC or ATL project to build your solution, click the following article numbers to view the articles in the Microsoft Knowledge Base: