Migrating a Word VBA Solution to Visual Basic Using Visual Studio 2005 Tools for Office

  • Article

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Jan Fransen, OfficeZealot.com

Lori Turner, Microsoft Corporation

Updated: January 2006

Applies to: Microsoft Visual Studio 2005 Tools for the Microsoft Office System, , Microsoft Office Word 2003, Microsoft Office Outlook 2003, Microsoft Visual Basic, Microsoft Visual Basic for Applications

Summary: Learn about opportunities and challenges we encountered when we migrated a traditional Microsoft Office Visual Basic for Applications (VBA) solution to managed code, using Visual Studio 2005 Tools for Office. (32 printed pages)

Download OfficeVSTOMigratingWordVBAtoVBNET.msi.

Contents

  • Overview

  • Installing the Solution

  • Migration Case Study: The VBA Solution

  • Possible Architectures for the Visual Studio 2005 Tools for Office Solution

  • The Migration Process

  • Code Security

  • Conclusion

  • About the Authors

  • Additional Resources

Overview

Currently, developers show strong interest in using Microsoft Office 2003 Professional as a smart client development platform. The terminology may be new, but the interest is no surprise to one segment of the Microsoft development community: the long-time Microsoft Office and Microsoft Visual Basic for Applications (VBA) developer. The renewed interest in Microsoft Office development is mostly because of two innovations: the greatly expanded support of XML in Microsoft Office Word 2003 and Microsoft Office Excel 2003, and the introduction of Microsoft Visual Studio 2005 Tools for the Microsoft Office System (Visual Studio 2005 Tools for Office).

XML support means that you can separate the data stored in an Office document from the formatting of the document. End users still work with Word documents and Excel workbooks using the applications that they know, but data can be easily added to or extracted from these files for use in other applications.

Visual Studio 2005 Tools for Office allows you to use Microsoft Visual Basic or Microsoft Visual C# to create the same types of document-centric Office solutions traditionally written in VBA. Visual Studio 2005 Tools for Office opens up capabilities that are not available to Microsoft Office developers using VBA or that are difficult to implement.

If you develop Microsoft Office solutions using VBA, you may be curious about what you gain by using Microsoft Visual Studio 2005 and about how the development experience compares with VBA. You may also want to know, from a practical point of view, where to focus your education, if you want to fully use the capabilities of Visual Studio and the Microsoft .NET Framework. This article explores these issues from the perspective of a single Word-based solution. First we look at the original VBA solution, and then we look at how to migrate the solution to Visual Basic, using Visual Studio 2005 Tools for Office.

Installing the Solution

Software Requirements

To run the migration solution, you must have the following software installed:

  • Microsoft Windows 2000 or later

  • Microsoft Office Professional 2003 SP1, the complete installation or the standalone versions of Microsoft Office Word 2003 and Microsoft Office Outlook 2003 (including the primary interop assemblies)

  • Microsoft Visual Studio 2005 Tools for the Microsoft Office System

  • Microsoft SQL Server 2000, Microsoft SQL Server 2000 Desktop Engine (MSDE 2000), or Microsoft SQL Server 2005 Express (included with Visual Studio 2005 Tools for Office)

Copying the Sample

To download the sample application that accompanies this article, click OfficeVSTOMigratingWordVBAtoVBNET.msi at the top of this article. In the Microsoft Download Center, click Download, and then follow the instructions to run the installation.

By default, the sample files copy to the My Documents\Visual Studio 2005\Projects\Word VBA Migration Sample directory.

Creating the Database and Security Policies

Create the SQL Server database on your local computer and add the security policies required by the migration solution.

To create the database and security policies

  1. Start a Visual Studio 2005 command prompt:

    • On the Start menu, click All Programs.

    • Point to Microsoft Visual Studio 2005, and then point to Visual Studio Tools.

    • Click Visual Studio 2005 Command Prompt.

      A command window opens.

  2. Change to the folder where the sample files reside:

    %userprofile%\My Documents\Visual Studio 2005\Projects\Word VBA Migration Sample

  3. To set up the security policies, execute the following batch procedure:

    setupVBAToVSTOMigrationSecurity.bat

    If you are prompted to add the security policies, press Y and then press ENTER for each prompt.

  4. Change to the folder where the database files reside:

    cd Database

  5. To install the database on your local computer running SQL Server, execute the following batch procedure:

    setupVBAToVSTOMigrationDatabase.bat

    -or-

    To specify a server name, use:

    setupVBAToVSTOMigrationDatabase.bat YourServerName

    The default name for SQL Server 2005 Express is .\SQLExpress.

  6. Close the command window.

Setting up the VBA Sample

The VBA sample defaults to a SQL Server connection where the server name is (local).

To specify a different server name

  1. Start Word.

  2. Open the CustomerCommunicationVBA.dot template in the Customer Communications VBA folder.

  3. Open the Visual Basic Editor, and then view the code of the frmBuildLetter form.

  4. Navigate to the UserForm_Initialize method, and then replace (local) in the cnn variable with the name of your server.

  5. Save your changes to the code, and then close the Word template.

Building the Visual Studio 2005 Tools for Office Sample

  1. Open Visual Studio 2005 Tools for Office.

  2. On the File menu, click Open Project/Solution.

  3. Navigate to the CategoryLibrary folder, and then choose CategoryLibrary.sln.

    The path to the folder is My Documents\Visual Studio 2005\Projects\Word VBA Migration Sample\CategoryLibrary. CategoryLibrary defaults to a SQL Server connection where the server name is (local).

  4. To specify a different server name:

    • Right-click the CategoryLibrary project in Solution Explorer and choose Properties.

    • Click the Settings tab.

    • Replace (local) (in the value property of the NorthwindVSTO2005ConnectionString setting) with the name of your server.

  5. On the Build menu, click Build Solution.

  6. On the File menu, click Open Project.

  7. Navigate to the Customer Communication VSTO 2005 folder, and then choose CustomerCommunication.sln.

    The path to the folder is My Documents\Visual Studio 2005\Projects\Word VBA Migration Sample\Customer Communication VSTO 2005. CustomerCommunication defaults to a SQL Server connection where the server name is (local).

  8. Follow the same procedure as for the CategoryLibrary project to change the server name, if necessary.

  9. Right-click the LetterWizard project in Solution Explorer, and then select Add Reference.

  10. On the Browse tab, open the Release folder or the Debug folder.

    The path to the folder is My Documents\Visual Studio 2005\Projects\Word VBA Migration Sample\CategoryLibrary\bin\Release (or Debug).

  11. Select CategoryLibrary.dll, and then click OK.

  12. Press F5 to build and run the solution.

If you did not choose the installation option "Complete" when you installed Microsoft Office 2003, you may receive compilation errors related to not being able to find Microsoft.Office.Core or Microsoft.Office.Interop components. One way to correct this problem is to create empty Excel and Word applications, which install the components before running the new applications. Another way is to select Advanced during Microsoft Office installation and specify these three options:

  • .NET Programmability Support for Excel and Word

  • Forms 2.0 .NET Programmability Support

  • Microsoft Graph .NET Programmability Support for Visual Studio Tools for Office.

To run the application outside of the Visual Studio development environment, you must open the CustomerCommunication.dot file in the Customer Communication VSTO 2005\bin folder.

Migration Case Study: The VBA Solution

The Word solution we used for this case study cannot include every possible scenario you might encounter in your own solutions based on Microsoft Office. Instead, the solution focuses on a few techniques that are frequently found in VBA solutions:

  • The user interface (UI) is implemented through a form.

  • Data stored in a database is used to fill form combo boxes and list boxes.

  • The document's content can be changed using:

    • Entries made in the form.

    • A control placed directly on the document.

    • Data that is accessible through a custom class library.

  • The VBA code uses the Word object model to update the document.

  • The completed document can be used as the body of an e-mail message, created on-demand in Microsoft Office Outlook using the Outlook object model.

VBA Solution Overview

The marketing department at the fictitious Northwind Traders Company uses several boilerplate documents for customer communication that is produced on a recurring schedule. Each document is in the form of a letter with consistently formatted address and signature information, but with different body text. To keep the case study simple, only three letters are included.

Before any solution was available, Northwind used templates to create new documents with the boilerplate text and formatting. But information about the sender and the recipient, as well as customized information for the particular letter, had to be typed in by the user.

The VBA solution was developed to assist in the creation of these kinds of customer communication documents. The VBA solution consists of:

  • A Word template, CustomerCommunicationVBA.dot

    The template contains a user form that prompts the user for information about which letter to use, sender and recipient information, and additional information that varies depending on the letter chosen.

  • Three supporting document files

    These files must reside in a subfolder of the template's folder, named DocumentBodies. These documents contain the text for each of the customer communication documents that users can create by using the template.

  • A custom SQL Server database, NorthwindVSTO2005

    This database is based on the Northwind Traders sample database, which is included with Microsoft SQL Server.

  • A custom class library, CategoryLibrary

    This library returns either a list of features or a list of testimonials for a given product. Note that the class library was written with Visual Studio and registered for COM; this class library looks like a regular COM component to VBA.

Creating a Customer Communication Document

To run the customer communication VBA solution, the user creates a document based on the CustomerCommumicationVBA.dot template. When the document opens in Word, the template's form opens as well, as shown in Figure 1. The form is built to look like a standard, wizard with multiple pages.

Figure 1. The form looks like a standard wizard

The user steps through the wizard by selecting the tabs of the Multipage control or by clicking Next and Back.

The second page of the wizard prompts the user for information about which letter to create and about the final format (letter or e-mail message), as shown in Figure 2.

Figure 2. Each communication piece uses the same basic letter structure, but the text bodies differ based on which letter type the user selects

Depending on which letter type is chosen, the user sees different items on the Additional Information tab. The options for the Follow-Up to a Prospective Customer letter are shown in Figure 3. Note the features and testimonials that are listed, but disabled, on this page of the form. They are pulled from a database using the CategoryLibrary after the product line is selected.

Figure 3. The communication piece that promotes a new product references a product that the customer ordered previously

The last page of the wizard prompts the user to specify a recipient. For the letter to prospective customers, the text boxes are enabled and the list is disabled so the user can type in recipient information. For other letters, the user chooses a recipient from the customer list. Figure 4 shows what the form looks like when the user must choose from the customer list.

Figure 4. The user can choose a customer from the list or type the name of a prospective customer

After completing all steps in the wizard, the user clicks Finish. The appropriate letter body text is inserted and all text is updated with the specified sender, recipient, and additional information.

After the letter is created and the form is closed, the user can change the recipient by using a combo box placed on the document itself, as shown in Figure 5.

Figure 5. The user can choose a different recipient from the recipient combo box on the document

Behind the Scenes of the VBA Solution

To understand some of the issues involved in migrating the solution to Visual Studio 2005 Tools for Office, it is helpful to look at the code in the VBA solution. As we demonstrate later in this article, the following VBA features are of particular interest because the corresponding Visual Studio 2005 Tools for Office solution implements them differently than the VBA solution does:

  • Changing how the form looks based on the letter selected, by toggling the visibility of the three different Additional Information pages

  • Populating the combo boxes and list boxes with ActiveX Data Objects (ADO)

  • Getting the features and testimonials from the CategoryLibrary

  • Using document properties and the Word Range object to populate the document with information specified in the form

Implementing the User Interface

The form is built to work like a wizard, with Back, Next, Cancel, and Finish buttons. The easiest way to get this functionality with a VBA user form is to use a MultiPage control. The MultiPage control includes one page for each step in the wizard. In this case the MultiPage control also includes a page for each letter's additional information, as shown in Figure 6.

Figure 6. You can see all six pages of the MultiPage control at design time

When the form is running, only one Additional Information page is visible at a time. When the user selects a letter on the Communication Piece page, code runs to change which Additional Information page is visible.

When the user clicks Next or Back, the MultiPage control shows the next or previous page. This works because the value of the MultiPage control is the index number of the current page. When the user clicks Next or Back, code runs to add to or subtract from the value of the MultiPage control. To account for the two invisible pages, the code loops until it finds the next visible page before attempting to select the page.

When the form opens, all combo boxes and list boxes are populated with code. The Communication Piece list box is the only list that is not based on data from a table; instead, a two-dimensional array is defined and used to set the List property of the list box.

The lists for most of the other combo boxes and list boxes are based on data in SQL Server tables. For these controls, data is retrieved using an ADO recordset. The rows are read into an array, which is then used to set the List property of the combo box or list box. For example, the following code fills the Current Product and New Product combo boxes on one of the Additional Information pages:

Public Sub FillProductList()
    Dim rstProduct As ADODB.Recordset
    Dim intCount As Integer
    Dim strSQL As String
    Dim arProduct() As String
    
    Set rstProduct = New ADODB.Recordset
   ' cnn is a public variable set in the Userform_Initialize procedure.
    rstProduct.ActiveConnection = cnn
    strSQL = "SELECT Products.ProductID, Products.ProductName " & _
     "FROM Products ORDER BY Products.ProductName;"

    rstProduct.Source = strSQL
    
    rstProduct.Open CursorType:=adOpenKeyset, Options:=adCmdText
    ReDim arProduct(0 To rstProduct.RecordCount - 1, 0 To 1)
    For intCount = 0 To rstProduct.RecordCount - 1
        arProduct(intCount, 0) = rstProduct!ProductID
        arProduct(intCount, 1) = rstProduct!ProductName
        rstProduct.MoveNext
    Next intCount
    
    Me.cboCurrentProduct.List = arProduct
    Me.cboNewProduct.List = arProduct
    
    rstProduct.Close
    Set rstProduct = Nothing
End Sub

Two list boxes are not filled when the form is initialized: The boxes that list the features and testimonials for the selected product line are displayed for reference only — the user cannot change them. They are filled whenever the product line is changed by calling functions in our custom class library, CategoryLibrary, which return data in XML format. The Microsoft XML object model is used to read and parse the fragment:

    Set objMarketing = CreateObject("CategoryLibrary.FeaturesAndTestimonials")
    Set returnedFeatureDoc = New MSXML2.DOMDocument
    returnedFeatureDoc.LoadXml _
     (objMarketing.wsm_GetFeatures(CLng(Me.cboProductLine)))
    
    Set objFeatureRoot = returnedFeatureDoc.documentElement
    
    Me.lstFeatures.Clear
    ' Add each feature to the list (each child has only one node).
    For Each objFeatureElement In objFeatureRoot.ChildNodes
        Me.lstFeatures.AddItem objFeatureElement.Text
    Next objFeatureElement

Building the Document

When the user clicks Finish, the body document that the user requested inserts at a bookmarked location using the InsertFile method of the Range object:

Set rngBody = docNew.Bookmarks("Body").Range
rngBody.InsertFile ThisDocument.Path & _
 "\DocumentBodies\Body--Announcement of New Product to Retailers.doc", _
 link:=False

After all of the boilerplate text is added, the document must be updated with the recipient, sender, and additional information that the user specified in the form. These updates are made using custom document properties and fields. As shown in Figure 7, the document includes a custom document property for each value that needs to change based on user selections.

Figure 7. Custom properties can store specific information about a document

The custom document properties display as part of the document's content by using fields. Figure 8 shows several fields based on document properties within a fragment of the document.

Figure 8. You can use a DOCPROPERTY field to display the value of a custom property

After the code changes the values of all the relevant custom document properties, all fields in the document update to display new values.

If the user chooses the Follow-Up to a Prospective Customer letter, the features and testimonials returned by the Web service methods are added to the document using the Word Range object and its methods. For example, the following code takes the testimonials from the form's testimonials list box and adds them to the document:

For intRow = 0 To Me.lstTestimonials.ListCount - 1
    rng.InsertAfter Me.lstTestimonials.Column(0, intRow)
    rng.InsertParagraphAfter
    rng.Style = "Testimonial"
    rng.Collapse wdCollapseEnd
    rng.InsertAfter "--" & Me.lstTestimonials.Column(1, intRow)
    rng.InsertParagraphAfter
    rng.Style = "Name"
    rng.Collapse wdCollapseEnd
Next intRow
rng.InsertParagraphAfter

Creating an E-Mail Message

If the user requests an e-mail message instead of a letter, the letter is still created. After the letter's body text updates, the code uses Outlook objects to create a new e-mail message with a body consisting of the text in the letter, from the greeting line to the signature line.

Possible Architectures for the Visual Studio 2005 Tools for Office Solution

Before beginning any migration project, you should think through your goals for the migration. For the Northwind Traders solution example, the developer wants to migrate to Visual Studio 2005 Tools for Office because the resulting solution is more secure, has a more flexible deployment model, and provides more options for the UI.

The VBA solution serves its purpose as it is currently architected, but its users desire enhancements to the solution to better handle their workflow and work habits:

  • The VBA user form is modal and does not allow users to view or edit their documents during letter creation. Users want a more dynamic approach to building letters.

  • Also, after the user form closes, users cannot use the wizard again to make changes to their documents if they made a mistake. Their only option is to discard their work and start over.

Migrating to Visual Studio 2005 Tools for Office requires a new UI because you cannot directly import a VBA user form into a Visual Studio solution and you cannot convert a VBA user form to a Microsoft Windows Form. In our example, while moving the solution to Microsoft .NET, the developer also provides a more fluid UI for users. The developer could replace the VBA user form with a Windows Form, but she believes that the Document Actions task pane is a better choice because the Document Actions task pane is unobtrusive and can better satisfy the user's workflow requirements.

The developer also uses the migration as an opportunity to try the XML features available in Word 2003 and the data binding capabilities of Visual Studio 2005. This architecture provides more flexibility, if the developer wants to enhance the solution later.

User Interface Options

Visual Studio 2005 Tools for Office provides two primary choices for a UI: a Windows Form or the actions pane.

  • A Windows Form is analogous to a VBA user form. Visual Studio provides a form designer that you can use to create the form and write code to show it to the user. When the Windows Form displays, it covers at least part of the active document.

  • The actions pane, which is new in Visual Studio 2005 Tools for Office, is an object model that you can use to customize the Document Actions task pane, which previously you could customize only by using the Office 2003 Smart Document Software Development Kit. You write code to add controls to the actions pane, and (like all task panes) the Document Actions task pane appears alongside the active document.

These two UI options can coexist in the same solution: You could include a Windows Form to gather information, and then provide an actions pane to allow the user to change the recipient or to send the letter as an e-mail message after it is created. In our example, we decided that we could satisfy our goals by designing the main UI of our Visual Studio 2005 Tools for Office sample solution as a collection of user controls that display in the Document Actions task pane.

Options for Populating Controls and the Document

Data binding is an important feature in Visual Studio 2005. Using Visual Studio 2005 and Visual Studio 2005 Tools for Office, you can add data binding to Windows Forms controls, Excel ranges, Excel lists, Excel charts, Word bookmarks, and Word XML nodes. Our sample solution uses data binding features in Visual Studio 2005 Tools for Office to populate the controls in the actions pane, and to fill the sender and recipient information in the letters. Using data binding eliminates the need to migrate a large section of the VBA code, including:

  • The ADO code used to populate the form's list box and combo box lists, and the document's combo box list

  • The custom document properties for sender and recipient

The way you insert and update the document body text also changes in the Visual Studio 2005 Tools for Office solution. In the VBA solution, the document bodies are Word document files containing custom properties. The VBA code inserts the requested document, and then updates the custom properties and the fields based on those properties. For the Visual Studio 2005 Tools for Office solution, the document bodies are XML documents containing both WordprocessingML markup and elements from a custom XML schema. The Visual Studio 2005 Tools for Office code reads the requested document as an XML document, uses XML code to select and change the values of nodes in the custom schema, and then inserts an XML fragment using the InsertXML method of the Range object.

For the Follow-up to a Prospective Customer letter, the migration also creates the opportunity to make a change to the way that we insert into the document the XML fragments that are returned by the custom class library. The VBA solution uses document object model (DOM) code and the Word object model to parse, insert, and format the features and testimonials. For the Visual Studio 2005 Tools for Office solution, the returned XML fragments are transformed to WordprocessingML as they insert into the document. We no longer need to apply styles and other formatting through the Word object model.

Although it is not noticeable with this case study's small documents, manipulating XML in memory before inserting it offers significant performance advantages over manipulating the document's content through the Word object model.

Note

The XML part of the migration would be possible even if the solution remained a VBA solution: It uses the XML features added to Word 2003, which are available from either VBA or Visual Studio 2005 Tools for Office.

The Migration Process

Remembering the migration goals and with the overall architecture determined, we played the role of the Northwind Traders developers and began researching and coding. We found that, as Microsoft Office and VBA developers, the steepest "learning curve" is with Windows Forms controls. However, Visual Studio provides a much greater range of controls when compared to VBA user form controls, and even controls with user form counterparts often have enhanced functionality. After working with controls in .NET, we found it difficult to go back to the more limited user forms.

In the following sections of this article, we walk through the process used to create the Visual Studio 2005 Tools for Office solution. We do not provide a detailed walkthrough of steps to create a Visual Studio 2005 Tools for Office project or to add a data source; several published walkthrough white papers on MSDN are specifically designed to lead you through those processes. See the Additional Resources section for more information and links.

Migrating the Document

To begin the migration process, we made a copy of the template, CustomerCommunicationVBA.dot, and named it CustomerCommunicationVS.dot. We opened the document in Word, replaced each Recipient and Sender field in the document with a text string, and then bookmarked each string. After creating a Visual Studio 2005 Tools for Office project for the document, we bound these bookmarks to fields in the data source.

The sender's name field required special treatment. It appears in two places: the sender address block at the top of the letter and the signature. Instead of creating and binding two different bookmarks to the same data, we bookmarked the occurrence in the sender address block, and then used a cross-reference field to display the bookmarked text in the signature.

Tip

You can use Alt+F9 to toggle the display of field codes in Word when you want to examine the fields in a document.

Because we wanted to replace the combo box control with a Windows Form control in the actions pane, we deleted the combo box from the document. Next, we deleted all the custom properties used for recipient and sender information. Then, we opened the VBA editor and deleted the code modules and the form from the project. We could not remove the ThisDocument class module; instead, we opened it and deleted the code it contained.

Migrating the Document Bodies

In the VBA solution, the document bodies are in Word .doc format. The Visual Studio 2005 Tools for Office solution works with the document bodies as XML. It is easy to open a document in Word and save it as XML, but we wanted to mark up the document with our own schema so that the values of certain nodes change based on user selections.

To start this part of the migration, we created an XML schema for the document bodies that are used in the solution:

<?xml version="1.0"?>
<xs:schema 
 targetNamespace="http://www.northwind.com/marketingservices/"
 xmlns:ns0="http://www.northwind.com/marketingservices/"
 xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="DocData">
        <xs:complexType>
            <xs:choice>
                <_- Define sequences for each type of document -->
                <xs:sequence>
                    <_- New product promotion -->
                    <xs:element name="CurrentProduct" type="xs:string" />
                    <xs:element name="NewProduct" type="xs:string" />
                </xs:sequence>
                <xs:sequence>
                    <_- Prospective Customer -->
                    <xs:element name="ProductLine" type="xs:string" />
                    <xs:element name="FollowUpDate" type="xs:string" />
                </xs:sequence>
                <xs:sequence>
                    <_- Ex-customer -->
                    <xs:element name="Salesperson" type="xs:string" />
                    <xs:element name="SalespersonPronoun" 
                     type="xs:string" />
                    <xs:element name="SalespersonFirstName" 
                     type="xs:string" />
                </xs:sequence>
            </xs:choice>
        </xs:complexType>
    </xs:element>
</xs:schema>

Next, we migrated the document bodies by opening each document in Word and attaching the XML schema. We replaced the fields used by the VBA solution with plain text and then used the XML structure pane to mark up the document with nodes from the custom schema. If we used a node's value more than once within the document, we used the same technique that we used earlier with the sender's name: We bookmarked the first occurrence, and replaced the second and subsequent occurrences with fields referencing the bookmark text. After preparing a document, we saved it in XML format. Figure 9 shows an example of a completed document body.

Figure 9. Each document body uses the same XML schema

Building XSL Transforms for Web Services Data

To build the transforms required for the Testimonials and Features XML fragments that are returned by the custom CategoryLibrary, we wrote code to call each function and write out the returned XML as a file. Then, we opened each file in Word and formatted it as we wanted to see it in the document. Then, we used a tool that is available for download from Microsoft, the Office 2003 Tool: WordprocessingML Transform Inference, to create the necessary XSL transform files.

Creating a Visual Studio 2005 Tools for Office Solution

With all the documents prepared, we were ready to create the Visual Studio 2005 Tools for Office project itself. We created a Word Template project based on an existing document called CustomerCommunicationVS.dot, and named the project CustomerCommunication.

Figure 10. You can create a document for your project or base it on an existing document

When we saved our project, Visual Studio created the directory structure for the solution. Then, we copied the WordprocessingML versions of the documents and the transforms to the project folder.

We avoided writing code to navigate the directory structure by ensuring that the supporting files were copied to the same output folder as the solution's DLL file. To make this happen, we added each of the support files to the project, and then changed the Copy To Output Folder property for each file to Copy if newer. When we built the project later, a copy of each file was placed in the output folder, along with a copy of the document template and the assembly itself. In our example, the output folder is CustomerCommunication\bin.

Adding a Data Source

After we created the project, we set up the data source necessary for the solution by adding a new connection to the SQL Server database, NorthwindVsto2005, as shown in Figure 11.

Figure 11. Add a connection for the database

We saved the connection string with the name NorthwindVsto2005ConnectionString and included only those database tables that the solution actually uses. The connection string to the database is stored in the project settings.

Configuring Data-Bound Controls on the Document

With the data source available, we could set up all the data-bound controls on the document. When we created our project, Visual Studio automatically recognized all of the bookmark controls as host controls, enabling us to view the properties of each in the Control Properties window. For example, when we click in the FirstName bookmark on the document in the sender section, we can view and change properties for that bookmark in the Properties window.

To start binding bookmarks in the document to fields in the data source, all we had to do was drop items from the Data Sources window onto the target bookmarks. So, for example, we started with the FirstName bookmark in the senders area of the document. In the Data Sources window, we expanded the Employees table, clicked on the FirstName field, and dragged it onto the FirstName bookmark in our document. Our bookmark was now data bound.

After we bound the bookmark to a field in the Employees table, Visual Studio automatically added a dataset, a table adapter, and a binding source to the document (these are visible in the component tray). By examining the DataBindings property of the bookmark, we see that Visual Studio bound the FirstName field in the data source to our bookmark, as shown in Figure 12.

Figure 12. Databinding properties of the FirstName bookmark

The remaining steps of our example walkthrough are simple. From the Data Sources window, we dragged onto our document those fields in the Employees and Customers tables that we wanted to bind to the bookmarks in the sender and recipient areas of our document. Visual Studio added additional table adapters and binding sources to the document, as required by our data-binding selections. When we wanted to bind the CustomerCSZ bookmark, we found that there is not a field in the data source that exactly matches what we required: a calculated field that builds the recipient's City + Region + Postal Code as one string. In the Data Sources window, we right-clicked the dataset and chose Edit DataSet in the Designer. Then, in the designer, we added a new column to the Customers table and named it CustomerCSZ. We set the Expression property for this new column to return the desired string, as shown in Figure 13.

Figure 13. Adding a calculated expression to a dataset

We ran a first pass on our project, with just the bookmark data bindings, to see the results. When running the project, we found that the bookmarks on our document bound to the actual data in the data source, as shown in Figure 14. It happened just like that; we did not have to write any code.

Figure 14. Bookmarks with data bindings

Building the Actions Pane

With the document data bound, we had to create the solution's UI. After much thought, research, and experimentation, we changed the overall design of the solution's UI significantly.

As noted earlier, the VBA solution uses a MultiPage control to contain each separate set of controls (using the wizard analogy, to contain each step's controls). This architecture makes it easy to toggle which Additional Information page is visible, depending on the letter chosen. It was also easy to program the Next and Back buttons because changing the value of the MultiPage control changes which page is active. Although the user can use the control's tabs, instead of using the navigation buttons, to select pages in any order, this functionality was secondary, not a requirement.

Visual Studio 2005 Tools for Office includes a Windows Form MultiPage control that could be used in this solution. However, we wanted to make each step in the wizard independent of the other steps. Doing so ensures that controls are more reusable and able to be shared with other solutions that we might create later. Although the MultiPage control looks like its user form counterpart, it works very differently. Although we could have used a Windows Form MultiPage control to create a wizard-like interface, we wanted to try something new. In addition to the obvious usability requirements of any form, we had one driving requirement: to change portions of the UI — to hide some controls and display others — based on selections made by the user at run time.

We decided to work with user controls for this purpose, creating a user control for each step in the solution's UI.

As we started to plan the design for each step's controls, we soon realized that the controls should look the same and share some similarities in their function. This is necessary so that the individual controls act in combination and appear as one wizard in the solution. We created one user control to act as a basis for the others. One user control can determine the baseline appearance of the other user controls; this control can also expose common properties and methods to reduce code redundancy. This approach is called control inheritance.

As previously stated, one of our goals was to design controls that we could use with other projects we might develop later. To meet this goal, we required a Control Library. On the File menu, we pointed to Add, and then clicked New Project. We selected the Windows Control Library project type and named the new project LetterWizard. By default, Visual Studio automatically added one user control, UserControl1.vb, to the project. We renamed this control WizardBase.vb, and then designed the control surface. The base control appearance is simple. As illustrated in Figure 15, WizardBase required only three controls: one Label control to display a caption and two LinkLabel controls for handling navigation. These three controls had to be fixed, so that controls that inherit from WizardBase can only modify those properties of the controls that we allow. To accomplish this, we set the Modifiers property of each of the three controls to Private.

Figure 15. The WizardBase user control

After we started designing the base control, we considered the inherited controls, which add the correct functionality to WizardBase. Table 1 summarizes the user controls that would inherit from WizardBase.

Table 1. Summary of user controls for the Visual Studio 2005 Tools for Office solution

User control

Description

SelectSender

Sender information: a ComboBox to select sender information that appears in the document

SelectLetterType

Letter choice: a ListBox containing the type of letter (Former Customer, New Product, or Prospective Customer)

SelectVisitingSalesperson

Salesperson information (used when the letter type is Former Customer): a ComboBox to select salesperson information used to build the body of the document

SelectNewProduct

Product information (used when the letter type is New Product): ComboBox controls to select product information used to build the body of the document

SelectProductLine

Product line information (used when the letter type is Prospective Customer): a ComboBox for product line information and a DateTimePicker for a follow-up date used when building the body of the document

SelectRecipient

Recipient information: ComboBox and TextBox controls for recipient information that appears in the document

Next, we added the first inherited control to the LetterWizard project. Logically, we started with the SelectSender control because is the first control to appear in the letter wizard. Because SelectSender inherits from WizardBase, it already contains the Label for a caption and two LinkLabels for navigation. We added one ComboBox to hold the list of senders, and Label and TextBox controls to display the user's selection, as shown in Figure 16.

Figure 16. The SelectSender user control, which inherits from WizardBase

Although we still had to design the remaining controls, we wanted to see how this first control looks in the actions pane within our document. Adding a control to the actions pane is simple. First, we required a reference to the LetterWizard control library. We added a private member variable to the ThisDocument class to hold the reference to the instance of the SelectSender control used in the actions pane:

Private WithEvents step1 As SelectSender

Then, to add the control to the ActionsPane, we added code at the beginning of the document's Startup event:

step1 = New SelectSender
Me.ActionsPane.Controls.Add(step1)

Then, we pressed F5 to start the solution in debug mode. After Word loaded the document, the first control appeared in the actions pane, as shown in Figure 17.

Figure 17. Add a control to the actions pane

The control looks great in the action pane, but we still had to add some functionality to the control. To make the SelectSender control functional:

  • The control caption should describe the step.

  • The ComboBox control needs to bind to the Employees data source, to show the list of available senders. Additionally, the ComboBox should bind to the same Employees data source as the document, so that the user's selection in the task pane syncs with the sender information displayed in the document.

  • We had to add navigation functionality to the Previous and Next link labels.

These three tasks are common to most, if not all, of the step controls in the wizard. So, we added this functionality to WizardBase. Adding this functionality to the base class reduces the amount of code to write, provides consistency, and simplifies maintenance — it is easier to change code in one place instead of in many.

We added the control caption and data source properties to an initialization routine, which we named InitializeStep. InitializeStep also hides the Previous link label, if the control is designated as the first control in the wizard (step number = 1), and it changes the text of the Next link label to Finish, if the control is designated as the last control in the wizard (isLastStep = true):

Private stepNum As Integer
Private bindSource As BindingSource
Protected Event DoDataBind()

Public Sub InitializeStep(ByVal stepNumber As Integer, ByVal isLastStep _
  As Boolean, ByVal stepCaption As String, Optional ByVal bindSrc _
  As BindingSource = Nothing)
    stepNum = stepNumber
    If stepNum = 1 Then Me.previousLink.Visible = False
    If isLastStep Then Me.nextLink.Text = "Finish"
    Me.stepLabel.Text = stepCaption
    If Not (bindSrc Is Nothing) Then StepBindingSource = bindSrc
End Sub

Public Property StepBindingSource() As BindingSource
    Get
        Return bindSource
    End Get
    Set(ByVal value As BindingSource)
        bindSource = value
        RaiseEvent DoDataBind()
    End Set
End Property

In our design, we decided that inherited controls should be responsible for their own data binding. For this reason, we raise an event called DoDataBind, which inherited controls can handle to bind their controls to the data source.

Note

The DoDataBind event declaration is a Protected member. Be aware of the access levels for the members in classes that you write. A protected member can only be accessed from a class that derives from the base class. In the Visual Studio 2005 Tools for Office customer communication solution, this implies that DoDataBind can be handled only from controls that inherit from WizardBase. Therefore, while DoDataBind is accessible to the SelectSender control, it is not visible or accessible to the ThisDocument class.

Next, we had to call InitializeStep on the first wizard control. We added the call to InitializeStep in the document's Startup event:

step1 = New SelectSender
Me.ActionsPane.Controls.Add(step1)
step1.InitializeStep(1, False, "Select a sender:", _
    Me.EmployeesBindingSource)

We had to set three properties to data bind the ComboBox control: DataSource, ValueMember, and DisplayMember. As the name implies, DataSource represents the data source that the control uses for data binding. ValueMember is the underlying value of an item in the list. DisplayMember is the text that displays in the list. For the ComboBox on the SelectSender control, we wanted to display the employee's full name but associate it with the employee's ID for later use. For this reason, we assigned the ValueMember property to the EmployeeID field and the DisplayMember property to a calculated field named FullName. FullName uses an expression (LastName + ', ' + FirstName). We added the FullName calculated field to the Employees table using the approach previously described for the CustomerCSZ calculated field in the Customers table:

Private Sub SelectSender_DoDataBind() Handles Me.DoDataBind
    'Bind controls on this user control to the binding source
    With Me.sender
        .DataSource = Me.StepBindingSource
        .ValueMember = "EmployeeID"
        .DisplayMember = "FullName"
    End With
    Me.title.DataBindings.Add("Text", Me.StepBindingSource, "Title")
    Me.phone.DataBindings.Add("Text", Me.StepBindingSource, "Phone")
End Sub

At this point, we ran the project again, to see the changes. Word opened the document and displayed the actions pane, as shown in Figure 18. Just as expected, when we changed the selection in the combo box, the bookmarks on the document (bound to fields in the Employees table) updated to reflect the selection in the combo box.

Figure 18. Add a control to the actions pane

After completing the control caption and data source functionality, the next task was to handle the navigation in WizardBase — the LinkClicked event for the Previous and Next link label controls. In the LinkClicked event for each, we raised an OnPrevious (or OnNext) event. If OnPrevious (or OnNext) succeed, we then raise an Action event to signal that it is time to go to the previous (or next) step in the wizard:

Protected Event OnNext(ByRef Success As Boolean)
Protected Event OnPrevious(ByRef Success As Boolean)
Public Event Action(ByVal currentStep As Integer, ByVal stepDirection _
  As Integer)

Private Sub previousLink_LinkClicked(ByVal sender As System.Object, _
  ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) _
  Handles previousLink.LinkClicked
    'Raise OnPrevious. If it returns true, raise the Action event.
    Dim success As Boolean = True
    RaiseEvent OnPrevious(success)
    If success Then RaiseEvent Action(Me.stepNum, -1)
End Sub

Private Sub nextLink_LinkClicked(ByVal sender As System.Object, _
  ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) _
  Handles nextLink.LinkClicked
    'Raise OnNext. If it returns true, raise the Action event.
    Dim success As Boolean = True
    RaiseEvent OnNext(success)
    If success Then RaiseEvent Action(Me.stepNum, 1)
End Sub

At least two controls are required to test navigation. So, we proceeded to design the SelectLetterType control for the second step in the wizard. Again, in Solution Explorer, we right-clicked the LetterWizard project, pointed to Add, and then clicked New Item. We selected the Inherited User Control template, named the control SelectLetterType, clicked OK, and then chose WizardBase for the control to inherit from. SelectLetterType needs only one additional control: a ListBox control to contain our three letter types. We added the list box and the code that is required to populate the list box at run time; SelectLetterType does not need data binding:

Public Enum LetterTypes
    newProductAnnouncement
    prospectFollowup
    formerCustomerInputRequest
End Enum

Public Event LetterTypeChanged(ByVal letter As LetterTypes)

Private Sub SelectLetterType_Load(ByVal sender As System.Object, _
  ByVal e As System.EventArgs) Handles MyBase.Load
    ' Add the letter types to the letterList list box.
    With Me.letterList
        .Items.Add("Announcement of New Product to Retailers")
        .Items.Add("Follow-up to Prospective Customer")
        .Items.Add("Request for Input from Former Customer")
        .SelectedIndex = 0
    End With
End Sub

Public ReadOnly Property SelectedLetter()
    Get
        Return Me.letterList.SelectedIndex
    End Get
End Property

Private Sub letterList_SelectedIndexChanged(ByVal sender As Object, _
  ByVal e As System.EventArgs) Handles letterList.SelectedIndexChanged
    RaiseEvent LetterTypeChanged(Me.letterList.SelectedIndex)
End Sub

The second wizard control was complete. We added a private member variable to the ThisDocument class to reference an instance of SelectLetterType for the actions pane:

Private WithEvents step1 As SelectSender

As mentioned earlier, WizardBase raises an Action event when the user navigates in the wizard. Because we declared Step 1 using the WithEvents keyword, we could use the Class Name and Method Name drop-down lists in the Code Editor to set up an event handler for the Action event of Step 1:

Private Sub step1_Action(ByVal currentStep As Integer, _
  ByVal nextStep As Integer) Handles step1.Action

End Sub

We could have used the same process to set up the event handler for Step 2, but we wanted to use a single function to streamline the steps in the wizard. Given that we could use one function for handling the same event for multiple controls with the Handles keyword on the procedure declaration, we modified the Action event handler for Step 1 to include Step 2 — that is not possible in VBA:

Private Sub WizardAction(ByVal currentStep As Integer, _
  ByVal nextStep As Integer) Handles step1.Action, step2.Action

    ' Clear the controls from the actions pane.
    Me.ActionsPane.Controls.Clear()

    Select Case currentStep + nextStep

        Case 1
            ' Show Step 1: Create and intialize the control and fill its 
            ' data source.
            If step1 Is Nothing Then
                step1 = New SelectSender()
                step1.InitializeStep(1, False, "Select a sender:", _
                   Me.EmployeesBindingSource)
                Me.EmployeesTableAdapter.Fill( _
                   Me.NorthwindVSTO2005DataSet.Employees)
                Me.Fields.Update()
            End If
            Me.ActionsPane.Controls.Add(step1)

        Case 2
            ' Show Step 2: Create and initialize the control.
            If step2 Is Nothing Then
                step2 = New SelectLetterType()
                step2.InitializeStep(2, False, "Select a letter")
            End If
            Me.ActionsPane.Controls.Add(step2)

    End Select

End Sub

The WizardAction procedure manages initialization of the controls for the actions pane and handles removing controls from and adding controls to the actions pane, based on the current step number. We modified the Startup event again, so that it calls the WizardAction procedure to initialize and display the first control in the wizard:

Private Sub ThisDocument_Startup(ByVal sender As Object, _
 ByVal e As System.EventArgs) Handles Me.Startup
    WizardAction(1, 0)
End Sub

At this point, we ran the project again to test the navigation. Word opened the document and displayed the SelectSender control in the actions pane, as before. We selected a sender and clicked Next. As illustrated in Figure 19, the SelectSender control disappeared and the SelectLetterType control appeared in the actions pane, as expected. We clicked Previous to go back to the first step: the earlier selection in the ComboBox remained but we could change the sender and, again, the document updated to reflect our selection.

Figure 19. Add a control to the actions pane

With the WizardBase functionality in place for initializing the wizard controls and handling navigation, it was then clear how to proceed with adding the remaining controls to the wizard. We used the same approach for the remaining actions pane controls that we used for the SelectSender and SelectLetterType controls. This article does not discuss all the details of the code because you can download the project files for this solution and review them. However, these two data-related items may not be obvious from reviewing the code alone:

  • When we dragged a field from the Data Sources window and dropped it onto the Word document, Visual Studio automatically created a BindingSource and a DataTableAdapter for the data binding. These components appear in the component tray. When we bound the document bookmarks to fields in the Employees and Customers tables, Visual Studio added these components. But the Visual Studio 2005 Tools for Office solution also requires binding actions pane controls to fields in the Categories and Products tables; we added a BindingSource and a DataTableAdapter for both tables by selecting a BindingSource component in the Toolbox and dropping it onto the document. After we set the binding source's DataSource and DataMember properties, the table adapter was created for us automatically.

  • In all but one instance, we used the default Fill method for the DataTableAdapter, which returns all records in a table. For the New Product letter type, we had to return only those customers that ordered a certain product. To handle this situation, we added a parameterized query to the CustomersTableAdapter and associated the query with a custom Fill method named FillByProductID.

    Note

    To view this query in the sample solution, in Solution Explorer, right-click NorthwindVSTO2005DataSet.xsd, and then click View Designer. Right-click FillByProductID in the CustomersTableAdapter, and then click Configure.

Working with Word Bookmarks

Code used throughout the solution references bookmarks in the document, but not in the same way that we were used to referring to bookmarks in VBA. When we worked with a document that included bookmarks, Visual Studio 2005 Tools for Office promoted each bookmark to be a first-class control, generically called a host control. A Word bookmark used in Visual Studio 2005 Tools for Office code has all the functionality of Word bookmarks, plus additional events and features like data binding. Word 2003 also promotes XMLNode objects and XMLNodes collections to be host controls. In Excel 2003, host controls include ranges, list objects, and chart objects.

This means that if we want to refer to a bookmark in Visual Studio 2005 Tools for Office code, we can refer to it as a member of the document. Also, the host control bookmark merges the functionality of a Bookmark object with that of a Range object. For example, we could change the text in a Word bookmark object with code like this:

Me.RecipientName.Text = "Some Text"

It is more concise and easier to type the host control syntax because the bookmark names show up in the Microsoft IntelliSense member list. There is an additional benefit from another feature of the Bookmark host control: Unlike its VBA counterpart, the host control bookmark is not deleted when its text value is set.

Note

It is recommended that you use the host control equivalents to Word or Excel objects whenever possible.

Working with XML Documents

To work with an XML document in Visual Basic, we used the System.Xml namespace. In our solution, controls used for the third step in the wizard all use XML to build the body of the document:

  1. We loaded the XML document from a file.

  2. We used XPath to select nodes.

  3. We set the values of the selected nodes based on the user's selections in the actions pane.

  4. We raised an event to insert the XML document at a given bookmark.

  5. We updated fields in the newly inserted range.

Because several controls use this technique for inserting formatted text in the document, it makes sense to create a helper class to streamline the code. For this purpose, we added the XmlHelper class to the LetterWizard control library project:

Imports System.Xml

Public Class XmlHelper

    Private xmlDoc As XmlDocument
    Private nsManager As XmlNamespaceManager

    Public Sub New(ByVal template As String)
        ' Load the XML fragment. 
        xmlDoc = New XmlDocument
        xmlDoc.Load(template)

        ' northwind and wordml namespaces must be defined 
        ' for XPath navigation.
        nsManager = New XmlNamespaceManager(xmlDoc.NameTable)
        nsManager.AddNamespace( _
         "ns1", "http://www.northwind.com/marketingservices/")
        nsManager.AddNamespace( _
         "w", "http://schemas.microsoft.com/office/word/2003/wordml")
    End Sub

    Public Sub ModifyNode(ByVal xPath As String, ByVal text As String)
        xmlDoc.SelectSingleNode(xPath, nsManager).InnerText = text
    End Sub

    Public ReadOnly Property XmlDocument()
        Get
            Return xmlDoc
        End Get
    End Property

End Class

Loading an XML Document from a File

Notice that the XmlHelper constructor (Sub New) expects one string parameter. When we created a new instance of the XmlHelper class, we had to provide the file name and full path to the XML that we wanted to load. In the constructor, XmlHelper creates a new XmlDocument and calls its Load method to load the XML from the path we provided.

Selecting a Node with XPath

To use XPath for selecting XML nodes in an XML document with multiple namespaces, XmlHelper initializes a namespace manager and adds the namespaces that we need for XPath expressions. With the namespace manager in place, we used the SelectSingleNode method of the XmlDocument object to select nodes. The values of selected nodes are then changed using the XmlNode object's InnerText property.

Inserting XML at a Bookmark Location

In WizardBase, we added an event, InsertXmlAtBookmark, and a protected method, SetXMLData. The InsertXmlAtBookmark event is raised when XML is ready for insertion into the document. SetXmlData is responsible for raising this event:

Public Event InsertXmlAtBookmark(ByVal bookmarkName As String, _
  ByVal xml As String, ByVal xsl As String)

Protected Sub SetXMLData(ByVal bookmarkName As String, _
  ByVal xml As String, Optional ByVal xsl As String = "")
    RaiseEvent InsertXmlAtBookmark(bookmarkName, xml, xsl)
End Sub

The controls in the third step of the wizard (SelectFormer, SelectNewProduct, and SelectVisitingSalesperon) each call SetXmlData when new XML is prepared and ready for insertion into the document. This then raises the InsertXmlAtBookmark event, which the ThisDocument class handles. Notice that, again, we used the Handles keyword to use one function for handling the same event on multiple controls:

Private Sub InsertXmlAtBookmark(ByVal bookmarkName As String, _
  ByVal xml As String, ByVal xsl As String) Handles _
  step3Former.InsertXmlAtBookmark, step3NewProduct.InsertXmlAtBookmark, _
  step3Prospect.InsertXmlAtBookmark

    'Insert the XML at the bookmark & update fields in the bookmark range.
    Dim targetRange As Word.Range = Me.Bookmarks(bookmarkName).Range
    targetRange.InsertXML(xml, xsl)
    targetRange.Fields.Update()

    ' Recreate the bookmark because it was removed by the call to InsertXML.
    Me.Bookmarks.Add(bookmarkName, targetRange)

End Sub

Migrating the E-Mail Feature

If the user chooses the E-Mail option in the last step, the VBA solution uses the letter's content to automatically create an e-mail message. To move this e-mail feature to the Visual Studio 2005 Tools for Office solution, we had to add a reference to the Outlook object library to the project, and write code using the Outlook object model to create and preview the e-mail message.

To make it easier to read and write the code that uses the Outlook object model, we added an Imports statement to the form's class module just before the class declaration:

Imports Outlook = Microsoft.Office.Interop.Outlook

We assigned the name "Outlook" to the reference. That way, after the reference is set, the code to send an e-mail message is very similar to the VBA code for the same feature:

' Start Outlook using Automation and create a new mail message from
' the text in the document.
Dim otlApp As Outlook.Application
Dim otlEMail As Outlook.MailItem
otlApp = New Outlook.Application
otlEMail = otlApp.CreateItem(Outlook.OlItemType.olMailItem)
With otlEMail
    .To = Me.CustomersBindingSource.Current("EmailAddress").ToString
    .Subject = emailSubject
    otlEMail.BodyFormat = Outlook.OlBodyFormat.olFormatHTML
    otlEMail.Body = Me.Range( _
        Me.EMailBodyStart.Start, Me.YourTitle.End).Text
    .Display()
End With

Code Security

The sample code referred to in this article is intended for instructional purposes, and should not be used in deployed solutions without modifications. In particular, you must consider code security.

To illustrate the simplicity of this sample solution, a list of potential threats has been identified using the threat modeling process and tools described in the Threat Modeling section of the Microsoft Security Developer Center.

The following are some examples of identified threats that you should take into consideration before expanding or deploying this solution:

  • Visual Studio 2005 Tools for Office Assemblies are replaced

    If the Visual Studio 2005 Tools for Office assemblies are replaced with other assemblies, this can result in the application behavior being altered to compromise the system. You can mitigate this threat by signing the code, using strong name conventions, or providing hash evidence. This sample does not currently implement these precautions, but they are strongly recommended before deploying an application to a production environment.

  • SQL Server database is compromised and contains invalid data

    This threat can affect the data binding in the document and the Windows Form. You can mitigate this threat by ensuring that only users with a valid user name and password have rights to read or change the SQL Server data.

For more information on code security, visit the Microsoft Security Developer Center.

Conclusion

Not every VBA solution benefits from a Visual Studio 2005 Tools for Office migration. If you are uncertain whether to move a particular solution, check out the Additional Resources and evaluate the benefits and concerns for your individual situation. But if you want a better security model, more flexible deployment, better memory management, and an easier way to write data-based solutions, Visual Studio 2005 Tools for Office is worth investigating.

No matter which development tool you choose, learning more about XML and how it is used in Microsoft Office, and about Web services, will help you create richer Microsoft Office solutions.

If you migrate to Visual Studio 2005 Tools for Office, you need to learn a new language. Visual Basic is different from VBA, but not dramatically different. If you choose to move to Visual C# instead, you may require more time to feel comfortable in the language. The areas you focus on within Visual Studio and the .NET Framework will vary depending on the types of applications you develop, but in general, learning about Windows Forms and controls, data binding, ADO.NET, and working with XML in .NET will help you develop richer Microsoft Office solutions.

About the Authors

Jan Fransen spends her days helping people find ways to use technology to do their jobs better. As a consultant, she creates solutions in Microsoft Office, VBA, and Visual Basic .NET. As a trainer and writer, she teaches people how to program effectively in the Visual Studio and VBA environments, and how to perform the occasional interactive Office trick.

Lori Turner is a developer consultant with Microsoft Services with many years of expertise in Office development. Lori has been with Microsoft for more than 12 years and still loves it! When not assisting Microsoft customers or writing cool Office solutions with .NET, she enjoys spending time with her husband Robbie and twin daughters Haley and Jordan. She's most happy when outdoors: either at the beach or in her garden in Charlotte, North Carolina.

This document was created in partnership with A23 Consulting.

Additional Resources

Visual Studio 2005 Tools for Office

XML in Microsoft Office Word 2003

VBA to Visual Basic

Office Developer Center

Code Security