This article covers building .NET applications in Whidbey and manifesting them in Word and Excel documents. This is accomplished with Visual Studio 2005 Tools for the Microsoft Office System (VSTO 2005).

If you're like many developers, you wonder why you'd build a .NET application to manifest its user interface in a Microsoft Office application like Excel or Word.

I was the same way when I first heard about Visual Studio Tools for Office (VSTO) a couple of years ago. I immediately discounted it - until I played with it. Then I found version 1.0 (VSTO 2003, shipping since October, 2003) intriguing.

With some effort, I built a number of compelling applications in a fraction of the time it would have taken without the Excel or Word infrastructure. At that point, I was hooked.

You're certainly not going to manifest your .NET applications in Word or Excel every time, but there are some compelling reasons to do it in a number of scenarios, not the least of which is for those information workers who spend their professional lives in Excel and Word.

As it turns out VSTO 2003 is a very powerful technology. But debugging is difficult, if not impossible. And without a design-time environment in Visual Studio .NET building VSTO 2003 applications is kludgey. All in all, though, VSTO 2003 is pretty darn impressive for a 1.0 product.

But this article is about the 2.0 version of VSTO - officially called Visual Studio 2005 Tools for the Microsoft Office System, the Whidbey version of VSTO that will ship in the box when Visual Studio 2005 finally ships later this year.

So what is VSTO 2005?

Well, VSTO 2005 lets you build dynamic Office documents in managed code that run in process with Excel and Word. You get to leverage the existing Office plumbing and manifest your .NET applications in Microsoft Office's rich user interface. Plus you get all the security support (Group Policy/CAS) you're accustomed to in building and deploying .NET apps. In other words, like with .NET in general, VSTO 2005 lets you leverage a lot of Microsoft plumbing "for free."

And VSTO 2005 is just fantastic. I admit I'm enamored with the beta version of VSTO 2005. It has a ton of new features that make it very powerful and quite easy to build robust applications.

VSTO 2005 Features
VSTO 2005 envisions using Office as a platform for robust business solutions built in .NET. It targets professional developers. VBA isn't going away. But, if you've done anything significant in VBA you know the pain involved and VBA solutions are very difficult to secure. The primary VSTO 2005 design goal is to improve one's ability to use Word 2003 and Excel 2003 as front-ends for business solutions by taking full advantage of the .NET platform. The bad news: Only Word, Excel and InfoPath projects are available to VSTO 2005. It would be great to have PowerPoint and Outlook too. We can assume that the VSTO team has the rest of the Office suite on the top of their plans for future versions.

Clearly the best features of the "new VSTO" are the design-time improvements - the biggest of which is the integration of Visual Studio 2005 and Office.

Notice that Excel has been embedded in the Visual Studio Environment. Word and Excel are hosted as designers in the IDE to provide an integrated design-time experience. Visual Studio 2005 hosts an instance of the Office application that relates to the project type being created and lets you seamlessly drag either host controls or Windows Forms controls onto the Excel worksheet or Word document. VSTO 2005 supports managed controls, and Office controls have been have been extended to give you features such as data binding and an enhanced event model, like right-click events in the Excel spreadsheet.

As Windows Forms and ASP.NET developers you have enjoyed the full power of Visual Studio, including the availability of the debugger, source code control and even the easy way data binding is exposed through the Data Sources window. VSTO 2005 now makes these powerful features available thanks to the tight integration of Visual Studio and Office. Even the Word and Excel menus have been integrated into VS 2005. These Office tools can be accessed not only from the Office toolbar but from cascading menus. Whenever Visual Studio and Office share a top-level menu by the same name, the Office menu counterpart is made available through a new menu item that's under the Visual Studio main menu. This practice minimizes confusion between Office and Visual Studio menu items.

The programming model has also been greatly enhanced by separating the data from the view in Office documents. You can use XML schemas and strongly typed DataSets to program against data. This simplifies the creation of Office solutions by letting you manipulate the UI without changing the underlying code. Developing with task panes has been drastically simplified. User Controls can be leveraged in the Office task pane simply by adding a couple of lines of code to create custom task pane solutions.

VSTO uses the same code-behind model used by Windows Forms, with each sheet in the workbook having a code-behind class as well as a code-behind class for a Word document. Like ASP.NET, VSTO provides a code-behind style of development, creating a code-behind class for each worksheet, workbook or document according to project type. These code-behind classes get the core of their functionality by inheritance from other classes. In VSTO 2005, the code-behind classes inherit from either Microsoft.Office.Tools.Word.Document or Microsoft.Office.Tools.Excel.Worksheet depending on project type. Excel projects that use VSTO also have a class that they inherit from Microsoft.Office.Tools.Excel.Workbook that represents the workbook itself.

.NET makes VSTO solutions more reliable thanks to the Common Language Runtime (CLR). Visual Studio 2005 provides a rapid access development (RAD) environment in which to develop VSTO solutions and, along with .NET it eases the transition time that non-Office developers need to create robust business solutions using Office as a smart client.

VSTO 2005 leverages .NET Code Access Security, which provides a secure execution environment for end users. Users or IT administrators can determine which VSTO solutions will execute and prevent rogue VSTO-based documents from running. .NET makes VSTO 2005 solutions easy to deploy and upgrade with click-once deployment. The most common VSTO deployment scenario would be deploying the .NET assembly to a network share and deploying the Office document itself locally - even by e-mail. The .NET assembly and the Office document are "married" by an Application manifest embedded in Office document itself. So, theoretically, you could e-mail an Excel spreadsheet with extremely sensitive data - like payroll info - to the entire company, but only those with security privileges could open the document and run it.

VSTO 2005 Walk Through
So let's walk through the product. This step-by-step tour should give you a big jump over the VSTO 2005 learning curve. I'm using the Beta 1 refresh of Whidbey, but the Beta 1, the Beta 2 and most certainly the shipping product are just fine for this walkthrough. Obviously, In addition to a version of Visual Studio 2005, you will need Office 2003 Professional installed with the necessary programmability options.

Exercise 1: Demonstrate the integrated design-time experience.

1. Open Microsoft Visual Studio 2005.
2. Select File > New > Project... from the menu bar.
3. Click on Visual Basic on the left, then Excel Application on the right.
4. Create a name for the project or use the default and click OK.
5. On the next dialog, make sure Create a new document is selected and click Finish.
6. After Visual Studio finishes creating the project, you will see the Excel spreadsheet hosted inside of the IDE.
7. Notice how the Excel Toolbar has been integrated in the Visual Studio dev environment.

1. If the Toolbox isn't visible, select View > Toolbox from the menu bar.
2. Expand the Windows Forms controls category of the Toolbox.
3. Drag a DateTimePicker control and a Button onto the Excel Spreadsheet, keeping the default names.
4. Double-click the Button control to create an event handler for the button's Click event.
5. Add the following line of code to the event handler:

   MessageBox.Show(DateTimePicker1.Value.Date)

6. Select Debug > Start from the menu bar.
7. Test the project by selecting a date and clicking the button.

1. Go back to the Excel spreadsheet by clicking on the appropriate tab of the Visual Studio editor.
2. Expand the Excel Controls category of the Toolbox.
3. Drag a NamedRange control onto the Excel Spreadsheet and select a single cell as the target.
4. Double-click the NamedRange control to create an event handler for the control's Change event.
5. Add the following line of code to the event handler:

   MessageBox.Show(Target.Text)

6. Select Debug > Start from the menu bar.
7. Test the project by entering a value in the cell referenced by the NamedRange control and then selecting another cell.

Exercise 4: Demonstrate the data integration with SQL Server.