Microsoft & .NETVisual BasicUsing Visual Studio Tools for Office in Visual Studio 2008

Using Visual Studio Tools for Office in Visual Studio 2008

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

This article will help you to get started with the new Ribbon interface in Microsoft Office 2007. The Ribbon interface is new to Office 2007. So. see what it means for the developer’s community to create solutions for Office 2007 using the Ribbon interface. This means that the developer needs to have special tools to create applications that make use of the Ribbon interface. Microsoft has answered the developers’ wishes by releasing the updated version of Visual Studio Tools for Office in Visual Studio 2008.

Visual Studio 2008 includes the third version of Visual Studio Tools for Office (VSTO), a set of libraries and designers for developing applications, and add-ins for Microsoft Office 2003 and 2007. This is the first time VSTO has been bundled into Visual Studio, and it also represents a significant step forward in usability, a major factor in getting .NET programmers to adopt Office as a platform.

Before you go through a sample walkthrough on creating applications that make use of the Ribbon interface with Visual Studio 2008, please take a walk through history and see how Microsoft Office came to be used as a major tool for presenting data from business applications and why it remains one of the most powerful tools when it comes to automating tasks as well as presenting data from a variety of data sources.

Visual Basic for Applications

This is the first release that Microsoft developed long ago and has been a critical part of Microsoft Office until .NET came into the picture.

Visual Basic for Applications (VBA) was a simple development environment integrated into all the Office applications. Each Office application had a rich object model that was accessed via a technology known as COM. Millions of developers identified themselves as “Office developers” and used VBA and the Office COM object models to do everything from automating repetitive tasks to creating complete business solutions that leveraged the rich features and user interface of Office. These developers realized that their users were spending their days in Office. By building solutions that ran inside Office, they not only made their users happy, but also were able to create solutions that did more and cost less by reusing functionality already available in the Office applications.

Visual Basic for Applications is an implementation of Microsoft’s Visual Basic, an event-driven programming language and associated integrated development environment (IDE) that is built into most Microsoft Office applications. By embedding the VBA IDE into their applications, developers can build custom solutions using Microsoft Visual Basic. It was also built in to Office applications up to version 2004 for Apple’s Mac OS X, other Microsoft applications such as Microsoft MapPoint and Microsoft Visio; as well as being at least partially implemented in some other applications such as AutoCAD, WordPerfect, and ArcGIS. It supersedes and expands on the capabilities of earlier application-specific macro programming languages such as Word’s WordBasic, and can be used to control almost all aspects of the host application, including manipulating user interface features, such as menus and toolbars, and working with custom user forms or dialog boxes. VBA can also be used to create import and export filters for various file formats, such as ODF.

As its name suggests, VBA is closely related to Visual Basic, but can normally run code only from within a host application rather than as a standalone application. It can, however, be used to control one application from another using OLE Automation. For example, it is used to create a Word report automatically from Excel data, in turn automatically collected by Excel from polled observation sensors.

VBA is functionally rich and extremely flexible, but it does have some important limitations, including limited support for function pointers that are used as callback functions in the Windows API. It has the ability to use (but not create) (ActiveX/COM) DLLs, and later versions add support for class modules.

VSTO 2003

With the advent of .NET in 2001 and the bridge between the managed and unmanaged world, Microsoft realized that VBA wouldn’t stand on its own in the world of managed code. So, it realized that it would have to develop a new mechanism of integrating with Microsoft Office and enable developers and users to integrate Office within their business applications.

The first version of VSTO was simple, but it accomplished the key goal of letting professional developers use the full power of Visual Studio .NET and the .NET Framework to put code behind Excel 2003 and Word 2003 documents and templates. It let professional developers develop Office solutions in Visual Basic 2005 and C#. It solved the problem of embedded code by linking a document to a .NET assembly instead of embedding the .NET assembly in the document. It also introduced a new security model that used .NET code-access security to prevent worms and macro viruses.

Reasons for moving from VBA to VSTO

