Populating an Excel 2003 Worksheet with Multiple Related Records from a Windows Form

  • Article

 

Frank Rice
Microsoft Corporation

August 2005

Applies to:
    Microsoft Office Excel 2003
    Microsoft Office Access 2003
    Microsoft Visual Studio 2005 Tools for the Microsoft Office System

Summary: Learn to display Microsoft Windows Forms from a Microsoft Office Excel 2003 workbook, display related records, and write that information into formatted worksheets. This article also describes methods for navigating these related data tables within a dataset and returning related records based on a selected record. (20 printed pages)

Download OfficeExcelRelatedRecordsInsert.exe.

Contents

Introduction
Creating Excel Workbook Projects in Visual Studio .NET
Adding Windows Forms to Projects
Connecting to the Database
Creating Data Adapters and Connections
Generating Datasets
Creating Table Relationships
Building the Form
Building the Workbook
Initializing Windows Forms
Filling Tables with Data
Navigating Related Records
Displaying Related Records
Navigating Related Records in Multiple Tables
Filling Rich Text Boxes
Navigating Many-to-Many Relationships
Formatting Worksheets
Displaying Data in Worksheets
Conclusion
Additional Resources

Introduction

Microsoft Visual Studio 2005 Tools for the Microsoft Office System (Visual Studio 2005 Tools for Office) provides a number of tools for developers who want to write code behind Microsoft Office Word 2003 and Microsoft Office Excel 2003 to address any number of business scenarios. For example, you may want to write business logic that manages budget figures in an Excel worksheet, or populate a table in a Word document with sales figures obtained using a Web service. If you are already using Microsoft Visual Studio .NET, you can use Visual Studio 2005 Tools for Office to address these and many other scenarios.

In this article, you build a solution that targets Excel. In building the application outlined here, you use many areas of Visual Studio .NET and the Microsoft Office System including the Visual Studio Server Explorer, Microsoft Windows Forms, OLE DB data access, and Microsoft Office automation.

Specifically, this article describes several procedures as it walks through the scenario:

  • Creating a project in Visual Studio .NET
  • Adding a Windows Form to the project
  • Displaying customer data in the Windows Form
  • Displaying related records based on user selection
  • Populating a worksheet with order details

In addition, this article describes some related tasks:

  • Adding to data table expression columns based on related data
  • Compiling aggregate information about related data
  • Creating a formatted worksheet
  • Testing the application

Sound like fun? Let's get started.

Creating Excel Workbook Projects in Visual Studio .NET

In this step, you create an Excel workbook project.

To create a project in Visual Studio .NET

  1. In Visual Studio .NET, on the File menu, point to New, and then click Project to display the New Project dialog box.

  2. In the Project Types pane, expand Microsoft Office System Projects, and then select Visual Basic Projects.

  3. In the Templates pane, select Excel Workbook.

  4. Name the project OfficeExcelRelatedRecordInsert, and then click OK.

    The Microsoft Office Project Wizard appears.

  5. Make sure that Create New Document is selected and then click Finish.

Visual Studio .NET creates an Excel workbook at the specified location and adds the OfficeExcelRelatedRecordInsert project to Solution Explorer.

Adding Windows Forms to Projects

In this step, you create a Windows Form to select and display the related records.

  1. In the Solution Explorer, select the project OfficeExcelRelatedRecordInsert.

  2. On the Project menu, click Add Windows Form.

  3. Name the form SelectRecords.vb and then click Open.

    The new form opens in the designer and the SelectRecords.vb file is added to the Solution Explorer.

  4. On the File menu, select Save All to save the project.

Connecting to the Database

This database connection allows you to communicate with the data source from within the integrated development environment.

To connect to the Northwind sample database in Server Explorer

  1. In Visual Studio .NET, on the View menu, click Server Explorer to establish a data connection to the Northwind sample database.

  2. On the Tools menu, select Connect to Database.

  3. In the Data Link Properties dialog box, click the Provider tab, choose Microsoft Jet 4.0 OLE DB Provider, and then click Next.

  4. In the Data Link Properties dialog box, click the Connection tab, and provide a path to the Northwind sample database. Typically, the database is installed at C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb.

  5. Type the username and password required to log on to the database.

  6. Click OK to establish the data connection.

    The Data Link Properties dialog box closes, and the new data connection appears beneath the Data Connections node, named for the server and database accessed.

    Note   Storing connection-string details (such as the server name, user name, and password) can have implications for the security of your application. Using Windows Integrated Security is a more secure way to control access to a database. For more information, search Visual Studio Help for Database Security.

  7. Expand the Northwind data connection in Server Explorer until you can see all the tables.

