Lab 3: Adding Functionality to Excel Workbook 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 Excel 2003 solutions created with Visual Studio Tools for the Microsoft Office System, including how to handle Excel events, and how to access and import data. (28 printed pages)

Introduction
Prerequisites
Getting Started
Exercise 1: Handling Excel Events
Exercise 2: Using a Windows Form with Your Excel Workbook Project
Exercise 3: Importing XML Data into Excel
Next Steps

Download the VSTOLabs.exe sample file.

Introduction

This lab contains three exercises that demonstrate how to work with Microsoft® Office Excel workbook projects in Microsoft Visual Studio® .NET. You create a new Excel Workbook project using Microsoft Visual Studio Tools for the Microsoft Office System, and then you learn how to handle events for initializing and handling data validation in the workbook. You use ADO.NET for data access to SQL Server for the purpose of building the workbook at run time. You also design a Windows® Form that appears when your workbook opens and inserts a value into a worksheet when the form is closed. Finally, you extract XML from an ADO.NET dataset and import the XML data into the workbook.

Estimated time to complete:

Exercise 1: Handling Excel Events - 40 minutes
Exercise 2: Using a Windows Form with an Excel Workbook Project - 20 minutes
Exercise 3: Importing XML Data into Excel - 10 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 Excel 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 project using Visual Studio Tools for the Microsoft Office System.

To create a new project

On the File menu in Visual Studio .NET, click 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 Excel Workbook project type.
Type OrderForm for the project Name, type C:\Labs\Lab3 for the project location, and click OK.

The Microsoft Office Project Wizard appears.
Click Finish.

Exercise 1: Handling Excel Events

The objective of this exercise is to handle events for initializing and handling data validation in a workbook. You also use ADO.NET for data access to SQL Server for the purpose of building the workbook at run time. You create a command bar and use Excel events to enable or disable it based on the active window.

This exercise contains the following tasks:

Populate a worksheet with the data from a dataset.
Modify the workbook at design time.
Add a data validation list to the workbook at run time.
Handle the Change event of the worksheet.
Build a command bar and command bar controls.
Insert new order data into the database.

Task 1: Populate a Worksheet with the Data from a Dataset

The assembly for this lab accesses data in the Northwind SQL Server sample database to build a workbook that acts as an order form. All the data access to SQL Server is placed in a single class module named clsDataAccess. Initially, the clsDataAccess contains only one method, GetCategoriesAndProducts, which returns a DataSet to the caller. The DataSet, which is disconnected from the actual data source, contains records that result from a join between the Categories table and the Products table in Northwind.

When the workbook is opened, a new instance of clsDataAccess is created and the DataSet representing Categories and Products is retrieved. Once the data is retrieved, it is added to cells in the worksheet for later use in a data validation list.

To populate the worksheet with data

On the Project menu, click Add Class.
Name the new class clsDataAccess.vb, and click OK.
Add the following Imports statements to clsDataAccess:
```
Imports System.Data.SqlClient
Imports System.Windows.Forms
Add the following class-level variable to clsDataAccess:
Private sConn As String = "Data Source=localhost;" & _
    "Integrated Security=SSPI;Initial Catalog=Northwind;"
```
**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.

The GetCategoriesAndProducts function creates a dataset that represents a join between the Products table and the Categories table to show the relationship between each category and its associated products. This function returns the dataset (which is disconnected from the data source) to the caller.

Add the GetCategoriesAndProducts member function to clsDataAccess.vb:

Public Function GetCategoriesAndProducts() As DataSet

   Try
       ' Connect to the data source.
       Dim Conn As SqlConnection = New SqlConnection(sConn)
       Conn.Open()

       ' Create data adapters and datasets for the 
       ' Categories and Products tables.
       Dim da1 As SqlDataAdapter = New SqlDataAdapter( _
           "SELECT CategoryID, CategoryName FROM Categories", Conn)
       Dim da2 As SqlDataAdapter = New SqlDataAdapter( _
           "SELECT CategoryID, ProductID, ProductName, " & _
               "QuantityPerUnit, UnitPrice FROM Products", _
               Conn)
       Dim ds As DataSet = New DataSet
       da1.Fill(ds, "Categories")
       da2.Fill(ds, "Products")
       Conn.Close()

       ' Create a nested data relation between the Categories
       ' and Products tables based on the CategoryID field.
       Dim rel As DataRelation = ds.Relations.Add("CatProds", _
           ds.Tables("Categories").Columns("CategoryID"), _
           ds.Tables("Products").Columns("CategoryID"))
       rel.Nested = True

       Return ds
   Catch ex As Exception
       MessageBox.Show("Error Retrieving Categories & Products: " _
           & ex.Message, "Order Form", MessageBoxButtons.OK, _
           MessageBoxIcon.Error)
       Return Nothing
   End Try

End Function

Open ThisWorkbook.vb.
Add class-level variables to the OfficeCodeBehind class:
```
Private da As clsDataAccess
Private ds as DataSet
Private WithEvents Sheet1 As Excel.Worksheet
```
The AddCategoryAndProductLists function populates cells starting at a specified cell with the category and product data in the dataset.

Add the AddCategoryAndProductList function to the OfficeCodeBehind class:

Private Function AddCategoryAndProductLists(ByVal StartCell As Excel.Range) _
    As Boolean

    Try
        ' Add categories and products to the worksheet starting 
        ' at StartCell; add a defined name for each range of
        ' products where the defined name is the category name.
        Dim ProdRow As DataRow, CatRow As DataRow
        Dim r As Integer = 0, c As Integer = 0
        For Each CatRow In ds.Tables("Categories").Rows
            c = c + 1
            StartCell.Offset(0, c - 1).Value = CatRow("CategoryName")
            For Each ProdRow In CatRow.GetChildRows("CatProds")
                r = r + 1
                StartCell.Offset(r, c - 1).Value = _ 
                    ProdRow("ProductName")
            Next
            StartCell.Offset(0, c - 1).Resize(r + 1).CreateNames(True)
            r = 0
        Next

        ' Create a defined name for the range containing the 
        ' category names.
        StartCell.Resize( _
            1, ds.Tables("Categories").Rows.Count).Name = _
            "CategoryList"
        Return True
    Catch ex As Exception
        MessageBox.Show("Error adding category and product data: " _
            & ex.Message, "OrderForm", MessageBoxButtons.OK, _
            MessageBoxIcon.Error)
        Return False
    End Try

End Function

The Open event is called each time your workbook is opened. For initialization of the order form, you retrieve the category and product data and then call AddCategoryAndProductLists to fill the worksheet with that data.

Add code to the Open event for the workbook so it looks like the following:

Private Sub ThisWorkbook_Open() Handles ThisWorkbook.Open
    ' Store a reference to the first worksheet.
    Sheet1 = CType(ThisWorkbook.Worksheets("Sheet1"), Excel.Worksheet)
    ' Retrieve the list of categories and products and add
    ' the data starting at cell J1.
    da = New clsDataAccess
    ds = da.GetCategoriesAndProducts()
    If Not AddCategoryAndProductLists(Sheet1.Range("J1")) Then Return
End Sub

Testing the Application

Try it out:

Press F5 to build and run the project.
When the workbook is opened, confirm that the categories and products lists appear in a range starting at cell J1 of Sheet1 in OrderForm.xls.
On the Insert menu, choose Names, and then click Define.

Observe the names that the AddCategoryAndProductLists function defined for ranges in the workbook.
Close OrderForm.xls without saving changes and quit Excel.

Task 2: Modify the Workbook at Design Time

In this section, you modify the workbook at design time to create labels and a list for the order entry area of the workbook.

To modify the workbook

Start Excel.
While holding the SHIFT key, click the File menu and select Open to open OrderForm.xls.

**Note **Holding the SHIFT key prevents the managed code extension from running when the workbook is opened.
Add labels in cells A1 and A3:G3 as shown in Figure 1.

Figure 1. Worksheet labels
Select cells A3:G3.
On the Data menu, choose List, and click Create List.
In the Create List dialog box, check My list has headers, and click OK.
On the Data menu, click Filter, and then click AutoFilter to remove the filter from the list.
Save your changes to OrderForm.xls and quit Excel.
In Windows Explorer, select the read-only attribute for OrderForm.xls.

Task 3: Add a Data Validation List to the Workbook at Run Time

Recall that when you populate the worksheet with the categories and products data using the AddCategoryAndProductLists function, certain ranges are defined with names in OrderForm.xls. You use these defined names as sources for data validation in the workbook. Figure 2 illustrates the data validation to add for the Category field in column A.

Figure 2. Data-validation list

To add a data-validation list

Add the following class-level variable to the OfficeCodeBehind class:
```
Private bDoNotHandleCellChange As Boolean = True 
Private OrderList As Excel.ListObject
```
The AddCategoryValidationList function creates data validation for a given cell; the data validation is the list type and references the cells in the defined range CategoryList.

Add the AddCategoryValidationList to the OfficeCodeBehind class:

Private Function AddCategoryValidationList(ByVal rng As Excel.Range) As Boolean

    ' Add the category data validation list to the specified cell.
    Try
        With rng.Validation
            .Add(Excel.XlDVType.xlValidateList, _
                Excel.XlDVAlertStyle.xlValidAlertStop, _
                1, "=CategoryList")
            .InCellDropdown = True
            .ErrorTitle = "Order Form"
            .InputMessage = "Please select a category"
            .ErrorMessage = "The value you have entered is not a " & _
                "valid category. Please select a category from " & _
                "the list."
            .ShowInput = True
            .ShowError = True
        End With
        Return True
    Catch ex As Exception
        MessageBox.Show("Error adding data validation for " & _
            "categories: " & ex.Message, "Order Form", _ 
            MessageBoxButtons.OK, MessageBoxIcon.Error)
        Return False
    End Try

End Function

The category data validation is added to the worksheet when the workbook is opened.

Append the following code to the Open event of the workbook:

' Store a reference to the list on the worksheet.
OrderList = Sheet1.ListObjects(1)

' Add the data validation for categories to cell A4.
If Not AddCategoryValidationList(Sheet1.Range("A4")) Then Return

' Select the Category cell of the first order item.
Sheet1.Range("A4").Select()

' Flag that you're ready to handle the Sheet1 Change event
' when the user changes cells.
bDoNotHandleCellChange = False

Testing the Application

Try it out:

Press F5 to build and run the project.

When the workbook opens, cell A4 is pre-selected.
Click the drop-down arrow at cell A4 to observe the list of categories; the category data-validation list is illustrated in Figure 2.

Task 4: Handle the Change Event of the Worksheet

When the end user selects a category from the data-validation list, the product field for the corresponding record (or row) should be populated with the products for the selected category. Likewise, when the user selects a product, the product-specific data should be added to the respective fields for that record (or row) in the order form. You use the Change event for Sheet1 to accomplish both tasks.

Figure 3. Data validation for Products

The AddProductValidation function adds data validation to a cell; this function creates a list-type data validation and uses a defined range for the list source. The defined range is determined by examining a string for the category name.

To handle the Change event of the worksheet

Add the AddProductValidation member to the OfficeCodeBehind class:

Private Sub AddProductValidation(ByVal rng As Excel.Range, _
    ByVal CatName As String)

    ' Add the product list as a data validation list for 
    ' the specified cell. The product list is determined 
    ' by matching the name of the selected category
    ' with the same "defined range" in the worksheet.
    With rng.Validation
      .Delete()
      .Add(Excel.XlDVType.xlValidateList, _
          Excel.XlDVAlertStyle.xlValidAlertStop, _
          1, "=" & CatName.Replace("/", "_").Replace(" ", "_"))
      .InCellDropdown = True
      .ErrorTitle = "Order Form"
      .InputMessage = "Please select a product."
      .ErrorMessage = "The value you have entered " & _
          "is not a valid product for the selected " & _
          "category. Please select a product from the list."
      .ShowInput = True
      .ShowError = True
    End With

End Sub

Given a string that represents the name of a product, the GetProductDetails function returns the ProductID, QuantityPerUnit, and UnitPrice details for that product.

Add the GetProductDetails member to the OfficeCodeBehind class:

Private Sub GetProductDetails(ByVal sProductName As String, _
    ByRef ProductID As Int32, ByRef QtyPerUnit As String, _
    ByRef UnitPrice As Double)

    ' Obtain the ProductID, QtyPerUnit and UnitPrice values 
    ' for the given Product Name.
    ' Note: If the product name contains a single quote,
    ' that single quote is replaced with two single quotes
    ' that the query to find the matching record
    ' is valid.
    Dim dr As DataRow() = ds.Tables("Products").Select _
        ("ProductName = '" & _
        sProductName.Replace("'", "''") & "'")
    ProductID = Convert.ToInt32(dr(0)("ProductID"))
    QtyPerUnit = Convert.ToString(dr(0)("QuantityPerUnit"))
    UnitPrice = Convert.ToDouble(dr(0)("UnitPrice"))

End Sub

The Change event for a worksheet is triggered whenever any cell on that worksheet changes; the Target argument of the Change event denotes the cells that have changed. In the Change event, you determine if the user has modified a Category or Product field in the order form by using range intersection:

If the end user has changed a value in the Category field, the intersection of Target and column 1 in the OrderList returns a valid range.
Similarly, if the end user has changed a value in the Product field, the intersection of Target and column 2 in the OrderList returns a valid range.

Add code to the Change event of Sheet1 so it looks like the following:

Private Sub Sheet1_Change(ByVal Target As _ 
    Microsoft.Office.Interop.Excel.Range _
    ) Handles Sheet1.Change

    If bDoNotHandleCellChange Then Return

    If Target.Cells.Count > 1 Then Exit Sub

    ' First, check to see if a value in the first column 
    ' (Category) has changed. And, if so, then create the 
    ' data-validation list for the second column (Product)
    ' based on the selected Category.
    Try
        Dim rng1 As Excel.Range = ThisApplication.Intersect( _
            Target, OrderList.ListColumns(1).Range)
        If Not (rng1 Is Nothing) Then
            Dim ProductCell As Excel.Range = Target.Offset(0, 1)
            bDoNotHandleCellChange = True
            Target.Offset(0, 1).Resize(1, 5).ClearContents()
            AddProductValidation(ProductCell, _
                Convert.ToString(Target.Value))
            ProductCell.Select()
            bDoNotHandleCellChange = False
            Return
        End If
    Catch ex1 As Exception
        MessageBox.Show("Error adding product validation list: " _
            & ex1.Message, "Order Form", MessageBoxButtons.OK, _
            MessageBoxIcon.Error)
    End Try

    ' Next, check to see if a value in the second column (Product)
    ' has changed. And, if so, then extract the details for that 
    ' product and add the details to the corresponding cells for the 
    ' selected product.
    Try
        Dim rng2 As Excel.Range = ThisApplication.Intersect( _
            Target, OrderList.ListColumns(2).Range)
        If Not (rng2 Is Nothing) Then
            Dim ProductDetailCells As Excel.Range = _
                Target.Offset(0, 1).Resize(1, 4)
            bDoNotHandleCellChange = True
            ProductDetailCells.ClearContents()
            Dim ProductID As Int32, QtyPerUnit As String, _
                UnitPrice As Double
            GetProductDetails(Convert.ToString(Target.Value), _
                ProductID, QtyPerUnit, UnitPrice)
            CType(ProductDetailCells.Item(1, 1), Excel.Range).Value = _
                ProductID
            CType(ProductDetailCells.Item(1, 2), Excel.Range).Value = _
                QtyPerUnit
            CType(ProductDetailCells.Item(1, 3), Excel.Range).Value = _
                UnitPrice
            CType(ProductDetailCells.Item(1, 4), Excel.Range).Select()
            CType(ProductDetailCells.Item(1, 5), _
                Excel.Range).Formula = _
                String.Format("=E{0}*F{0}", Target.Row)
            bDoNotHandleCellChange = False
            Return
        End If
    Catch ex2 As Exception
        MessageBox.Show("Error retrieving product detail: " _
            & ex2.Message, "Order Form", 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 in A4. The product list is created for B4.
Select a product in B4. The product details appear in the row.
Enter a value for the quantity in F4. The item total appears in G4.
Repeat entry for additional order items.

Task 5: Build a Command Bar and Command Bar Controls

When the workbook is opened, a command bar is created. This new command bar contains two button controls as shown in Figure 4.

Figure 4. New command bar

To build a command bar

Add class-level variables to the OfficeCodeBehind class for the CommandBar and its controls:
```
Private CBar As Office.CommandBar
Private WithEvents CBarViewButton As Office.CommandBarButton
Private WithEvents CBarSubmitButton As Office.CommandBarButton
```
The SetupCommandButtons function creates the command bar and its controls.****

Add the SetupCommandButtons function to the OfficeCodeBehind class:

Private Function SetupCommandButtons() As Boolean
    Try
        ' Create a new, temporary command bar.
        CBar = ThisApplication.CommandBars.Add("Order Form", , , True)

        ' Add two buttons to the command bar.
        CBarViewButton = CType(CBar.Controls.Add( _
            Office.MsoControlType.msoControlButton), _
                Office.CommandBarButton)
        CBarViewButton.Style = Office.MsoButtonStyle.msoButtonCaption
        CBarViewButton.Caption = "View Categories and Products"
        CBarSubmitButton = CType(CBar.Controls.Add( _
            Office.MsoControlType.msoControlButton), _
            Office.CommandBarButton)
        CBarSubmitButton.BeginGroup = True
        CBarSubmitButton.Style = Office.MsoButtonStyle.msoButtonCaption
        CBarSubmitButton.Caption = "Submit Order"

        ' Make the CommandBar visible.
        CBar.Visible = True

        Return True

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

End Function

Append the following code to the Open event handler of the workbook to call SetupCommandButtons:
```
SetupCommandButtons()
```
The Order Form command bar controls should only be used with the OrderForm.xls solution. To enforce this, you can enable or disable the CommandBar when the workbook window is activated or deactivated.

Add code to the WindowActivate and WindowDeactivate events of the workbook so it looks like the following:

Private Sub ThisWorkbook_WindowActivate(ByVal Wn As _
    Microsoft.Office.Interop.Excel.Window) Handles ThisWorkbook.WindowActivate
    CBar.Enabled = True
End Sub

Private Sub ThisWorkbook_WindowDeactivate(ByVal Wn As _
    Microsoft.Office.Interop.Excel.Window) Handles ThisWorkbook.WindowDeactivate
    CBar.Enabled = False
End Sub

Testing the Application

Try it out:

Press F5 to build and run the project.

The Order Form command bar appears when OrderForm.xls opens.
On the File menu, click New, and then click Blank workbook in the task pane to create a new workbook.

Notice that the Order Form command bar is disabled when this new workbook is active.
Switch to OrderForm.xls, and the Order Form command bar is enabled again.

Task 6: Insert New Order Data into the Database

To add the new order information to the Northwind database, you use a transaction to:

Insert a new record in the Orders table and acquire the OrderID for this new order record.
Insert a new record in the Orders Detail table for each item in the order.

If an error occurs at any point during the transaction, the entire transaction is rolled back.

If the new order is added successfully, you display the new Order ID.

The AddNewOrder function takes an array containing the order items and a string for the Customer ID as parameters. Using these parameters, AddNewOrder inserts the new records in the Orders and Order Details tables in a transaction.

To add the new order information to the database

Open clsDataAccess.vb.

Add the AddNewOrder member function to the clsDataAccess class:

Public Function AddNewOrder(ByVal CustID As String, _
    ByVal OrderItems As Array) As Int32

    ' Connect to the data source.
    Dim Conn As SqlConnection = New SqlConnection(sConn)
    Conn.Open()
    Dim trans As SqlTransaction

    Try

        ' Create data adapters for inserting a new record into
        ' the Orders table and the corresponding new records into
        ' the Order Details table.
        Dim daOrders As New SqlDataAdapter( _
            "Select OrderID, CustomerID, OrderDate From [Orders]", _
            Conn)
        Dim dsOrders As DataSet = New DataSet
        daOrders.Fill(dsOrders, "Orders")
        Dim daOrdDetails As New SqlDataAdapter( _
            "Select OrderID, ProductID, UnitPrice, Quantity From " & _
            "[Order Details]", Conn)
        Dim dsOrdDetails As DataSet = New DataSet
        daOrdDetails.Fill(dsOrdDetails, "Order Details")

        ' Start a transaction for inserting records.
        trans = Conn.BeginTransaction(IsolationLevel.Serializable)

        ' Create the INSERT command for the Orders table.
        daOrders.InsertCommand = New SqlCommand( _
            "INSERT INTO [Orders] (CustomerID, OrderDate) VALUES " & _
            "(@CustomerID, @OrderDate); " & _
            "SELECT OrderID, CustomerID, OrderDate " & _
            "FROM Orders WHERE OrderID = @@IDENTITY", Conn)
        daOrders.InsertCommand.Parameters.Add("@CustomerID", _
            SqlDbType.NVarChar, 5, "CustomerID")
        daOrders.InsertCommand.Parameters.Add("@OrderDate", _
            SqlDbType.DateTime).SourceColumn = "OrderDate"
        daOrders.InsertCommand.Transaction = trans

        ' Create the INSERT command for the Order Details table.
        daOrdDetails.InsertCommand = New SqlCommand( _
            "INSERT INTO [Order Details] (OrderID, " & _
            "ProductID, UnitPrice, Quantity) VALUES" & _
            "(@OrderID, @ProductID, @UnitPrice, @Quantity); " & _
            "SELECT OrderID, ProductID, UnitPrice, Quantity From " & _
            " [Order Details]", Conn)
        With daOrdDetails.InsertCommand.Parameters
            .Add("@OrderID", SqlDbType.Int).SourceColumn = "OrderID"
            .Add("@ProductID", SqlDbType.Int).SourceColumn = _
                "ProductID"
            .Add("@UnitPrice", SqlDbType.Money).SourceColumn = _
                "UnitPrice"
            .Add("@Quantity", SqlDbType.Int).SourceColumn = "Quantity"
        End With
        daOrdDetails.InsertCommand.Transaction = trans

        ' Add the new Order to the dataset.
        Dim drOrders As DataRow
        drOrders = dsOrders.Tables(0).NewRow
        drOrders("CustomerID") = CustID
        drOrders("OrderDate") = Now()
        dsOrders.Tables(0).Rows.Add(drOrders)

        ' Apply the dataset changes to the Orders table and accept the 
        ' changes in the dataset to obtain the new OrderID.
        daOrders.Update(dsOrders, "Orders")
        dsOrders.AcceptChanges()
        Dim nOrderID As Int32 = Convert.ToInt32(drOrders("OrderID"))

        ' Next, add the products/quantities for this new order to
        ' the dataset and apply those changes to the Order Details
        ' table.
        Dim drOrdDetails As DataRow
        Dim n As Int32
        For n = 0 To OrderItems.GetUpperBound(0)
            drOrdDetails = dsOrdDetails.Tables(0).NewRow
            drOrdDetails("OrderID") = nOrderID
            drOrdDetails("ProductID") = OrderItems.GetValue(n, 0)
            drOrdDetails("UnitPrice") = OrderItems.GetValue(n, 1)
            drOrdDetails("Quantity") = OrderItems.GetValue(n, 2)
            dsOrdDetails.Tables(0).Rows.Add(drOrdDetails)
        Next
        daOrdDetails.Update(dsOrdDetails, "Order Details")

        ' Commit the transaction.
        trans.Commit()
        Conn.Close()
        Return nOrderID

    Catch ex As Exception
        ' If an error occurred, roll back the entire transaction for
        ' inserting records into Orders and Order Details.
        trans.Rollback()
        Conn.Close()
        MessageBox.Show("Error adding new order: " & ex.Message, _
            "Order Form", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Return 0
    End Try

End Function

When the end user clicks the Submit Order button, you build an array for the order items and call the AddNewOrder member of clsDataAccess. For now, you hard-code the CustomerID for new orders to 'ALFKI'.

Open ThisWorkbook.vb.

Add the following code to the Click event for the CBarSubmitButton CommandBar button:

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

    Try
        ' Build an array with the same number of rows as the 
        ' number of items in the OrderList data range and 3 columns. 
        ' Each row of the array contains the elements: (0) ProductID,
        ' (1) UnitPrice and (2) Quantity.
        Dim ItemCount As Int32 = OrderList.DataBodyRange.Rows.Count
        Dim arrOrders As Array = _
            Array.CreateInstance(GetType(System.Double), ItemCount, 3)
        Dim r As Int32
        For r = 1 To ItemCount
            arrOrders.SetValue( _
                CType(OrderList.DataBodyRange.Cells.Item(r, 3), _
                Excel.Range).Value, r - 1, 0)
            arrOrders.SetValue( _
                CType(OrderList.DataBodyRange.Cells.Item(r, 5), _
                Excel.Range).Value, r - 1, 1)
            arrOrders.SetValue( _
                CType(OrderList.DataBodyRange.Cells.Item(r, 6), _
                Excel.Range).Value, r - 1, 2)
        Next

        ' Pass the customer ID and the array of order items to 
        ' the AddNewOrder method of the data access class.
        Dim nOrderID As Int32 = da.AddNewOrder("ALFKI", arrOrders)
        If nOrderID > 0 Then
            MessageBox.Show("Your OrderID is " & nOrderID, _
                "Order Form", MessageBoxButtons.OK, _
                MessageBoxIcon.Information)
        End If

    Catch ex As Exception
        MessageBox.Show("Error with order submission: " & ex.Message, _
            "Order Form", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try

End Sub

Testing the Application

Try it out:

Press F5 to build and run the project.
Add a few new order items to the order list.
Click Submit Order.

If the order is added successfully, you receive a message with the Order ID. Note the Order ID for the newly entered order.
Quit Excel.
From the View menu in Visual Studio .NET, click Server Explorer.
In Server Explorer, locate the Order Details table in your SQL Server Northwind database.
Right-click Order Details and select Retrieve Data from Table from the context menu.
Locate your new order in the data that is displayed.

Exercise 2: Using a Windows Form with Your Excel Workbook Project

In this exercise, you design a Windows Form that appears when your workbook opens and inserts a value into a worksheet when the form is closed. You set the data source of a combo box on the Windows Form to an ArrayList.

You should start this lab with the OrderForm project from Exercise 1.

Task 1: Build a Windows Form in an Excel Workbook Project

In this lab, you build the Windows Form illustrated in Figure 5. The purpose of the Windows Form is to allow the user to select a customer for the new order when the workbook opens. A combo box on the form uses an ArrayList for its data source so that you can have one set of members displayed in the list while returning a value associated with the selected member to the caller. For your OrderForm solution, selecting a customer in the form is required; therefore, if the user cancels the form without selecting a customer, you want the OrderForm solution to end.

Figure 5. Windows Form

To build a Windows Form

On the Project menu, click Add Windows Form. Name the new form CustomerForm.vb and click OK.
Add a ComboBox and two Button controls to the form as illustrated in Figure 5.

Apply the following properties to the form and the form controls:

Table 1. Properties to apply to the form.

Control	Property	Value
Form	Text	Select a Customer
	FormBorderStyle	FixedDialog
ComboBox	Name	cboCustomers
	DropDownStyle	DropDownList
Button	Name	btnOK
	Text	OK
	DialogResult	OK
Button	Name	btnCancel
	Text	Cancel
	DialogResult	Cancel

When the CustomerForm is dismissed by clicking OK, it should store the ID of the selected customer.

Add the following code to the CustomerForm class:
```
Private m_ID As String = ""

Public ReadOnly Property CustomerID() As String
    Get
        Return m_ID
    End Get
End Property

Private Sub btnOK_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles btnOK.Click

    m_ID = Convert.ToString(cboCustomers.SelectedValue())
End Sub
```
The ComboBoxDataSource property may be set to an ArrayList of custom objects. The advantage of doing so is that you can display one value in the drop-down list (like the Customer Name) but return another value corresponding to the list selection (like the Customer ID).

The ArrayList in this example consists of objects based on a Customer class; each Customer object provides a Name property and an ID property for use by the ComboBox.

Nest the Customer class inside the clsDataAccess class:

Public Class Customer

    Private m_ID As String
    Private m_Name As String

    Public Sub New(ByVal ID As String, ByVal Name As String)
        MyBase.New()
        Me.m_ID = ID
        Me.m_Name = Name
    End Sub

    Public ReadOnly Property ID() As String
        Get
            Return m_ID
        End Get
    End Property

    Public ReadOnly Property Name() As String
        Get
            Return m_Name
        End Get
    End Property

End Class

The clsDataAccess class contains a member function named GetCustomers. GetCustomers retrieves customer names and IDs from Northwind to build an ArrayList of Customer objects that it returns to the caller.

Add GetCustomers to clsDataAccess:

Public Function GetCustomers() As ArrayList

    Try
        ' Connect to the data source.
        Dim Conn As SqlConnection = New SqlConnection(sConn)
        Conn.Open()

        ' Create a data reader for the Customer IDs and Names.
        Dim cmd As SqlCommand = New SqlCommand( _
            "SELECT CustomerID, CompanyName FROM Customers", Conn)
        Dim rdr As SqlDataReader = cmd.ExecuteReader

        ' Return the Customer IDs and Names in the dataset as
        ' a one-dimensional array.
        Dim CustArray As New ArrayList
        Do While rdr.Read
            CustArray.Add(New Customer(rdr.GetString(0), _
                rdr.GetString(1)))
        Loop
        rdr.Close()
        Conn.Close()

        Return CustArray

    Catch ex As Exception
        MsgBox(ex.Message)
        Return Nothing
    End Try

End Function

Add the following class-level variables to the OfficeCodeBehind class:
```
Private CustomerID As String
Private CustList As ArrayList
```
This next function, AddCustomerID, calls GetCustomers in clsDataAccessto acquire the ArrayList ofCustomers. Then, AddCustomerID creates a new instance of the customer form and sets the data source for the ComboBox to the ArrayList that was acquired.

Add the AddCustomerID function to the OfficeCodeBehind class:

Private Function AddCustomerID(ByVal rng As Excel.Range) As Boolean

    ' Retrieve the customers ArrayList.
    CustList = da.GetCustomers

    ' Set the ComboBox DataSource to the ArrayList with the member
    ' to display in the list as the customer name and the member to
    ' return upon selection as the customer id.
    Dim frm As New CustomerForm
    With frm.cboCustomers
        .DataSource = CustList
        .DisplayMember = "Name"
        .ValueMember = "ID"
    End With

    ' Show the Customer form. If the user clicks OK, display the
    ' selected customer id in the designated range and return
    ' True; otherwise, if the user clicks Cancel, return False.
    Dim result As DialogResult = frm.ShowDialog()
    If result = DialogResult.OK Then
        rng.Value = frm.CustomerID
        frm = Nothing
        Return True
    End If

    Return False

End Function

The customer form is shown when the workbook opens; the selected customer ID is then added at cell B1 of the worksheet.

Append the following code to the Open event for the workbook:

' Retrieve the CustomerID. If no customer is selected, then
' quit the solution.
Dim bCustomerSelected As Boolean = AddCustomerID(Sheet1.Range("B1"))
If Not bCustomerSelected Then
    ThisWorkbook.Saved = True
    ThisApplication.Quit()
    Return
End If

You might recall that the code for submitting new orders presently hard-codes the customer ID to 'ALFKI'.

Modify the call to AddNewOrder in the Click event of CBarSubmitButton so that it uses the customer ID that is inserted at cell Sheet1!B1.

Replace the following line in the Click event of CBarSubmitButton:
```
Dim nOrderID As Int32 = da.AddNewOrder("ALFKI", arrOrders)
```
With:
```
Dim nOrderID As Int32 = da.AddNewOrder( _
    Convert.ToString(Sheet1.Range("B1").Value), arrOrders)
```

Testing the Application

Try it out:

Press F5 to build and run the project.
When the workbook opens, the customer form appears.
Select any customer from the list and click OK.
Create a new order and click the Submit Order CommandBar button to create a new order for your selected customer.

Exercise 3: Importing XML Data into Excel

In this exercise, you extract XML from an ADO.NET dataset for the purpose of creating an XML map in a workbook. At run time, you create a new list from the XML map, set the XML Path Language (XPath) values for each column in the list, and finally import the XML data into the workbook.

You should begin this exercise with the OrderForm project you created in Exercise 2.

Task 1: Import XML Data into Excel

In this exercise, you add functionality to the CBarViewButton command bar button. When the button is clicked, you import the category and product information stored in the dataset into a new workbook. The import uses the XML representation of the dataset for creating the list in the workbook.

To import data into Excel

Add code to the Click event for CBarViewButton:

Private Sub CBarViewButton_Click( _
    ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, _
    ByRef CancelDefault As Boolean) Handles CBarViewButton.Click
    Try
        ' Store an XML string representation of the dataset.
        Dim sXML As New System.IO.StringWriter
        ds.WriteXml(sXML)

        ' Add an XML map to a new workbook with one worksheet.
        Dim Bk As Excel.Workbook, Sh As Excel.Worksheet
        Bk = ThisApplication.Workbooks.Add( _
            Excel.XlWBATemplate.xlWBATWorksheet)
        Sh = CType(Bk.Worksheets(1), Excel.Worksheet)
        ThisApplication.DisplayAlerts = False
        Dim Mp As Excel.XmlMap = Bk.XmlMaps.Add(sXML.ToString, _
            "NewDataSet")
        ThisApplication.DisplayAlerts = True

        ' Create a new list with columns for CategoryName, ProductName,
        ' QuantityPerUnit and UnitPrice.
        Dim lo As Excel.ListObject = Sh.ListObjects.Add
        With lo.ListColumns(1)
            .XPath.SetValue(Mp, "/NewDataSet/Categories/CategoryName")
            .Name = "Category"
        End With
        With lo.ListColumns.Add
            .XPath.SetValue(Mp, _
                "/NewDataSet/Categories/Products/ProductName")
            .Name = "Product"
        End With
        With lo.ListColumns.Add
            .XPath.SetValue(Mp, _
                "/NewDataSet/Categories/Products/QuantityPerUnit")
            .Name = "Quantity Per Unit"
        End With
        With lo.ListColumns.Add
            .Range.NumberFormat = "$#,##0.00"
            .XPath.SetValue(Mp, _
                "/NewDataSet/Categories/Products/UnitPrice")
            .Name = "Unit Price"
        End With

        ' Import the XML into the list.
        Bk.XmlImportXml(sXML.ToString, Mp)
        sXML.Close()

        ' Format the window for the new workbook.
        With Bk.Windows(1)
            .DisplayHeadings = False
            .DisplayWorkbookTabs = False
            CType(Sh.Rows(2), Excel.Range).Select()
            .FreezePanes = True
            .WindowState = Excel.XlWindowState.xlNormal
            .Width = Convert.ToDouble(Sh.Range("A1:E1").Width)
            .Caption = "Northwind Products"
        End With

        ' Protect the worksheet and the workbook and set 
        ' the workbook's Saved property to True so that 
        ' you are not prompted to save when closing the 
        ' workbook.
        Sh.Protect()
        Bk.Protect()
        Bk.Saved = True

    Catch ex As Exception
        MessageBox.Show("Error display XML in list: " & ex.Message, _
            "Order Form", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try

End Sub

Testing the Application

Try it out:

Press F5 to build and run the project.
Click the View Categories and Products command bar button.

A new worksheet containing the category and product data appears.
Examine the contents of the new worksheet and then quit Excel.

Next Steps

In Lab 4: Securing Office Solutions that Use Managed Code Extensions, you can examine the Microsoft .NET Framework security requirements for Word and Excel solutions that use managed code extensions and learn how to set up a Visual Studio Tools for Office solution to run from a local computer, a network share, or a Web server.

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

Lab 3: Adding Functionality to Excel Workbook Solutions in Visual Studio .NET 2003

Contents

Introduction

Prerequisites

Getting Started

Exercise 1: Handling Excel Events

Task 1: Populate a Worksheet with the Data from a Dataset

Testing the Application

Task 2: Modify the Workbook at Design Time

Task 3: Add a Data Validation List to the Workbook at Run Time

Testing the Application

Task 4: Handle the Change Event of the Worksheet

Testing the Application

Task 5: Build a Command Bar and Command Bar Controls

Testing the Application

Task 6: Insert New Order Data into the Database

Testing the Application

Exercise 2: Using a Windows Form with Your Excel Workbook Project

Task 1: Build a Windows Form in an Excel Workbook Project

Testing the Application

Exercise 3: Importing XML Data into Excel

Task 1: Import XML Data into Excel

Testing the Application

Next Steps

Additional resources