The following reasons explain what a developer gains when moving from a VBA environment to VSTO environment. There are many benefits when you migrate from VBA to Visual Studio 2005 Tools for Office, but for simplicity only the top benefits are listed below.

  • Improved development productivity: One reason to migrate from VBA to Visual Studio 2005 Tools for Office is to gain access to the integrated development environment between Visual Studio 2005 Tools for Office and Microsoft Visual Studio 2005. Visual Studio is one of the richest and most sophisticated development environments available, increasing coding and testing productivity. Although Visual Studio 6.0 also has one of the best debugging tools in the world, it is not enough to solve all issues related to the runtime environment of VBA. For example, it is hard to debug the environment when they run as a standalone application. Also, the famous DLL Hell at the client’s place makes it hard to pinpoint the cause of the error.
  • Improved security model: Back in the days of COM, there was not much reason to fear for security and worries about malicious code written by developers. Most applications could be trusted by the users and they didn’t pose security risks to the environment in which users ran their applications. Also, the VBA applications/components ran with full trust; this meant that they had access to the user’s file system, on which the application ran. Therefore, the application could do lot of things without the user’s knowledge. With the advent of the Internet, things changed a lot and users had to be cautious before running applications from external sources on their computers. Applications written in VSTO benefit from the security model built into the common language runtime (CLR) because they run in a trusted domain and cannot access the file system of the user.
  • Easier deployment and versioning: Because one of the primary benefits of the CLR is “no more DLL Hell,” this means that the user can create multiple versions of the same application and run them on the client side without having to worry about conflicts and the like. Managed solutions allow more flexible deployment options and simplified deployment and updates.
  • Added functionality from the.NET Framework: Because VBA is built on the Visual Basic language and Visual Basic 6.0 and earlier didn’t support OOPS, this meant that inheritance and reusability couldn’t be implemented. The developer had to write all the functionalities over and over, thereby bloating the size of the application. Having access to the Base Class Libraries of the CLR allows you to use its new features such as XML readers, ADO.NET, and simplified consumption of Web services.
  • Improved code reuse: Because Visual Basic .NET is a fully object-oriented language, this facilitates code reuse and hence can be used to build the building blocks for reuse in other applications.
  • Easier integration: The.NET Framework facilitates integration with other applications using Web services.

A sample application using VSTO

Now, you will do a walkthrough with Visual Studio to create a mail merge document so that the user can generate mailers on the fly from a custom data pane. This data pane will be mapped to a database; the user can flick through the records and then print out the document if he wants. Of course, he can return to the application any time to take the printout if he wants.

For this, you will be using Visual Studio 2008 and Office 2007. You will make use of a custom Word Template and the Document Pane in Office 2007. Note that the same can also be done using VSTO with Visual Studio 2005. Because VSTO is build into Visual Studio 2008, this makes it easier to start working.

For simplicity, you will start with a custom Word template, which is seen in Figure 1. This is mapped to a XML scheme and the tags can be seen in the template.

Figure 1: The XML tags in the invite letter that will replace the invite’s name and address from the database.

Note that you can see the XML tags in the document as follows. This is to indicate that these are the columns in the database. Take a customer table and refer it from a sample database named PartyInvite.

To,

[First Name] [Last Name]

[Street Address]

[City], [State]   [ZIP Code]



Dear [First Name],

Now, you will have to integrate this document with a VSTO application so that it is mapped to the table. When the document opens, you will see the screen, as shown in Figure 2.

Figure 2: The final output of the document after you complete this walkthrough.

To start with, fire up Visual Studio 2008 and select New Project. Then, select the Office 2007 category on the left side and then from the right side, select Word 2007 template (see Figure 3).

Figure 3: The pop-up dialog in Visual Studio 2008 that allows you to create a Word template based on the .NET 3.5 Framework.

Next, you will be asked to create a new document or use an existing document. Because you are going to import an existing template to this document, you will use this option. Select the second radio button and browse to the path of the template that I mentioned earlier (see Figure 4).

Figure 4: Select the second option, which allows you to reuse an existing template instead of creating a new blank document.

Click Finish to complete the initial stage; you now will be shown with the document inside of Visual Studio 2008, as shown in Figure 5. This is a new feature that allows the developer to preview and work with the document while writing code in .NET.

Figure 5: The Word document is previewed inside of your project in VS 2008; this is a new feature.

This completes the first part; the next part will create the panel to show the list of customers and bind them to the document. This requires that you understand the basics of Dataset, Table Adapter, and creating connections to a variety of data sources such as SQL Server 2005, SQL Express 2005, and MS Access. For this demo, you will be using SQL Express 2005 because it comes free and can be used on a desktop.

Now, create a typed dataset and refer that to the Customers table. This is because the first name, last name, Address, City, State, and Zip are all found in the Customers table.

Click the Server Explorer in Visual Studio 2008, right-click Data Connection, and click New Connection. This opens the Add Connection window, as shown in Figure 6. By now, most of you should be familiar with this because you have come across this in your development life cycle daily.

Figure 6: The Data Connection Wizard to establish a connection to the database and to be used in the Server Explorer.

Right-click the project in Solution Explorer and click add new item and choose Dataset from the dialog box (see Figure 7).

Figure 7: Adding a dataset to your project. This dataset will contain the query necessary for your application.

Now, you will see a blank window in which you can add a table (see Figure 8).

Figure 8: After adding the dataset to the project, the Dataset Designer comes where you can add your tables and build up your query.

Add a tableadapter to it because you are referring to a single table. Right-click the empty space in the middle window and then select TableAdapter from the pop up menu. You will be presented with a dialog box wizard to select the Data connection, as shown in Figure 9. Open the drop-down list box and you will see that the connection that you created recently is seen. Select that and click Next. Complete the rest of the wizard and make sure that you select only the Customers Table (see Figure 10) and nothing else.

Figure 9: Walking through the Wizard to add and configure a Table Adapter.

Figure 10: Add only the table Customers to the Table Adapter.

Now, you will see that in Figure 10 you have created a strongly typed dataset named customers; you will use this to refer data from the table into the document. The next step is to create the document panel; that is also made easier using VSTO built into Visual Studio 2008.

Right-click the project in Solution Explorer and then click New item. From the pop up dialog box, select the Actions Pane control from the Office template. Now, you will see a blank area without borders (see Figure 11); this reminds you of creating user controls in .NET. In fact, this is a custom user control on which you can drag and drop controls and write code behind to manipulate data and display the same on the document.

Figure 11: The Actions Pane control resembles the user control in .NET where you can add your own interface.

From the toolbox, drag and drop a BindingSource control on the action control panel. Rename it to CustomersBindingSource.

From the toolbox, drag and drop a Binding Navigator control on the action control panel. Rename it to Navigator1.

Using the properties dialog box, change the Datasource of the CustomersBindingSource control to Party Dataset.

Using the properties dialog box, change the BindingSource of the Navigator1 control to CustomersBindingSource.

Now, drag and drop some labels and text boxes on to the panel so that it looks like Figure 12.

Figure 12: The look of the user control after adding all the controls to view the Customers table.

Now, you need to bind the controls to each column of the table. Select the first text box; in other words, CustomerID. Now, select the Databinding properties from the properties dialog box and then expand it. Select Text from it and on clicking the drop-down arrow, you will see that you are able to see the list of datasources that you created just now. Expand it until you see the columns of the Customers table. See Figure 13. Select the first column CustomerId; this will bind the control to this column.

Figure 13: Mapping each field in the Actions Pane control with each field in the Table Adapter.

Do this for all the remaining controls in the panel so that each column is bound to its respective column in the Customers table.

Now, you will change to the .NET environment and then do some real coding to display the list of customers in the panel.

Double-click the panel and you will be switched to the code behind window.

Enter the following code so that the navigator populates data from the Customers table.

Private Sub Navigate_Load(ByVal sender As System.Object, _
                          ByVal e As System.EventArgs) _
                          Handles MyBase.Load
   Me.CustomersTableAdapter.Fill(Me.Party1.Customers)
End Sub

Now, go back to the Thisdocument.vb form; this is where you will write the complete code to display data in the navigator as well as populate the word document. The code for the complete class is seen below. Note that you will have to make a reference to Microsoft Office Word 12.

Imports WordTools = Microsoft.Office.Tools.Word


Public Class ThisDocument

   Private customerPane As Navigate

   Private Sub ThisDocument_Startup(ByVal sender As Object, _
      ByVal e As System.EventArgs) Handles Me.Startup
      ' Add the CustomerNavigator control to the actions pane
      customerPane = New Navigate()
      Me.ActionsPane.Controls.Add(customerPane)


      LockContentControls(False)
      'Bind the content controls to the data source
      BindContentControls()


      LockContentControls(True)
   End Sub


   Friend Sub LockContentControls(ByVal locked As Boolean)
      ' Iterate the host controls on the document. If the host
      ' control is the type PlainTextContentControl, then lock the
      ' contents and the control itself.
      For Each ctrl As Object In Me.Controls
         If ctrl.GetType() Is GetType( _
         WordTools.PlainTextContentControl) Then
            Dim plainContent _
               As WordTools.PlainTextContentControl = _
               TryCast(ctrl, WordTools.PlainTextContentControl)
            If plainContent IsNot Nothing Then
               Try
                  plainContent.LockContentControl = locked
                  plainContent.LockContents       = locked
               Catch ex As Exception
                  MessageBox.Show(ex.Message)
                  Exit For
               End Try
            End If
         End If
      Next
   End Sub

   Private Sub BindContentControls()
      For Each ctrl As Object InMe.Controls
         If ctrl.GetType().Equals(GetType( _
         WordTools.PlainTextContentControl)) Then
            Dim contentControl As WordTools.PlainTextContentControl
            contentControl = TryCast(ctrl, _
               WordTools.PlainTextContentControl)
            If contentControl IsNot Nothing Then
               If contentControl.Tag <> String.Empty Then
                  Try
                     contentControl.DataBindings.Add("text", _
                        customerPane.CustomersBindingSource, _
                        contentControl.Tag)
                  Catch
                     MessageBox.Show( _
                        "Unable to bind content control.")
                     Exit For
                  End Try
               End If
            End If
         End If
      Next
   End Sub

End Class

Now, review the above code and see what it does. The BindContentControls function binds the document’s XML tag to each column of the navigation panel.

The lockContentControls function locks the entire navigation panel so that the contents cannot be manipulated, except when clicking the Previous and Next rows of the Navigation Panel.

Now, go back to the Navigate.vb form and enter the following code so that the panel is unlocked when navigating through the controls.

Private Sub BindingNavigator1_Move(ByVal sender As System.Object, _
   ByVal e As System.EventArgs) Handles _
   BindingNavigatorMoveLastItem.Click, _
   BindingNavigatorMoveFirstItem.Click, _
   BindingNavigatorMoveNextItem.Click, _
   BindingNavigatorMovePreviousItem.Click
      ' When the current item in the binding navigator changes,
      ' unlock the content controls before data binding
      Globals.ThisDocument.LockContentControls(False)
End Sub

That is all is required to run the application. Press F5 and run the application. You will see that Word 2007 launches and after a while you will see that the Invite template loads along with the Action Pane. Inside the action pane, you will see a navigation panel that allows you to scroll through each customer as found in the Customers table.

Observe that, when navigating through each customer, the corresponding name is updated in the following area as seen, in Figure 14.

Figure 14: The final appearance of the document that updates the XML tag in the document with the data seen in the Actions Pane control.

Conclusion

This completes the walkthrough. You have seen that it is possible to create an action panel, bind the template to some columns, and dynamically display the contents of the database at runtime.

About the Author

M. Rajesh is from India and is a software developer working in a software firm in India. He received a Masters Degree in Mathematics and works on .NET development.

M. has received the Microsoft Most Valuable Professional Award from Microsoft for the past three years and was awarded in the following disciplines: .NET in 2003 and for Windows Shell in 2004 and 2005.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories