Lab 2: Adding Functionality to Word Document Solutions in Visual Studio .NET 2003

Article
07/11/2006

Lori Turner
Microsoft Corporation

March 2004

Applies to:
    Microsoft® Visual Studio® Tools for the Microsoft Office System
    Microsoft Office Professional Edition 2003
    Microsoft Visual Basic® .NET 2003

Summary: Demonstrates how to work with Word 2003 solutions created with Visual Studio Tools for the Microsoft Office System, including how to handle Word events and add Web services. (22 printed pages)

Introduction
Prerequisites
Getting Started
Exercise 1: Handling Word Events
Exercise 2: Using a Web Service with Word
Next Steps

Download the VSTOLabs.exe sample file.

Introduction

This lab contains two exercises that demonstrate how to work with Microsoft® Office Word document projects in Microsoft Visual Studio® .NET. It provides step-by-step instructions on how to create a Word Document project that uses Visual Basic® .NET code, how to handle Word events to initialize, save, and close a document based on a Word template, and how to use ADO.NET to import data.

Estimated time to complete:

Exercise 1: Handling Word Events - 40 minutes
Exercise 2: Using a Web Service with Word - 20 minutes

For a link to all labs in this series, see Visual Studio Tools for the Microsoft Office System Training.

Note The labs in this series are designed to be completed in order.

Prerequisites

To complete these exercises, you need:

Microsoft Visual Studio Tools for the Microsoft Office System.
Microsoft Office Word 2003, installed from Microsoft Office Professional Edition 2003.
Access to a server with the Northwind SQL Server sample database.
Sufficient permissions to read from and write to the SQL Server database.

Getting Started

Start this lab by creating a new Visual Basic® project using Visual Studio Tools for the Microsoft Office System.

To create a project

On the File menu in Visual Studio .NET, point to New, and then click Project.

The New Project dialog box appears.
In the Project Types list, expand Microsoft Office System Projects, and click Visual Basic Projects.
Select the Word Template project type.
Type ProductCatalog for the project name.
For the project location, type C:\Labs\Lab2, and click OK.

The Microsoft Office Project Wizard appears.
Click Finish.

Exercise 1: Handling Word Events

In this exercise, you handle Word events for the initialization, saving, and closing of a document based on a Word template. At run time, you access SQL Server data (for the purpose of building the document), build tables, and format the document. Additionally, you create a CommandBar in the context of the document and handle events for the CommandBar controls.

This exercise includes four different tasks:

Create a class for accessing Northwind data with ADO.NET.
Build a command barandcommand bar controls.
Handle the command bar button Click event to import data.
Handle the saving and closing of the document.

Task 1: Create a Class for Accessing Northwind Data with ADO.NET

The assembly for this lab accesses data in the Northwind SQL Server sample database to build a document. All the data access to SQL Server is placed in a single class module, clsDataAccess. This class does the following:

Connects to Northwind.
Returns a two-dimensional array containing information about all the records in the Categories table.
Returns a tab-delimited string for all the records in the Products table that match a given category.

When a new document is created from the template ProductCatalog.dot, a new instance of clsDataAccess is created and the two-dimensional array of category data is obtained.

To access Northwind data

On the Project menu, click Add Class.
Name the new class clsDataAccess.vb and click OK.
Add the following Imports statements at the top of clsDataAccess:
```
Imports System.Data.SqlClient
Imports System.Windows.Forms
```
Add the following class-level variables to clsDataAccess:
```
Private Conn As SqlConnection
```
Add code to initialize the clsDataAccess class with a connection to the Northwind database:
```
Public Sub New()
    ' Connect to the Northwind sample SQL Server database.
    Try
        Conn = New SqlConnection("Initial Catalog=Northwind;" & _
            "Data Source=localhost;Integrated Security=SSPI;")
        Conn.Open()
    Catch ex As Exception
        MessageBox.Show("Unable to connect to Northwind: " & _
            ex.Message, "ProductCatalog", MessageBoxButtons.OK, _
            MessageBoxIcon.Error)
    End Try
End Sub
```
The RetrieveCategoriesList function creates a dataset from the records in the Categories table and then returns a two-dimensional string array containing the list of category names, IDs, and descriptions to the caller.

**Note **The connection string assumes that the Northwind SQL Server database is on your local computer. If the database is on another computer, change the data source in the connection string appropriately.

Add RetrieveCategoriesList as a public member function to clsDataAccess.vb:

Public Function RetrieveCategoriesList() As Array

    Try
        ' Create a dataset for the Categories table.
        Dim cmd As SqlCommand = New SqlCommand( _
            "Select * From Categories", Conn)
        Dim da As SqlDataAdapter = New SqlDataAdapter
        da.SelectCommand = cmd
        Dim dsCat As New DataSet
        da.Fill(dsCat, "Category Information")
        Dim tbl As DataTable = dsCat.Tables("Category Information")

        ' Create a 2-dimenstional string array to hold category data:
        ' Element 0: Category Name
        ' Element 1: Category ID
        ' Element 2: Category Description
        Dim Cats As Array = _
            Array.CreateInstance(GetType(System.String), _
               tbl.Rows.Count, 3)
        For i As Integer = 0 To tbl.Rows.Count - 1
            Cats.SetValue(tbl.Rows(i).Item(1), i, 0)
            Cats.SetValue(tbl.Rows(i).Item(0).ToString(), i, 1)
            Cats.SetValue(tbl.Rows(i).Item(2), i, 2)
        Next

        ' Return the array of category names.
        Return Cats

    Catch ex As Exception
        MessageBox.Show("Problem retrieving category list: " _
            & ex.Message, "ProductCatalog", _
            MessageBoxButtons.OK, MessageBoxIcon.Error)
        Return Nothing
    End Try

End Function

Add RetrieveProductDataAsText as a public member function to clsDataAccess.vb.

The RetrieveProductDataAsText extracts the product data for a given category ID and returns that data as a tab-delimited string.

Public Function RetrieveProductDataAsText(ByVal CatID As Int32, _
    ByVal Headers As Boolean) As String

    ' This function returns product data to the caller as a 
    ' tab-delimited string.
    Try
        ' Retrieve the data for the given Category ID from the
        ' Categories table.
        Dim cmd As SqlCommand, rdr As SqlDataReader
        cmd = New SqlCommand( _
            "SELECT ProductID, ProductName, QuantityPerUnit, " & _
            "UnitPrice FROM Products WHERE CategoryID=" & CatID, Conn)
        rdr = cmd.ExecuteReader()

        ' Set the column headers as the first line in the string.
        Dim sData As String = ""
        If Headers Then
            sData = "Product ID" + vbTab + "Product Name" + vbTab + _
               "Quantity Per Unit" + vbTab + "Unit Price" + vbCrLf
        End If

        ' Retrieve each row of data in the DataReader
        ' where the fields are delimited by tabs 
        ' and the rows are delimited by CrLf.
        Do While rdr.Read()
            sData = String.Format("{4}{0}" + vbTab + "{1}" + _
                vbTab + "{2}" + vbTab + "{3:F2}" + vbCrLf, _
                rdr.GetInt32(0), rdr.GetString(1), _
                rdr.GetString(2), rdr.GetSqlMoney(3).ToDouble(), sData)
        Loop
        sData = sData.Remove(sData.Length - 2, 2) 'Drop the last CrLf
        ' Close the DataReader.
        rdr.Close()

        ' Return the string of product data.
        Return sData

    Catch ex As Exception
        MessageBox.Show("Problem retrieving product data: " _
            & ex.Message, "ProductCatalog", _
            MessageBoxButtons.OK, MessageBoxIcon.Error)
        Return String.Empty
    End Try

End Function

Add a public function member to clsDataAccess that closes the connection to Northwind:
```
Public Sub Close()
    Conn.Close()
End Sub
```
Open ThisDocument.vb.
Add a class-level variable to the OfficeCodeBehind class to store an instance of your clsDataAccess class:
```
Private da As clsDataAccess
```
Add a class-level variable to the OfficeCodeBehind class to hold the two-dimensional array of category data returned by the RetrieveCategoriesList function:
```
Private CatList As Array
```
Since this is a Template project, you use the document's New event for initializing the document. The New event is called each time a new document based on your template is created. For initialization, you create a new instance of clsDataAccess (to connect to Northwind) and obtain a two-dimensional array containing category data.
```
Private Sub ThisDocument_New() Handles ThisDocument.New
   da = New clsDataAccess
   CatList = da.RetrieveCategoriesList
End Sub
```

Testing the Application

Try it out:

Set a breakpoint on the End Sub of the New event handler.
Press F5 to build and run the project.

When the breakpoint is hit, examine the contents of the CatList array in the Locals window. If the Locals window is not visible, click Windows on the Debug menu, and then click Locals.
Press F5 to continue debugging.
Quit Word to stop debugging.

Task 2: Build a Command Bar and Command Bar Controls

When a new document is created from the template ProductCatalog.dot, a****command bar is added in the context of the new document. This new command bar contains a ComboBox control and a Button control as shown in Figure 1. The combo box is populated with the list of category names from the two-dimensional array.

Figure 1. Command bar containing a combo box and a button

To build the command bar

Add class-level variables to the OfficeCodeBehind class for the command bar and its controls:

Private CBar As Office.CommandBar
Private CBarCategoryComboBox As Office.CommandBarComboBox
Private WithEvents CBarImportButton As Office.CommandBarButton

Add the SetupCommandButtons function to the OfficeCodeBehind class.

The SetupCommandButtons function creates the command bar and its controls. It also populates the list in the combo box with the category names in the two-dimensional array CatList.

Private Function SetupCommandButtons() As Boolean
    Try
        ' Create a new, temporary command bar.
        ThisApplication.CustomizationContext = ThisDocument
        CBar = ThisApplication.CommandBars.Add( _
            "Product Catalog", , , True)

        ' Add a drop-down list box to hold the category names.
        CBarCategoryComboBox = CType(CBar.Controls.Add( _
            Office.MsoControlType.msoControlDropdown), _
            Office.CommandBarComboBox)
        With CBarCategoryComboBox
            .Caption = "Select a Category:"
            .Style = Office.MsoComboStyle.msoComboLabel
        End With

        ' Add the category names from the array to the combo box
        ' and set the selection to the first item in the list.
        Dim i As Int16
        For i = 0 To Convert.ToInt16(CatList.GetUpperBound(0))
            CBarCategoryComboBox.AddItem( _
                Convert.ToString(CatList.GetValue(i, 0)))
        Next
        CBarCategoryComboBox.ListIndex = 1

        ' Add a button to the command bar for executing the import.
        CBarImportButton = CType(CBar.Controls.Add( _
            Office.MsoControlType.msoControlButton), _
            Office.CommandBarButton)
        CBarImportButton.Style = Office.MsoButtonStyle.msoButtonCaption
        CBarImportButton.Caption = "Import Products"
        CBarImportButton.BeginGroup = True 'Adds a separator.

        ' Make the command bar visible.
        CBar.Visible = True

        Return True

    Catch ex As Exception
        MessageBox.Show("Problem Creating CommandBar: " & ex.Message, _
            "ProductCatalog", MessageBoxButtons.OK, _
            MessageBoxIcon.Error)
        Return False
    End Try

End Function

Add a line at the end of ThisDocument_New event handler to call SetupCommandButtons:
```
SetupCommandButtons()
```

Testing the Application

Try it out:

Press F5 to build and run the project.

A new document based on ProductCatalog.dot appears in Word. Your new command bar also appears in Word. The combo box contains a list of category names.

**Note **You have not yet added code to handle the import.
Create a new blank document in this instance of Word.

Notice that the Product Catalog command bar does not appear.
Switch to the document based on the ProductCatalog.dot.

Notice that the Product Catalog command bar reappears. Why? Prior to adding the new Product Catalog CommandBar in the SetupCommandBarButtons function, you set the Word application CustomizationContext to ThisDocument. This means that the Product Catalog CommandBar applies only to that document.

Task 3: Handle the Command Bar Button Click Event to Import Data

Now you can build the document at run time based on the selected category in the combo box. When the user selects a category, the code does the following:

Determines which category is selected and gathers details about that category.
Builds a table in the document.
Fills the table with the category information and pastes an image stored as an embedded resource in the assembly.
Adds the product details as a table in the document.

The finished document resembles Figure 2.

Figure 2. The finished document

First, add an image as an embedded resource in your assembly.

To add an embedded resource

Copy Nwind.jpg from your C:\Labs\Files folder to your C:\Labs\Lab2 folder.
In Solution Explorer, right click the ProductCatalog project, click Add on the shortcut menu, and then click Add Existing Item.
Browse to C:\Labs\Lab2\Nwind.jpg, and click Open to add it to your project.
Right-click Nwind.jpg in Solution Explorer and click Properties on the context menu. Change the Build Action property to Embedded Resource.
In Solution Explorer, right-click the ProductCatalog project and click Add Reference on the shortcut menu.
Click System.Drawing.dll in the .NET list of components, click Select, and then click OK.

The CopyImage procedure loads the embedded image resource and copies it to the clipboard.

Add CopyImage to the OfficeCodeBehind class:

Private Sub CopyImage()
   'Load the NWind.jpg resource and copy it it the clipboard
   Dim file As System.IO.Stream = _
      Me.GetType().Assembly.GetManifestResourceStream( _
      "ProductCatalog.Nwind.jpg")
   Clipboard.SetDataObject(System.Drawing.Image.FromStream(file), _
       False)
End Sub

**Note **In the call to GetManifestResourceStream, the case for ProductCatalog.Nwind.jpg should exactly match the case of the ProductCatalog project and Nwind.jpg as they appear in Solution Explorer.

You use class-level variables to store information about the currently selected category.

To store the currently selected category

Add the following class-level variables to the OfficeCodeBehind class:
```
Private CategoryID As Int32
Private CategoryName As String
Private CategoryDesc As String
```
The DisplayCategoryData function builds a two-column by two-row table in the document. This table gets populated with the category name, the description, and the picture from your embedded image resource.

Add the DisplayCategoryData function to the OfficeCodeBehind class:

Private Function DisplayCategoryData() As Boolean

    ' Create a table to hold the category information; the table is 2
    ' cols x 2 rows, has no borders, and the cells in the second
    ' column are merged.
    Dim tbl As Word.Table
    Try
        tbl = ThisDocument.Tables.Add( _
            ThisApplication.Selection.Range, 2, 2)
        tbl.Columns(2).Cells.Merge()
        Dim brdr As Word.Border
        For Each brdr In tbl.Borders
            brdr.LineStyle = Word.WdLineStyle.wdLineStyleNone
        Next
        ThisDocument.StoryRanges( _
            Word.WdStoryType.wdMainTextStory).InsertParagraphAfter()
    Catch ex As Exception
        MessageBox.Show( _
            "Problem creating Category table in the document: " & _
            ex.Message, "ProductCatalog", MessageBoxButtons.OK, _
            MessageBoxIcon.Error)
        Return False
    End Try

    ' Add the data to the the document.
    Try
        With tbl.Cell(1, 1).Range
            .Text = CategoryName
            .Style = ThisDocument.Styles("Heading 2")
        End With
        tbl.Cell(2, 1).Range.Text = CategoryDesc

        ' Copy the NWind Image (embedded resource).
        CopyImage

        ' Paste the image into the table.
        tbl.Cell(1, 2).Range.Paste()
        tbl.Cell(1, 2).Range.ParagraphFormat.Alignment = _
            Word.WdParagraphAlignment.wdAlignParagraphRight

        Return True

    Catch ex As Exception
        MessageBox.Show( _
            "Problem adding category data: " & ex.Message, _
            "ProductCatalog", _
            MessageBoxButtons.OK, MessageBoxIcon.Error)
        Return False
    End Try
End Function

The DisplayProductData function retrieves the product data from clsDataAccess as a tab-delimited string. This tab-delimited string is inserted into the document, converted to a table, and the resulting table is then formatted.

To build a table in the document

Add DisplayProductData to the OfficeCodeBehind class:

Private Function DisplayProductData(ByVal CatID As Int32) As Boolean

    ' Retrieve the product data for the given category as a tab-
    ' delimited string.
    Dim sProductData As String = da.RetrieveProductDataAsText( _
        CatID, True)
    If sProductData.Length <= 0 Then Return False

    ' Insert the tab-delimited string at the end of the 
    ' document, convert it to a table and format the text
    Try
        ' Insert the string at the end of the document.
        Dim pos1 As Object = ThisDocument.StoryRanges( _
            Word.WdStoryType.wdMainTextStory).End - 1
        ThisDocument.Range(pos1).InsertAfter(sProductData)
        Dim pos2 As Object = ThisDocument.StoryRanges( _
            Word.WdStoryType.wdMainTextStory).End

        ' Convert the text to a table using the tab delimiters.
        Dim tbl As Word.Table = ThisDocument.Range( _
            pos1, pos2).ConvertToTable( _
            Word.WdTableFieldSeparator.wdSeparateByTabs)

        ' Format the first row as the header row.
        With tbl.Rows(1).Range
            .Font.Bold = Convert.ToInt32(True)
            With .Borders(Word.WdBorderType.wdBorderBottom)
                .LineStyle = Word.WdLineStyle.wdLineStyleSingle
                .LineWidth = Word.WdLineWidth.wdLineWidth100pt
            End With
            .Shading.BackgroundPatternColor = _
                Word.WdColor.wdColorGray15
        End With

        ' Set preferred column widths to the table.
        Dim widths As Int32() = New Int32() {15, 40, 30, 15}
        For c As Int16 = 1 To Convert.ToInt16(tbl.Columns.Count)
            tbl.Columns(c).PreferredWidthType = _
                Word.WdPreferredWidthType.wdPreferredWidthPercent
            tbl.Columns(c).PreferredWidth = widths(c - 1)
        Next
        Return True

    Catch ex As Exception
        MessageBox.Show _
            ("Problem adding product details: " & ex.Message, _
            "ProductCatalog", _
            MessageBoxButtons.OK, MessageBoxIcon.Error)
        Return False
    End Try

End Function

To handle the Click event of the CommandBar button, you obtain information about the selected category from the two-dimensional array CatList, and call the DisplayCategoryData and DisplayProductData functions to build the document for that category.

To handle the Click event of the button

Add the following code to the Click event of CBarImportButton:

Private Sub CBarImportButton_Click( _
    ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, _
    ByRef CancelDefault As Boolean) Handles CBarImportButton.Click

    ' Clear the text in the document.
    ThisDocument.StoryRanges( _
        Word.WdStoryType.wdMainTextStory).Text = ""

    ' Get the category ID for the selection.
    Dim Index As Int32 = CBarCategoryComboBox.ListIndex - 1
    CategoryName = Convert.ToString(CatList.GetValue(Index, 0))
    CategoryID = Convert.ToInt32(CatList.GetValue(Index, 1))
    CategoryDesc = Convert.ToString(CatList.GetValue(Index, 2))

    ' Display the category data followed by the product data.
    If DisplayCategoryData() Then
        DisplayProductData(CategoryID)
    End If

End Sub

Testing the Application

Try it out:

Press F5 to build and run the project.
Select a category in the combo box and click Import Products.

Task 4: Handle Saving and Closing the Document

For this task, you use the DocumentBeforeSave event to:

Remove the custom document properties for the assembly.
Save the document to the user's My Documents folder without user intervention.

The DocumentBeforeClose event is handled to confirm that the user wants to close, and if so, closes the connection to Northwind.

In this example, saved documents do not require the functionality provided in the assembly (i.e., the CommandBar control with your custom Import Products feature). Therefore, when saving the document, you remove the custom document properties _AssemblyName0 and _AssemblyLocation0.

To remove the custom properties

Add the following code to the DocumentBeforeSave event of ThisApplication:

Private Sub ThisApplication_DocumentBeforeSave(ByVal Doc As _
    Microsoft.Office.Interop.Word.Document, _
        ByRef SaveAsUI As Boolean, ByRef Cancel As Boolean) _
        Handles ThisApplication.DocumentBeforeSave

    ' When a new document is saved, remove the assembly link
    ' location and assembly name since the "finished" document will
    ' not use the assembly.
    Try
        Dim DocProps As Office.DocumentProperties
        DocProps = CType(ThisDocument.CustomDocumentProperties, _
            Office.DocumentProperties)
        DocProps.Item("_AssemblyLocation0").Delete()
        DocProps.Item("_AssemblyName0").Delete()
    Catch
        'Do nothing
    End Try

End Sub

You override the Word Save and Save As commands to automatically save the document with the category name plus the ".doc" file extension in the user's My Documents folder.

To save the document

Append the following code to the DocumentBeforeSave event of ThisApplication:

' Save the document in the same path as the template and use the 
' category name for the document name.
Try
    Dim sFile As String = CategoryName
    sFile = sFile.Replace("/", "&")
    Dim sPath As String = System.Environment.GetFolderPath( _
         Environment.SpecialFolder.Personal)
    ThisDocument.SaveAs(String.Format("{0}\{1}.doc", sPath, sFile))
    Cancel = True
Catch ex As Exception
    MessageBox.Show("Problem saving document: " & ex.Message, _
        "ProductCatalog", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

Handle the DocumentBeforeClose event to confirm that the user wants to close when the document is "dirty" (has been changed). And, if the user confirms the close, close the connection to Northwind.

To close the document

Add the following code to the DocumentBeforeClose event of ThisApplication:

Private Sub ThisApplication_DocumentBeforeClose(ByVal Doc As _
    Microsoft.Office.Interop.Word.Document, ByRef Cancel As Boolean) _
        Handles ThisApplication.DocumentBeforeClose

    If Doc Is ThisDocument And Doc.Type = _
        Word.WdDocumentType.wdTypeDocument Then
        If Doc.Saved Then
            ' The document is already saved; allow the document
            ' to close without prompting the user.
            da.Close()
            da = Nothing
        Else
            ' The document is not saved; prompt the user
            ' to confirm the close.
            Dim ret As DialogResult = MessageBox.Show( _
                "Are you sure you want to close without" & _
                " saving this document?", _
                "ProductCatalog", MessageBoxButtons.YesNoCancel, _
                MessageBoxIcon.Error)
            If ret = DialogResult.Yes Then
                ' Setting the Saved property for the document
                ' closes the document without actually 
                ' saving and without any additional prompts
                ' from Word.
                Doc.Saved = True
                da.Close()
                da = Nothing
            Else
                Cancel = True
            End If
        End If
    End If
End Sub

Testing the Application

Try it out:

Press F5 to build and run the project.
Select a category and import the products for that category.
On the File menu, click Save (or Save As). The document is saved with the category name with no further prompts.
Import another category to "dirty" the document.
Close the document. The custom prompt appears upon closing. Click Yes to close without saving, and the document closes without any further prompts.
Quit Word.
Open the document your code saved to the My Documents folder. Notice that the command bar does not appear (the assembly does not load for this saved document since you removed the assembly location and name upon saving the document).

Exercise 2: Using a Web Service with Word

In this exercise, you provide culture-specific formatting using the System.Globalization namespace, and call a Web service from your Word project.

You should begin this exercise with the ProductCatalog project you created in Exercise 1.

In this exercise, you add functionality to convert the Unit Price amounts in the table to a currency of the user's choice. This conversion functionality is exposed to the user through the Product Catalog command bar as illustrated in Figure 3.

Figure 3. Product Catalog command bar

The Unit Price conversion converts the amounts using the currency exchange rate for the selected region and displays the converted Unit Price in a currency format applicable to the region. A Web service is used to determine the currency exchange rate.

To set up the command bar

Add the following class-level variables to the OfficeCodeBehind class:

Private CBarCurrencyComboBox As Office.CommandBarComboBox
Private WithEvents CBarConvertButton As Office.CommandBarButton
Private arrCurrency As String() = _
    New String() {"Canada", "Japan", "United States"}
Private arrCulture As String() = New String() {"en-CA", "ja-JP", "en-US"}

The SetupCommandButtons_Conversion function creates controls on the Product Catalog command bar. A combo box allows the user to select a region for the currency conversion and a button is used to perform the conversion.

Add SetupCommandButtons_Conversion to the OfficeCodeBehind class:

Private Function SetupCommandButtons_Conversion() As Boolean
    Try

        ' Add a drop-down list box to hold the currency list
        ' if it does not already exist.
        CBarCurrencyComboBox = CType(CBar.FindControl(, , _
            "ProductCatalog_Currency"), Office.CommandBarComboBox)
        If CBarCurrencyComboBox Is Nothing Then

            CBarCurrencyComboBox = CType(CBar.Controls.Add( _
                Office.MsoControlType.msoControlDropdown), _
                Office.CommandBarComboBox)
            With CBarCurrencyComboBox
                .Tag = "ProductCatalog_Currency"
                .Caption = "Display Currency for:"
                .Style = Office.MsoComboStyle.msoComboLabel
                Dim i As Int32
                For i = 0 To arrCurrency.Length - 1
                    .AddItem(arrCurrency(i))
                Next
                .BeginGroup = True
            End With
        End If
        CBarCurrencyComboBox.Enabled = True
        CBarCurrencyComboBox.ListIndex = 0

        ' Add a button to the command bar button 
        ' for executing the currency conversion 
        ' if the button does not already exist.
        CBarConvertButton = CType(CBar.FindControl(, , _
            "ProductCatalog_Convert"), Office.CommandBarButton)
        If CBarConvertButton Is Nothing Then
            CBarConvertButton = CType(CBar.Controls.Add( _
                Office.MsoControlType.msoControlButton), _
                Office.CommandBarButton)
            CBarConvertButton.Tag = "ProductCatalog_Convert"
            CBarConvertButton.Style _
                = Office.MsoButtonStyle.msoButtonCaption
            CBarConvertButton.Caption = "Convert"
        End If
        CBarConvertButton.Enabled = True

        Return True

    Catch ex As Exception
        MessageBox.Show _
            ("Problem Creating Currency CommandBar Controls: " & _
            ex.Message, "ProductCatalog", MessageBoxButtons.OK, _
            MessageBoxIcon.Error)
        Return False

    End Try

End Function

The currency conversion CommandBar controls are added after an import of new product data.

Append the following code to the Click event for CBarImportButton:

' Display the Currency Conversion commandbar buttons.
SetupCommandButtons_Conversion

You use a Web service to obtain the currency exchange rate for the region selected by the user.

To add a reference to the Web Service

Right-click ProductCatalog in Solution Explorer and select Add Web Reference from the context menu.
In the Add Web Reference dialog box, enter the following URL (note: the URL is case sensitive):

http://www.xmethods.net/sd/2001/CurrencyExchangeService.wsdl
Click Go to locate the CurrencyExchangeService Web service.

Note that CurrencyExchangeService provides one method named getRate.
Click Add Reference.

Add code to the Click event of CBarConvertButton to acquire the currency exchange rate and perform the conversion for the Unit Price column in the table:

Private Sub CBarConvertButton_Click( _
    ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, _
    ByRef CancelDefault As Boolean) Handles CBarConvertButton.Click

    Try

        ' Use the Currency Exchange Rate Web service to 
        ' calculate the rate between the US Dollar and 
        ' the currency for the selected country.
        Dim ws As New net.xmethods.www.CurrencyExchangeService
        Dim nCurrency As Int32 = CBarCurrencyComboBox.ListIndex
        Dim dCurrencyRate As Single = _
            ws.getRate("United States", arrCurrency(nCurrency - 1))

        ' Iterate the cells in the Price column of the 
        ' Products table and:
        ' 1) convert the values using the selected currency rate and 
        ' 2) display the values in a string localized for the region.
        Dim CuInfo As System.Globalization.CultureInfo = _
            New System.Globalization.CultureInfo _
                (arrCulture(nCurrency - 1))
        Dim ProdTable As Word.Table = ThisDocument.Tables(2)
        Dim row As Int32
        For row = 2 To ProdTable.Rows.Count
            Dim d As Decimal = CType(ThisApplication.CleanString( _
                ProdTable.Cell(row, 4).Range.Text), Decimal)
            ProdTable.Cell(row, 4).Range.Text = _
               String.Format(CuInfo, "{0:C}", d * dCurrencyRate)
        Next

        ' Disable conversion buttons.
        CBarCurrencyComboBox.Enabled = False
        CBarConvertButton.Enabled = False

    Catch ex As Exception
        MessageBox.Show("Unable to obtain currency conversion: " _
            & ex.Message, "Product Catalog", _
            MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try

End Sub

Testing the Application

Try it out:

Press F5 to build and run the project.
Select a category and click Import. The data appears in the document and the conversion controls are added to the Product Catalog command bar.
Choose a currency and click Convert. The results are similar to the illustration in Figure 4.

Figure 4. Finished document

Next Steps

See Lab 3: Adding Functionality to Excel Workbook Solutions in Visual Studio .NET 2003, where you learn how to work with Excel 2003 solutions created with Visual Studio Tools for Office, including how to handle Excel events, and how to access and import data.

For more information, as well as links to other labs in this series, see Visual Studio Tools for the Microsoft Office System Training.

Lab 2: Adding Functionality to Word Document Solutions in Visual Studio .NET 2003

Contents

Introduction

Prerequisites

Getting Started

Exercise 1: Handling Word Events

Task 1: Create a Class for Accessing Northwind Data with ADO.NET

Testing the Application

Task 2: Build a Command Bar and Command Bar Controls

Testing the Application

Task 3: Handle the Command Bar Button Click Event to Import Data

Testing the Application

Task 4: Handle Saving and Closing the Document

Testing the Application

Exercise 2: Using a Web Service with Word

Testing the Application

Next Steps

Additional resources