Creating Data Adapters and Connections

This step creates the connection and data adapters that connect and exchange data between your application and the data source.

To create the data adapters and connections

  1. Drag the Customers table from the Server Explorer onto the form. A connection and data adapter appear in the component tray.

    Note   If prompted to include the password in the connection string, you should be aware of the security concerns involved because the password is saved as clear text. It is recommended that you not include the password. You are prompted for a user name and password when connecting to the database.

  2. Right-click OleDbConnection1, and then click Properties.

  3. In the drop-down list at the top of the Properties window, select OleDbConnection1 and set the Name property to dcNorthwind.

  4. Repeat the previous steps for OleDbDataAdapter1 and set the Name property to daCustomers.

  5. From Server Explorer, drag the Orders table onto the form. A second data adapter appears in the component tray.

  6. Select the new data adapter and set the Name property to daOrders.

Generating Datasets

Next, using the data adapters that you just added to the form, we generate a dataset****that includes the Customers and Orders tables.

  1. On the Data menu, select Generate Dataset. The Generate Dataset dialog box appears.
  2. Click New and name the dataset dsNorthwind.
  3. Select both the Customers and Orders tables.
  4. Select the Add this dataset to the designer check box, and then click OK.

A file named dsNorthwind.xsd is added to the project in Solution Explorer, and an instance of the dataset appears in the component tray.

Creating Table Relationships

Generating a dataset does not automatically create a relationship between the tables that are in it. You can create the relationship programmatically or visually, using the XML Designer. This example uses the XML Designer.

To create the relationship between the Customers and Orders tables

  1. In Solution Explorer, double-click the dsNorthwind.xsd file. The file opens in the XML Designer.

  2. On the View menu, click Toolbox and then click the XML Schema tab. Drag a Relation onto the Orders table.

  3. In the Edit Relation dialog box, set the following values:

    Table 1. Relation values

    Item Setting
    Name CustomersOrders
    Parent Customers
    Child Orders
    Key Fields CustomerID
    Foreign Key Fields CustomerID

  4. Click OK to create the relation and close the dialog box.

  5. Save the project.

Building the Form

This application uses a combo box, list box, and rich text box for selecting and displaying data. It also uses a button to add the records and related records to the worksheet.

SelectRecords form

Figure 1. SelectRecords form

To add controls to select and display the data

  1. In Solution Explorer, right-click SelectRecords.vb, and then select View Designer.

  2. On the left half of the form, add a ListBox control, and set its Name property to lbOrders.

  3. On the right half of the form, add a RichTextBox control, and set its Name property to rtbDetails.

  4. Above the list box, add a ComboBox control, and set its Name property to cbCustomers. Later, you may want to resize the length of the control to fit the company names.

  5. Below the list box and the rich text box, add a Button control, set its Name property to btnDisplay, and its Text property to Display Records.

    You add code for the button later in this article.

  6. Click to return to the Windows Form Designer and, from the Properties window, make the following settings to the combo box.

    Table 2. Combo box settings

    Property Settings
    DataSource DsNorthwind1
    DisplayMember Customers.CompanyName
    ValueMember Customers.CustomerID

    Note   You add code to the combo box and other controls later in this article.

Building the Workbook

Now turn your attention to the workbook.

To display the form when the application starts

  1. In Solution Explorer, double-click ThisWorkbook.vb to open the workbook in the Code Editor.

  2. Add code to the ThisWorkbook_Open event handler to declare a variable for the form and then show the form. Use the Me keyword as a parameter for the SelectRecords procedure to create a reference to the current OfficeCodeBehind object.

    Dim inputForm As New SelectRecords(Me)
inputForm.Show()

  3. Create a method called echoStringToCell that writes text to the worksheet. This method is called from the form, and passes the record data to the appropriate cells in the worksheet.

    Public Sub EchoStringToCell(ByVal str As String, _
        ByVal row As Integer, ByVal column As Integer)
    Dim sheet1 As Excel.Worksheet

    ' Start Excel to the worksheet and display the records.
    sheet1 = CType(Me.thisWorkbook.Worksheets(1), Excel.Worksheet)
    CType(sheet1.Cells.Item(row, column), Excel.Range).Value2 = str
End Sub

  4. Save the project and return to the form's code window.

Initializing Windows Forms

  1. Before the Windows Form code file (the Public Class SelectRecords statement) in the code window, add the following statements. These statements allow you to add references to objects within a class without having to use the fully qualified name:

    Imports System
Imports System.IO

  2. Just after the Inherits System.Windows.Forms.Form statement, declare a class-level variable for OfficeCodeBehind, which enables the Windows Form's constructor Sub New() to initialize the form to include a reference to the current workbook:

    Private excelCode As OfficeCodeBehind

  3. Open the Windows Form Designer generated code region and replace the default code for the Sub New() constructor with the following:

    Public Sub New(ByVal targetExcelCode As OfficeCodeBehind)
    MyBase.New()
    ' This call is required by the Windows Form Designer.
    InitializeComponent()
    ' Adds any initialization after the InitializeComponent() call.
    Me.excelCode = targetExcelCode
End Sub

  4. Close the Windows Form Designer generated code region.

Filling Tables with Data

The next step is to set up the combo box and add code to the application to fill the tables with data.

To fill the Customers and Orders tables in the dataset (dsNorthwind1) with data

  1. Double-click an empty area on the form to create an event handler for the SelectRecords_Load event.

  2. Replace the default code with the following:

    Private Sub SelectRecords_Load(ByVal sender As System.Object, ByVal e 
As System.EventArgs) Handles MyBase.Load
' Create an expression column named Total in the Order_Details 
' table.
'Dim dcTotal As DataColumn = New DataColumn("Total")
'dcTotal.DataType = System.Type.GetType("System.Decimal")
'dcTotal.Expression = "UnitPrice * Quantity"
'DsNorthwind1.Order_Details.Columns.Add(dcTotal)

' Create an expression column named OrderTotal in the Orders 
' table.
'Dim dcOrderTotal As DataColumn = New DataColumn("OrderTotal")
'dcOrderTotal.DataType = System.Type.GetType("System.Decimal")
'dcOrderTotal.Expression = "Sum(Child.Total)"
'DsNorthwind1.Orders.Columns.Add(dcOrderTotal)

' Turn off constraints in the dataset.
DsNorthwind1.EnforceConstraints = False

' Fill the tables with data.
'daOrderDetails.Fill(DsNorthwind1)
'daProducts.Fill(DsNorthwind1)

daOrders.Fill(DsNorthwind1)
daCustomers.Fill(DsNorthwind1)

' Turn constraints back on.
DsNorthwind1.EnforceConstraints = True

End Sub

  3. Save the project.

  4. Press F5 to run the application. Excel starts and the form's combo box now contains a list of company names.

  5. Close the form and workbook.

One of the more common tasks when working with relational databases in Microsoft Visual Basic .NET is to access data between two tables in a dataset that participates in a one-to-many relationship. One way to accomplish this is by using a DataRelation object to navigate from one DataTable to another within a DataSet. This allows you to retrieve all the related DataRow objects in one DataTable when given a single DataRow from a related DataTable. Another technique is to use the GetChildRows or GetParentRow methods. After you select a data row, you can return its related records by calling the GetChildRows or GetParentRow method and passing it the appropriate data relation. For example, after establishing a DataRelation between a table of customers and a table of orders, you can retrieve all the order rows for a particular customer row using DataRow.GetChildRows. The GetChildRows method returns data in an array of DataRow objects, whereas the GetParentRow method returns a single data row. This is the technique used here.

To create an event handler to get orders for a selected customer

  1. Click the SelectRecords.vb [Design] tab to view the View Designer.

  2. Double-click the combo box to create an event handler for the SelectedIndexChanged event.

  3. Replace the default code with the following:

    Private drSelectedCustomer As DataRow

Private Sub cbCustomers_SelectedIndexChanged _
(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles cbCustomers.SelectedIndexChanged

' Declare a string to hold the selected customer's CustomerID.
Dim SelectedCustomerID As String
SelectedCustomerID = cbCustomers.SelectedValue.ToString()

' Declare a data row to hold the selected customer's record.
drSelectedCustomer = DsNorthwind1.Customers.FindByCustomerID _
    (SelectedCustomerID)

' Declare an array of data rows to hold the related records.
Dim draOrders As DataRow()
draOrders = drSelectedCustomer.GetChildRows("CustomersOrders")

' Display the length of the array (number of orders)
' and the CustomerID in the form caption.
Me.Text = draOrders.Length.ToString() & " orders for " & _
    SelectedCustomerID

End Sub

  4. Save the project.

  5. Press F5 to run the application.

  6. Select a different customer, and examine the form's caption.

    The total number of orders is displayed for the selected customer, as well as the selected customer's Customer ID.

  7. Close the form and workbook.

After running the procedure in the previous section, you now have the related records of the selected customer (stored in the array of data rows). In this section, you display them for interaction with the user. The application iterates through the array of data rows returned from the GetChildRows method, adding the OrderID field of each related record as an individual item in the list box.

  1. In Solution Explorer, right-click SelectRecords, and then select View Code.

  2. In the SelectedIndexChanged event handler for the combo box created in the previous procedure, add the following code to the handler (after the code already in the handler):

    ' When the selected customer changes, clear the list of orders.
lbOrders.Items.Clear()
rtbDetails.Text = ""

' Add the OrderID of each related order to the list box.
Dim drOrder As DataRow
For Each drOrder In draOrders
    lbOrders.Items.Add(drOrder("OrderID"))
Next

  3. Save the project.

  4. Run the application.

    A list of orders appears in the list box. Select a different customer in the combo box, and the list of orders is updated.

  5. Close the form and workbook.

Navigating three or more tables is not much more difficult than working with two. To illustrate working with more than two tables in this application, you add the OrderDetails table and the Products table to the dsNorthwind dataset. When an order is selected in the list box, the details from that order are displayed in the rich text box.

In the following steps, you add two additional tables to the existing dataset and create additional DataRelation objects to tie all the tables together.

To add the OrderDetails and Products tables to the dsNorthwind dataset

  1. In Solution Explorer, right-click SelectRecords.vb, and then select View Designer.
  2. From Server Explorer, drag the Order Details table onto the form. A new data adapter appears in the component tray.
  3. Select the new data adapter and set its Name property to daOrderDetails.
  4. From Server Explorer, drag the Products table onto the form. A new data adapter appears in the component tray.
  5. Select the new data adapter and set its Name property to daProducts.
  6. Close the Server Explorer.

The new tables are only added to the form, so you must regenerate the dataset whenever additional tables are added.

To regenerate the dsNorthwind dataset

  1. On the Data menu, select Generate Dataset.

  2. Click Existing and then select the dsNorthwind dataset.

  3. Select all four tables (Customers, Orders, OrderDetails, and Products).

  4. Clear the Add this dataset to the designer check box, and then click OK. The dataset is regenerated with the additional tables.

    Note   If you get a dialog box that says "The file has been modified outside of the source editor. Do you want to reload it?" click Yes.
    Generating a dataset does not automatically create relationships between the tables that are in it.

To create the relationships

  1. In Solution Explorer, double-click the dsNorthwind.xsd file. The file opens in the XML Designer.

  2. On the View menu, click Toolbox and then click the XML Schema tab. Drag a Relation object onto the OrderDetails table.

  3. In the Edit Relation dialog box, set the following values:

    Table 3. Relation values

    Item Setting
    Name OrdersOrderDetails
    Parent Orders
    Child OrderDetails
    Key Fields OrderID
    Foreign Key Fields OrderID

  4. Click OK to create the relation and close the dialog box.

  5. On the View menu, click Toolbox and then click the XML Schema tab. Drag a Relation object onto the OrderDetails table.

  6. In the Edit Relation dialog box, set the following values:

    Table 4. Relation values

    Item Setting
    Name ProductsOrderDetails
    Parent Products
    Child OrderDetails
    Key Fields ProductID
    Foreign Key Fields ProductID

  7. Click OK to create the relation and close the dialog box.

  8. Save the project.

The OrderDetails and Products tables are added to the dataset, but you still need to add code to fill them with data at run time.

To fill the tables with data

  1. In Solution Explorer, right-click SelectRecords.vb, and then select View Code.

  2. In the SelectRecords_Load event handler, uncomment the following statements located between the Fill the tables with data comment and the daOrders.Fill(dsNorthwind1) line:

    daOrderDetails.Fill(dsNorthwind1)
daProducts.Fill(dsNorthwind1)

Filling Rich Text Boxes

In this section, you add code to the project that displays all of the order details in the rich text box when a specific order is selected in the list box.

The following code calls the GetChildRows method based on the order selected in the list box. All of the related records in the OrderDetails table are assigned to the array of data rows named draOrderDetails. The contents of each data row are displayed in the rich text box.

To set the rich text box to display all the order details

  1. In Solution Explorer, right-click SelectRecords.vb and then select View Designer.

  2. Double-click the list box to create an event handler for the SelectedIndexChanged event of the list box lbOrders.

  3. Replace the default code with the following:

    Private details As String
Private OrderTotals As String = ""

Private Sub lbOrders_SelectedIndexChanged _
 (ByVal sender As System.Object, ByVal e As System.EventArgs) _
     Handles lbOrders.SelectedIndexChanged

' Clear the rich text box when a new order is selected.
rtbDetails.Clear()

' Declare an integer to hold the selected OrderID.
Dim SelectedOrderID As Integer

' Cast the selected item as an integer.
SelectedOrderID = CType(lbOrders.SelectedItem, Integer)

' Declare a data row to hold the selected Order record.
Dim drSelectedOrder As DataRow
drSelectedOrder = _
    DsNorthwind1.Orders.FindByOrderID(SelectedOrderID)

' Declare an array of data rows to hold the related records.
Dim draOrderDetails() As DataRow
draOrderDetails = _
    drSelectedOrder.GetChildRows("OrdersOrderDetails")

' Create the OrdersTotal entry.
OrderTotals = "Order Total: " & String.Format("{0:c}", _
    DsNorthwind1.Orders.FindByOrderID(CType(lbOrders.SelectedItem, _
    Integer))("OrderTotal")) & ControlChars.CrLf

details = ""
Dim drDetails As DataRow
Dim dcDetails As DataColumn

' Cycle through the order detail entries by row 
' and then by column.
For Each drDetails In draOrderDetails

            For Each dcDetails In drDetails.Table.Columns
               details &= dcDetails.ColumnName & ": "
               If dcDetails.ColumnName.ToString = "Total" Then
                details &= String.Format("{0:c}", drDetails(dcDetails))
               Else
                details &= drDetails(dcDetails).ToString()
               End If
               details &= ControlChars.CrLf
            Next
            details &= ControlChars.CrLf
Next

' Display the results in the rich text box.
rtbDetails.Text = details

End Sub

  4. In the SelectRecords_Load event handler, uncomment the following statements:

    Dim dcTotal As DataColumn = New DataColumn("Total")
dcTotal.DataType = System.Type.GetType("System.Decimal")
dcTotal.Expression = "UnitPrice * Quantity"
DsNorthwind1.Order_Details.Columns.Add(dcTotal)

    This code illustrates creating calculated columns containing the results of mathematical operations. As shown here, you can perform these calculations on values taken from existing columns. The code creates a column named Total that is added to the OrderDetails table and contains the value returned by the expression UnitPrice * Quantity, which is the total dollar value of the order.

  5. Uncomment the following statements:

    Dim dcOrderTotal As DataColumn = New DataColumn("OrderTotal")
dcOrderTotal.DataType = System.Type.GetType("System.Decimal")
dcOrderTotal.Expression = "Sum(Child.Total)"
DsNorthwind1.Orders.Columns.Add(dcOrderTotal)

    This code illustrates using aggregate information in an expression column. The code adds a new column named OrderTotal to the Orders table. The column uses the Sum function to return the total dollar amount of all child order-detail records, based on the order selected in the list box (lbOrders). This value is used in the worksheet later in this article.

  6. Save the project.

  7. Run the application.

  8. Select an order in the list box. The details are displayed in the rich text box. Notice the Totals entry beneath each order.

  9. Select a different order in the list box. The order details in the rich text box are updated.

In a many-to-many relationship, many records in one table can have many matching records in another table. Many-to-many relationships are set up by using a linking table containing just the key fields of the two tables to be linked. This setup allows one record in table A to link to multiple records in table B and the reverse, using the link table as an intermediary.

In the Northwind database, the Orders and Products tables are related in such a way. There may be orders that contain many products, just as there may be products that are sold on many orders. These two tables are connected through the OrderDetails table, which uses columns from each table to relate the data.

To navigate the many-to-many relationship between the Order Details table and the Products table, you access the products based on the individual record in the Order Details table, which returns the product name and displays it in the order details.

You use the GetParentRow method to access the product name from the Products table. Calling the GetParentRow method returns a single data row, whereas calling the GetChildRows method returns an array of rows.

To get the product name from the order details record

  1. In Solution Explorer, right-click SelectRecords, and then select View Code.

  2. In the SelectedIndexChanged event handler for the list box (lbOrders), add the following code between the For Each lines:

    details &= "Product name: " & _
    CType(drDetails.GetParentRow("ProductsOrderDetails") _
    ("ProductName"), String) & ControlChars.CrLf

  3. Save the project.

  4. Run the application.

  5. Select an order in the list box.

    The product name is displayed with the details in the rich text box. There is now related data from all four tables displayed on the form.

  6. Select a different order in the list box. The order details in the rich text box are updated.

  7. Close the form and workbook.

Formatting Worksheets

Next you format the worksheet to properly display the customer information and related records. The code for the button as detailed in the following section contains hard-coded values to allow it to fit into a pre-formatted worksheet. You will probably want to change those values to fit into your custom worksheet.

To format the worksheet associated with this project

  1. Navigate to the location where you saved the OfficeExcelRelatedRecordInsert project.
  2. Open the OfficeExcelRelatedRecordInsert.xls workbook.
  3. Close the Windows Form if it is displayed.
  4. Highlight columns A through E.
  5. On the Format menu, click Cells.
  6. On the Alignment tab, click the Vertical drop-down list, and select Top. Click OK.
  7. Highlight cells A1 through E2.
  8. Again on the Format menu, click Cells.
  9. On the Patterns tab, select a color for the highlighted area.
  10. Add any additional formatting you want. Close and save the workbook.

Displaying Data in Worksheets

Now add code to the button that you added to the form previously to display the records and related data in the worksheet.

  1. Double-click the button to open the code file with the button's Click event handler added.

  2. Replace the default code with the following:

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

' Some of these items are hard-coded for display in a preformatted
' form in specific columns/rows. You can change these settings
' to fit your own preformatted forms.
Dim row As Integer = 1
Dim column As Integer = 1

' Add the contact name to the worksheet.
Me.excelCode.EchoStringToCell("Contact Name: " & vbCrLf & _
        drSelectedCustomer.ItemArray(3), row, column)

row += 2
column += 1
' Add the customerID to the worksheet.
Me.excelCode.EchoStringToCell("Customer ID: " & vbCrLf & _
        drSelectedCustomer.ItemArray(6), row, column)

column += 1
' Add the company name to the worksheet.
Me.excelCode.EchoStringToCell("Company Name: " & vbCrLf & _
        drSelectedCustomer.ItemArray(2), row, column)

column += 1
' Add the current date to the worksheet.
Me.excelCode.EchoStringToCell("Date: " & Date.Today, 1, column)

Dim sr As New StringReader(details)
Dim tempStr As String = ""
Dim results As String = ""

Do
    tempStr = sr.ReadLine

    If tempStr = "" Then 'end of record.
        'Display the record.
        Me.excelCode.EchoStringToCell(results, row, column)
        row += 1
        ' Clear the results variable to get ready for the next read.
        results = ""
        ' Check to see if we are at the end of the records.
        tempStr = sr.ReadLine

            If tempStr = "" Then 'end of orders.
                results = ""
            End If
    End If
    ' If we are at the end of records, this will add a line feed, 
    ' carriage return to the blank line which we can then test for.
    results &= tempStr & vbCrLf
    tempStr = ""
Loop Until results = vbCrLf

column += 1
' Display the order totals.
Me.excelCode.EchoStringToCell(OrderTotals, row, column)

' Close the form and release its resources.
Me.Dispose()

End Sub

  3. Save the project.

  4. Run the application.

  5. Follow the steps to display related records in the rich text box.

  6. Click the Display Record button.

  7. The record information, such as Contact Name and Company Name, are displayed in the formatted worksheet along with related orders and total order amount.

The formatted worksheet with records for customer AROUT

Figure 2. The formatted worksheet with records for customer AROUT

In a production environment, after you are satisfied with your worksheet and the project compiles without error, you are ready to deploy your application. It is also important that you give careful consideration to adding error handling code to the project. For more information on deployment, see How to: Deploy Office Documents and Assemblies. For more information on error handling, see the Visual Studio topic Introduction to Exception Handling.

Conclusion

Visual Studio 2005 Tools for Office provides many tools and abstractions that allow you to take advantage of the productivity enhancements in the Microsoft .NET Framework to extend Word and Excel using Visual Basic .NET. As you saw in this article, letting these tools work for you frees you up to focus your energies on the logical development of applications in a deliberate, step-by-step manner.

Additional Resources

For more information about the tools and techniques described in this article, see the following resources: