Visual Studio 2005 Tools for Office Hands-on Labs for Excel 2003

Article
01/19/2007

Microsoft Corporation

Published June 2005
Updated March 2006

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

Summary: Use these hands-on labs to gain experience in building an asset allocation application and a debt consolidation application using Microsoft Visual Studio 2005 Tools for the Microsoft Office System and Microsoft Office Excel 2003. (29 printed pages)

Download VSTO2005ExcelLabs.msi.

Download the VSTO2005ExcelLabs.msi sample file. (807 KB)

Setting Up the Labs
Lab 1-Connecting Data and Controls in an Excel Workbook
Lab 2-Using Controls in the Actions Pane
Lab 3-Connecting Data in an Excel Workbook and Actions Pane
Conclusion
Additional Resources

Setting Up the Labs

To work through the lab exercises, you must install:

Microsoft Windows 2000 or later.
Microsoft Visual Studio 2005 Tools for the Microsoft Office System (Visual Studio 2005 Tools for Office).
Microsoft Office Professional Edition 2003 SP1 with the Microsoft Excel Analysis ToolPak add-in installed.

Note Only Microsoft Office Excel is required, but be sure to choose Run from My Computer for the Excel and smart tag primary interop assemblies (PIAs). Additionally, be sure to choose Run from My Computer for the Excel Analysis Toolpak add-in.
Microsoft SQL Server 2005 Express Edition.

To prepare Excel for the labs

Start Excel.
On the Tools menu, point to Macro, and then click Security.
On the Security Level tab, select Medium, and then click OK.
On the Tools menu, click Add-Ins.
In the Add-Ins available list, select Analysis Toolpak, and then click OK.
Exit Excel.

Note When applicable, instructions in the labs refer to files by a full path; it is assumed that the download files are extracted to \My Documents\Visual Studio 2005\Projects. The files needed to complete the labs are in a subfolder named VSTO 2005 Excel Labs\Starter Files. You can find a completed version of the labs in the VSTO 2005 Excel Labs\Completed Labs subfolder.

Lab 1—Connecting Data and Controls in an Excel Workbook

This lab demonstrates how to connect to a Microsoft SQL Server data source for the purpose of binding controls in a Microsoft Office Excel workbook to the data source. You also use Windows Forms controls on the workbook to navigate records in the data source, and add smart tag expressions and actions.

Estimated time to complete:

Exercise 1: Connecting and Binding Data—15 minutes
Exercise 2: Using Windows Forms Controls—15 minutes
Exercise 3: Creating Smart Tags—20 minutes

Exercise 1: Connecting and Binding Data

To create an Excel Workbook project

On the File menu, click New, and then click Project.
In the list of Project Types, expand Visual Basic or Visual C# and click Office.
In the list of project Templates, select Excel Workbook.
In the project Name box, type AssetAllocations.
In the Location box, type a location, such as C:\VSTO2005\Labs. (If there is no Location box, the Save Project dialog box appears in Step 11 instead.)
Click OK. The Visual Studio Tools for Office Project Wizard appears.
In the wizard, click Copy an existing document.
Click Browse.
Navigate to the following directory and then click Open:

\My Documents\Visual Studio 2005\Projects\VSTO 2005 Excel Labs\Starter Files\Asset Allocations.xls
Click OK. If this is your first Visual Studio 2005 Tools for Office project for Excel, you may see the dialog box shown in Figure 1. If you do, click OK to acknowledge and close the dialog box.

Figure 1. Access to the Visual Basic for Applications project system is disabled by default (click picture to see larger image)
On the File menu, click Save All.
If you did not have a Location field in Step 4, the Save Project dialog box appears. In the project Location box, type a location, such as C:\VSTO2005\Labs, and click Save.

To create the sample database by using an SQL script

This lab uses a SQL Server Express database that is included in the starter files. You use an SQL script to add the sample database to your instance of SQL Server. The name of the new database is VSTO2005Lab.

On the Start menu, click Run. Type cmd and click OK. A Command Prompt window opens.
Go to the folder where the starter files reside, \My Documents\Visual Studio 2005\Projects\VSTO 2005 Excel Labs\Starter Files.

Run the batch procedure.

osql -E -S .\SQLExpress -i VSTO2005Lab.sql

Close the Command Prompt window.

To add a data source to your project

On the Data menu, click Show Data Sources. The Data Sources window appears.
Click Add New Data Source in the Data Sources window. You see the Data Source Configuration Wizard.
Select Database for the data source type and click Next.
Click New Connection. If you have not created a data connection before, the Choose Data Source dialog box appears. If you have created a data connection, you see the Add Connection dialog box shown in Figure 3 instead, and you can skip to Step 7.
In the Choose Data Source dialog box, select Microsoft SQL Server, as shown in Figure 2.

Figure 2. Choose Data Source dialog box
Click Continue. You see the Add Connection dialog box shown in Figure 3.

Figure 3. The Add Connection dialog box (click picture to see larger image)
Create the connection:
1. If the value in the Data source box is not Microsoft SQL Server (SqlClient), click Change and choose the SQL Server data source.
2. In the Server name box, type .\SQLExpress.
3. Select Use Windows Authentication for the authentication mode.
4. In the Connect to a database section, select VSTO2005Lab in the Select or enter a database name list.
5. Click Test Connection to confirm that the connection is valid and then click OK to add the connection. The new connection has the default name YourServerName.VSTO2005Lab.dbo.
Click Next.
Save the connection using the default name, VSTO2005LabConnectionString.
Click Next.
In the list of database objects, select the Tables check box and the Views check box and click Finish.

To add a relation to the data source

In the Data Sources window, select VSTO2005LabDataSet and click Edit DataSet with Designer (at the top of the Data Sources window).
On the Data menu, point to Add and then click Relation. The Relation dialog box appears.
Set the following fields for the new relation:
- Name is Customers_PortfolioView
- Parent Table is Customers
- Child Table is PortfolioView
- Key Column is CustomerID
- Foreign Key Column is CustomerID
The completed dialog box looks like Figure 4.

Figure 4. Completed Relation dialog box (click picture to see larger image)
Click OK to add the new relation.
The VSTO2005LabDataSet designer window now looks like Figure 5.

Figure 5. New relation between Customers and PortfolioView (click picture to see larger image)

To bind NamedRange controls to fields in the data source

In Solution Explorer, right-click Sheet1.vb and then click View Designer on the shortcut menu.
In the Data Sources window, expand the Customers table.
Click CustomerID under the Customers table and drag it onto cell C2 of Sheet1.
Click FirstName under the Customers table and drag it onto cell C7 of Sheet1.
Click LastName under the Customers table and drag it onto cell C8 of Sheet1.
Click BirthDate under the Customers table. Click the arrow to the right of the BirthDate field and then click NamedRange in the list. Click BirthDate and drag it onto cell C9 of Sheet1.
Notice that the formula for cell C10 is already set to calculate the customer's age.

To bind a ListObject control to fields in the data source

In the Data Sources window, click the PortfolioView node of the Customers table, as shown in Figure 6, and drag it onto cell A15 of Sheet1. A ListObject named PortfolioViewListObject is created. It contains one column for each field in PortfolioView. Leave PortfolioViewListObject selected.

Note When you drag complex elements from the Data Sources window onto a worksheet, you lose the formatting of the cells in the new ListObject on the worksheet. Lab 2, Exercise 2, explains how to manually fix this by setting the DataBoundFormat property of the ListObject.

Figure 6. PortfolioView in the Data Sources window
With the new ListObject selected, on the Data menu, point to Microsoft Office Excel Data, point to Filter, and then click AutoFilter to remove the AutoFilter lists from the ListObject.
The Asset Allocations workbook uses named ranges in the ListObject for calculations. Name the Sheet1 range B15:B16 Symbol by following these steps:
1. Select the range B15:B16.
2. In the Name box shown in Figure 7, type Symbol and press ENTER.
Figure 7. Use the Name box to name a range
Repeat the steps in Step 3 to name the ranges described in Table 1.

Table 1. Named ranges needed for the Asset Allocation workbook

Name Range

Symbol Sheet1!$B$15:$B$16

Class Sheet1!$D$15:$D$16

LastPrice Sheet1!$G$15:$G$16

Shares Sheet1!$H$15:$H$16

Amount Sheet1!$I$15:$I$16

Name	Range
Symbol	Sheet1!$B$15:$B$16
Class	Sheet1!$D$15:$D$16
LastPrice	Sheet1!$G$15:$G$16
Shares	Sheet1!$H$15:$H$16
Amount	Sheet1!$I$15:$I$16

Checkpoint

On the File menu, click Save All.
On the Debug menu, click Start Debugging to build and run the project. The workbook Asset Allocations.xls opens in Excel, as shown in Figure 8.

Figure 8. Ranges in the workbook are bound to data in the SQL Server tables (click picture to see larger image)
The data for the first record in the Customers table is displayed in the bound NamedRange controls; additionally, the corresponding details from the PortfolioView view appear in the ListObject control.
Exit Excel without saving the workbook.

Exercise 2: Using Windows Forms Controls

To add Windows Forms controls to the workbook for navigating the data source

If the Toolbox window is not visible, click the View menu and then click Toolbox.
Select a Button control in the Toolbox and drag it to cell C4 of Sheet1. Button1 is added to the worksheet.
Select another Button control in the Toolbox and drag it to cell C5 of Sheet1. Button2 is added to the worksheet.
Use the Properties window to change the properties of the buttons, as shown in Table 2.

Table 2. Property settings for button controls

Control Property Value

Button1 (Name) btnPrevious

Text Previous

Button2 (Name) btnNext

Text Next

Control	Property	Value
Button1	(Name)	btnPrevious
	Text	Previous
Button2	(Name)	btnNext
	Text	Next

In Solution Explorer, right-click Sheet1.vb (or Sheet1.cs) and then click View Code on the shortcut menu.

The designer automatically created a class named Sheet1 for the worksheet and added code to the Startup event for Sheet1. The designer added this code when you bound the NamedRange and ListObject controls to fields in the data source.

' Visual Basic
Private Sub Sheet1_Startup(ByVal sender As Object, _
   ByVal e As System.EventArgs) Handles Me.Startup
    'TODO: Delete this line of code to remove the default AutoFill
    'for 'VSTO2005LabDataSet.PortfolioView'.
    If Me.NeedsFill("VSTO2005LabDataSet") Then
        Me.PortfolioViewTableAdapter.Fill( _
          Me.VSTO2005LabDataSet.PortfolioView)
    End If
    'TODO: Delete this line of code to remove the default AutoFill 
    'for 'VSTO2005LabDataSet.Customers'.
    If Me.NeedsFill("VSTO2005LabDataSet") Then
        Me.CustomersTableAdapter.Fill(Me.VSTO2005LabDataSet.Customers)
    End If
End Sub

// C#
private void Sheet1_Startup(object sender, System.EventArgs e)
{
    // TODO: Delete this line of code to remove the default AutoFill for
    // 'vsto2005LabDataSet.PortfolioView'.
    if (this.NeedsFill("vsto2005LabDataSet"))
    {
        this.portfolioViewTableAdapter.Fill(
            this.vsto2005LabDataSet.PortfolioView);
    }
    // TODO: Delete this line of code to remove the default AutoFill for
    // 'vsto2005LabDataSet.Customers'.
    if (this.NeedsFill("vsto2005LabDataSet"))
    {
        this.customersTableAdapter.Fill(this.vsto2005LabDataSet.Customers);
    }
}

On the View menu, click Designer.
Double-click the Previous button on your worksheet to set up the event handler for the button's Click event.

Add code to the Click event of btnPrevious to navigate the data source.

' Visual Basic
Private Sub btnPrevious_Click(ByVal sender As Object, _
  ByVal e As System.EventArgs) Handles btnPrevious.Click
    'Move to the previous record.
    Me.CustomersBindingSource.MovePrevious()
End Sub

// C#
private void btnPrevious_Click(object sender, EventArgs e)
{
    // Move to the previous record.
    this.customersBindingSource.MovePrevious();
}

Follow the same process to add code to the Click event of btnNext.

' Visual Basic
Private Sub btnNext_Click(ByVal sender As Object, _
  ByVal e As System.EventArgs) Handles btnNext.Click
    'Move to the next record.
    Me.CustomersBindingSource.MoveNext()
End Sub

// C#
private void btnNext_Click(object sender, EventArgs e)
{
    // Move to the next record.
    this.customersBindingSource.MoveNext();
}

Checkpoint

On the File menu, click Save All.
On the Debug menu, click Start Debugging to build and run the project. The Asset Allocations workbook opens in Excel.
Click the buttons to move between the records in the data source. Notice that the details in the ListObject update when the bound fields for the parent table (Customers) change.
Exit Excel without saving changes.

Exercise 3: Creating Smart Tags

To add smart tag expressions and actions

In Solution Explorer, right-click Sheet1.vb (or Sheet1.cs) and then click View Designer.
In cell C11 of Sheet1, add the following formula:
```
=IF(Status="Balanced", "Balanced", "This portfolio is not balanced")
```
At run time, this formula will return the text "Balanced" or "This portfolio is not balanced" based on whether the asset allocations match the recommended allocations for the customer's age group. For now, in design time, the formula will return #N/A.
On the View menu, click Code.

Add an Imports (or using) directive to Sheet1.vb (or Sheet1.cs).

' Visual Basic
Imports MOTE = Microsoft.Office.Tools.Excel

// C#
using MOTE = Microsoft.Office.Tools.Excel;

Append the following code to Sheet1_Startup for adding the smart tag expressions and actions.

' Visual Basic
Dim tag As MOTE.SmartTag = New MOTE.SmartTag( _
    "www.sample.com#AssetAllocationsLab", "Exchange")
tag.Expressions.Add(New System.Text.RegularExpressions.Regex( _
    "This portfolio is not balanced"))
Dim action1 As MOTE.Action = New MOTE.Action( _
    "Execute recommended exchanges")
AddHandler action1.Click, AddressOf action_Click
Dim action2 As MOTE.Action = New MOTE.Action("Send customer letter")
AddHandler action2.Click, AddressOf action_Click
tag.Actions = New MOTE.Action() {action1, action2}
Globals.ThisWorkbook.VstoSmartTags.Add(tag)

// C#
MOTE.SmartTag tag = new MOTE.SmartTag(
    "www.sample.com#AssetAllocationsLab", "Exchange");
tag.Expressions.Add(new System.Text.RegularExpressions.Regex(
    "This portfolio is not balanced"));
MOTE.Action action1 = new MOTE.Action("Execute recommended exchanges");
action1.Click += new MOTE.ActionClickEventHandler(action_Click);
MOTE.Action action2 = new MOTE.Action("Send customer letter");
action2.Click += new MOTE.ActionClickEventHandler(action_Click);
tag.Actions = new MOTE.Action[] { action1, action2 };
Globals.ThisWorkbook.VstoSmartTags.Add(tag);

Add a routine to handle the Click events for the smart tag actions.

' Visual Basic
Private Sub action_Click(ByVal sender As Object, _
   ByVal e As MOTE.ActionEventArgs)
    Dim action As MOTE.Action = DirectCast(sender, MOTE.Action)
    If (action.Caption = "Execute recommended exchanges") Then
        'TO DO: Execute exchanges
    Else
        MessageBox.Show("Customer letter: TO DO")
    End If
End Sub

// C#
private void action_Click(object sender,
    Microsoft.Office.Tools.Excel.ActionEventArgs e)
{
    MOTE.Action action = (MOTE.Action)sender;
    if (action.Caption == "Execute recommended exchanges")
    {
        //TO DO: Execute exchanges
    }
    else
    {
        MessageBox.Show("Customer letter: TO DO");
    }
}

Checkpoint

On the File menu, click Save All.
On the Debug menu, click Start Debugging to build and run the project. The Asset Allocations workbook opens in Excel.

Notice that cell C11 displays the text "The portfolio is not balanced". Because this is an expression that you added as a smart tag expression, the smart tag indicator appears at the lower-right corner of C11.
Click the smart tag indicator. The smart tag actions appear on the shortcut menu, as shown in Figure 9.

Figure 9. Smart tag indicator shortcut menu (click picture to see larger image)
Click Send customer letter on the shortcut menu.

A message box appears in response to the smart tag action.
Exit Excel without saving changes to the workbook.

To add code to execute an exchange of assets

In the Sheet1 class, add a member named Exchange that will insert new records into the data source to exchange assets to match a recommended allocation.

' Visual Basic
Private Sub Exchange()
    Dim ta As New _
        VSTO2005LabDataSetTableAdapters.TransactionsTableAdapter
    Dim custID As Integer = CType(CustomerIDNamedRange.Text, Integer)
    Dim transDate As Date = System.DateTime.Today

    Try
        ' Iterate through the rows in the Exchanges range on Sheet2.
        Dim exchangeData(,) As Object = Globals.Sheet2.Exchanges.Value2
        Dim r As Integer
        For r = 1 To exchangeData.GetUpperBound(0)

            Dim transAmt As Double = 0
            Dim transType As String = ""
            Dim transShares As Double = 0

            ' First check to see if it is an Exchange Out.
            transAmt = exchangeData(r, 6)
            If transAmt < 0 Then
                transType = "Exchange Out"
                transShares = exchangeData(r, 5)
            Else
                ' Check to see if it is an Exchange In.
                transAmt = exchangeData(r, 8)
                If transAmt > 0 Then
                    transType = "Exchange In"
                    transShares = exchangeData(r, 7)
                End If
            End If

            ' If Amount is not 0, perform the exchange.
            If transAmt <> 0 Then  
                Dim transSymbol As String = exchangeData(r, 2)
                Dim results As Integer = ta.Insert(custID, _
                    transSymbol, transDate, transType, transShares, _
                    transAmt)
            End If
        Next

    Catch ex As Exception
        MessageBox.Show(ex.Message, "Asset Allocations", _
            MessageBoxButtons.OK, MessageBoxIcon.Error)
        Return
    End Try

    Application.ScreenUpdating = False
    PortfolioViewTableAdapter.Fill(Vsto2005LabDataSet.PortfolioView)
    Application.ScreenUpdating = True
End Sub

// C#
private void Exchange()
{
    VSTO2005LabDataSetTableAdapters.TransactionsTableAdapter ta = 
        new VSTO2005LabDataSetTableAdapters.TransactionsTableAdapter();
    int custId = Convert.ToInt32(this.customerIDNamedRange.Text);
    DateTime date = DateTime.Today;
    object[,] exchangeData = 
        (object[,])(Globals.Sheet2.Exchanges.Value2);

    try
    {
        // Iterate through the rows in the array.
        for(int r=1;r<=exchangeData.GetUpperBound(0);r++)
        {
            decimal amt = 0;
            string type = "";
            double shares = 0;

            // First check to see if it is an Exchange Out.
            amt = Convert.ToDecimal(exchangeData[r,6]);
            if (amt < 0)
            {
                type = "Exchange Out";
                shares = (double)(exchangeData[r,5]);
            }
            else
            {
                // Check to see if it is an Exchange In.
                amt = Convert.ToDecimal(exchangeData[r,8]);
                if (amt > 0)
                {
                    type = "Exchange In";
                    shares = (double)(exchangeData[r,7]);
                }
            }

            // If amount is not 0, perform the exchange.
            if (amt != 0)  
            {
                string symbol = exchangeData[r,2].ToString();
                int results = ta.Insert(custId, symbol, date, type, 
                shares, amt);
            }
        }
    }

    catch (Exception ex)
    {
        // If an error occurred, roll back the transaction.
        MessageBox.Show(ex.Message, "Asset Allocations", 
            MessageBoxButtons.OK, MessageBoxIcon.Error);
        return;
    }

    Application.ScreenUpdating = false;
    portfolioViewTableAdapter.Fill(vsto2005LabDataSet.PortfolioView);
    Application.ScreenUpdating = true;
}

In action_Click, replace the comment TO DO: Execute exchanges with a call to Exchange.
```
' Visual Basic
Exchange()

// C#
Exchange();
```

To delete the added exchange records (Optional)

If you want to delete the exchanges from the Transactions table so that you can re-run the solution with the database in its original state, you can add a query that will delete the records added for the exchanges when the workbook closes.

In the Data Sources window, right-click VSTO2005LabDataSet and click Edit DataSet with Designer.
Right-click the TransactionsTableAdapter and click Add Query.
Select Use SQL Statements and click Next.
Select DELETE and click Next.

Specify the following SQL statement.

DELETE FROM [dbo].[Transactions] WHERE TransactionType<>'Buy'

Click Next.
Name the function DeleteExchanges and click Finish.
In Solution Explorer, right-click Sheet1.vb (or Sheet1.cs) and click View Code.

Add the following code to Sheet1_Shutdown to call the DeleteExchanges function.

' Visual Basic
Private Sub Sheet1_Shutdown(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles Me.Shutdown

    Dim ta As New _
        VSTO2005LabDataSetTableAdapters.TransactionsTableAdapter
    ta.DeleteExchanges()
End Sub

// C#
private void Sheet1_Shutdown(object sender, System.EventArgs e)
{
    VSTO2005LabDataSetTableAdapters.TransactionsTableAdapter ta =
        new VSTO2005LabDataSetTableAdapters.TransactionsTableAdapter();
    ta.DeleteExchanges();
}

Checkpoint

On the File menu, click Save All.
On the Debug menu, click Start Debugging to build and run the project. The Asset Allocations workbook opens in Excel.
Select the smart tag indicator for cell C11 and click the Execute recommended exchanges action. The exchanges are executed and the workbook now appears as shown below.

Figure 10. Result of smart tag action (click picture to see larger image)
You can navigate through the remaining records in the data source and perform exchanges.
Exit Excel without saving changes to the workbook.

Lab 2—Using Controls in the Actions Pane

In this lab, you create an actions pane for an Excel solution using a Windows Forms control and a user control that you also create.

Note This lab depends upon formulas from the Excel Analysis ToolPak-VBA add-in.

Estimated time to complete:

Exercise 1: Adding a Windows Forms Control to the Actions Pane—5 minutes
Exercise 2: Adding a User Control to the Actions Pane—5 minutes

Exercise 1: Adding a Windows Forms Control to the Actions Pane

To create an Excel Workbook project

On the File menu, click New, and then click Project.
In the list of Project Types, expand Visual Basic (or Visual C#) and click Office.
In the list of project Templates, select Excel Workbook.
In the project Name box, type DebtConsolidation.
In the Location box, type a location, such as C:\VSTO2005\Labs. (If no Location box appears, the Save dialog box appears in Step 10 instead.)
Click OK. The Visual Studio Tools for Office Project Wizard appears.
In the wizard, click Copy an existing document.
Click Browse.
Navigate to the following folder, click Debt Consolidation.xls, click Open, and then click OK:

\My Documents\Visual Studio 2005\Projects\VSTO 2005 Excel Labs\Starter Files
On the File menu, click Save All.
If a Location field did not appear in Step 4, a Save dialog box appears. In the project Location box, type a location, such as C:\VSTO2005\Labs, and click Save.

To set the size and position for the actions pane

In Solution Explorer, right-click ThisWorkbook.vb (or ThisWorkbook.cs) and then click View Code. The code module for the workbook appears. Observe that the workbook code-behind is encapsulated in a class named ThisWorkbook and that event handlers for Startup and ShutDown already exist.

Add code to the ThisWorkbook_Startup event to size and position the actions pane when the workbook loads and to initialize the worksheet view.

' Visual Basic
Private Sub ThisWorkbook_Startup(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles Me.Startup

    With Me.Application.CommandBars("Task Pane")
        .Width = 300
        .Position = Office.MsoBarPosition.msoBarLeft
    End With
    Dim labelControl As New Label
    labelControl.Text = "TO DO: Add Controls Here"
    ActionsPane.Controls.Add(labelControl)
    Globals.Sheet2.Select()
    Application.ActiveWindow.DisplayHeadings = False
End Sub

// C#
private void ThisWorkbook_Startup(object sender, System.EventArgs e)
{
    Office.CommandBar taskpane = 
        ThisApplication.CommandBars["Task Pane"];
    taskpane.Width = 300;
    taskpane.Position = Microsoft.Office.Core.MsoBarPosition.msoBarLeft;
    Label labelControl = new Label();
    labelControl.Text = "TO DO: Add Controls Here";
    this.ActionsPane.Controls.Add(labelControl);
    Globals.Sheet2.Select(missing);
    ThisApplication.ActiveWindow.DisplayHeadings = false;
}

Checkpoint

On the File menu, click Save All.
On the Debug menu, click Start Debugging to build and run the project. The workbook titled Debt Consolidation.xls opens in Excel.
The actions pane is displayed with a label, as shown in Figure 11.

Figure 11. The code adds a label to the actions pane (click picture to see larger image)
Close the workbook without saving changes and exit Excel.

Exercise 2: Adding a User Control to the Actions Pane

To create a user control

In Solution Explorer, right-click the DebtConsolidation project, point to Add on the shortcut menu, and then click New Item. The Add New Item dialog box appears.
Select the User Control template.
In the Name box, type CustomerProfile, and then click Add.
Set properties for the CustomerProfile user control by referring to the values in Table 3.

Table 3. Property settings for the CustomerProfile user control

Property Value

BackColor InactiveCaptionText

ForeColor Desktop

Size 300, 600

Property	Value
BackColor	InactiveCaptionText
ForeColor	Desktop
Size	300, 600

To add the user control to the actions pane

In Solution Explorer, right-click Thisworkbook.vb (or Thisworkbook.cs) and then click View Code on the shortcut menu.

In the ThisWorkbook class, add a new member variable for an instance of a CustomerProfile control.

' Visual Basic
Private WithEvents profileControl As CustomerProfile

// C#
private CustomerProfile profileControl;

Append the following code to ThisWorkbook_Startup to add an instance of the CustomerProfile control to the actions pane.

' Visual Basic
profileControl = New CustomerProfile
Me.ActionsPane.Controls.Add(profileControl)

// C#
profileControl = new CustomerProfile();
this.ActionsPane.Controls.Add(profileControl);

Checkpoint

On the File menu, click Save All.
On the Debug menu, click Start Debugging to build and run the project. The workbook titled Debt Consolidation opens in Excel.
The actions pane now contains two controls, a Label control and a CustomerProfile user control.

Note You add controls to the CustomerProfile control in another lab. For now, you see only the user control surface in the actions pane.
Close the workbook without saving changes and exit Excel.

Lab 3—Connecting Data in an Excel Workbook and Actions Pane

In this lab, you connect to a Microsoft SQL Server data source, bind controls in both the workbook and the actions pane to database objects, and navigate records in the data source.

Estimated time to complete:

Exercise 1: Setting Up a Connection and Adding a Data Source—10 minutes
Exercise 2: Connecting, Binding, and Navigating Data—40 minutes

This lab uses the DebtConsolidation project that you created in Lab 2.

Exercise 1: Setting Up a Connection and Adding a Data Source

To create the sample database using an SQL script

On the Start menu, click Run. Type cmd and click OK. A Command Prompt window opens.
Navigate to the folder where the starter files reside, \My Documents\Visual Studio 2005\Projects\VSTO 2005 Excel Labs\Starter Files.

Run the batch procedure.

osql -E -S .\SQLExpress -i VSTO2005DebtConsolidation.sql

Close the Command Prompt window.

To add a data source to your project

On the Data menu, click Show Data Sources. The Data Sources window appears.
In the Data Sources window, click Add New Data Source. The Data Source Configuration Wizard appears.
Select Database for the data source type and click Next.
Click New Connection. Because you already created a data connection in Lab 1, you see the Add Connection dialog box shown in Figure 12.

Figure 12. The Add Connection dialog box (click picture to see larger image)
Create the connection:
1. If the Data Source is not Microsoft SQL Server (SqlClient), click Change and choose the SQL Server data source.
2. In the Server Name box, type .\SQLExpress.
3. Select Use Windows Authentication for the authentication mode.
4. In the Connect to a database section, select VSTO2005DebtConsolidation in the Select or enter a database name list.
5. Click Test Connection to confirm that the connection is valid and then click OK to add the new connection. The new connection has the default name YourServerName.VSTO2005DebtConsolidation.dbo.
Click Next.
Save the connection using the default name, VSTO2005DebtConsolidationConnectionString.
Click Next.
In the list of database objects, select the Tables check box and then click Finish.
Click VSTO2005DebtConsolidationDataSet in the Data Sources window and then click Edit DataSet with Designer at the top of the Data Sources window. Observe that a relation named FK_LoanData_NewCustomers already exists between the LoanData and NewCustomers tables.

Figure 13. The relation between the LoanData and NewCustomers tables

Exercise 2: Connecting, Binding, and Navigating Data

To select controls to use for binding NewCustomers fields

In Solution Explorer, right-click CustomerProfile.vb and then click View Designer on the shortcut menu.
On the Data menu, click Show Data Sources to display the Data Sources window.
In the Data Sources window, select NewCustomers.
Click the drop-down arrow next to NewCustomers and click Details.
Expand NewCustomers.
Click to select the ProposedRate field, click the arrow next to ProposedRate, and then click Customize. The Options dialog box appears.
In the Associated controls list, select NumericUpDown (if it is not already selected) and click OK.
Click the drop-down arrow next to ProposedRate, and then click NumericUpDown.
Click to select the MinimumRate field, click the arrow next to MinimumRate, and then click None.

To add controls that are bound to the data source to the CustomerProfile component

From the Data Sources window, drag NewCustomers to the upper-left corner of the user control design surface.

Observe that the designer automatically generates Label controls, bound TextBox controls, and a bound NumericUpdown control for the fields in the NewCustomers table. The designer also adds a BindingNavigator control for navigating the records in the data source at run time.
In the Data Sources window, select the LoanData node in the NewCustomers table, as shown in Figure 14, and drag it to the user control. Drop it under the existing NewCustomers fields.

Figure 14. LoanData shows as a child of NewCustomers

Observe that the designer adds a DataGridView control, named LoanDataDataGridView, to the user control.

To adjust the data grid display properties

Right-click the LoanDataDataGridView control and then click Edit Columns.
Remove NewCustomerID, LoanNumber, LoanAmount, and LoanRate from the list of selected columns by clicking the Remove button.
In the list of selected columns, select LoanName. Set the Width property to 150 and the ReadOnly property to True.
In the list of selected columns, select Include and click the Move Up button. Set the Width property to 50 and the ReadOnly property to True.
Click OK to close the Edit Columns dialog box.

Note At this point, Include and LoanName should be the only two columns in LoanDataDataGridView.
Set the properties for LoanDataDataGridView, as shown in Table 4.

Table 4. Property settings for the data grid

Property Value

AllowUserToAddRows False

AllowUserToDeleteRows False

RowHeadersVisible False

Size 200, 175

Property	Value
AllowUserToAddRows	False
AllowUserToDeleteRows	False
RowHeadersVisible	False
Size	200, 175

To set NumericUpDown control properties and add data bindings

Select the ProposedRateNumericUpDown control.
Set the DecimalPlaces property to 1, the Increment property to 0.5, and the Maximum property to 24.
Expand the (DataBindings) property and click the ellipsis (...) next to (Advanced). The Formatting and Advanced Binding dialog box appears.
In the Property list, select Minimum. Click the drop-down arrow for the Binding list. In the list, expand NewCustomersBindingSource and select MinimumRate.
Click OK to close the Formatting and Advanced Binding dialog box.
In the component tray, select NewCustomersBindingNavigator.

To fill the table adapters when the component loads

Double-click the CustomerProfile control surface to display the control's Load event in the Code Editor.

Add code to fill the table adapters.

' Visual Basic
Private Sub CustomerProfile_Load(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles Me.Load
    Me.LoanDataTableAdapter.Fill( _
        Me.VSTO2005DebtConsolidationDataSet.LoanData)
    Me.NewCustomersTableAdapter.Fill( _
        Me.VSTO2005DebtConsolidationDataSet.NewCustomers)
End Sub

// C#
private void CustomerProfile_Load(object sender, EventArgs e)
{
    this.loanDataTableAdapter.Fill(
        this.vSTO2005DebtConsolidationDataSet.LoanData);
    this.newCustomersTableAdapter.Fill(
        this.vSTO2005DebtConsolidationDataSet.NewCustomers);
}

To toggle the Include field when a DataGridView row is clicked

On the View menu, click Designer.
Double-click the LoanDataDataGridView control to display the control's CellContentClick event in the Code Editor.

Add code to toggle the Include field when the row is clicked.

' Visual Basic
Private Sub LoanDataDataGridView_CellContentClick( _
    ByVal sender As Object, ByVal e As _
    System.Windows.Forms.DataGridViewCellEventArgs) _
    Handles LoanDataDataGridView.CellContentClick
    Try
        Dim r As Integer = Me.LoanDataDataGridView.CurrentCell.RowIndex
        Dim rv As DataRowView = DirectCast( _
            Me.LoanDataDataGridView.Rows(r).DataBoundItem, DataRowView)
        rv("Include") = Not (rv("Include"))
        rv.EndEdit()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

// C#
private void loanDataDataGridView_CellContentClick(
    object sender, DataGridViewCellEventArgs e)
{
    try
    {
        int r = this.loanDataDataGridView.CurrentCell.RowIndex;
        DataRowView rv = (DataRowView)(
            this.loanDataDataGridView.Rows[r].DataBoundItem);
        rv["Include"] = !(bool)(rv["Include"]);
        rv.EndEdit();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

To eliminate the placeholder label from the actions pane

In Solution Explorer, right-click ThisWorkbook.vb (or ThisWorkbook.cs) and then click View Code.

In the ThisWorkbook_Startup procedure, comment out the three lines used to define the label and add the label to the actions pane.

' Visual Basic
' Dim labelControl As New Label
' labelControl.Text = "TO DO: Add Controls Here"
' ActionsPane.Controls.Add(labelControl)

// C#
// Label labelControl = new Label();
// labelControl.Text = "TO DO: Add Controls Here";
// this.ActionsPane.Controls.Add(labelControl);

Checkpoint

On the File menu, click Save All.
On the Debug menu, click Start Debugging to build and run the project. The Debt Consolidation workbook opens in Excel.

The records from the data source appear in the actions pane, as illustrated in Figure 15. The first record is for the customer Nancy Davolio.

Figure 15. The actions pane with bound data
In the DataGridView control, click one or more rows to select or clear the check boxes.
Change the Proposed Rate.
On the BindingNavigator control, click Move next to move to the next record. The next record corresponds to the customer Andrew Fuller.
On the BindingNavigator control, click Move previous to move to the previous record. The data for the customer Nancy Davolio appears. Notice that your changes to the bound DataGridView and NumericUpDown controls are restored (in other words, your changes persisted in the in-memory dataset).
Close the workbook without saving changes and exit Excel.

To update a named range based on changes in the actions pane

In Solution Explorer, right-click CustomerProfile.vb (or CustomerProfile.cs) and then click View Designer on the shortcut menu.
Double-click the ProposedRateNumericUpDown control to display the control's ValueChanged event in the Code Editor.

Add code to raise an event named RateChanged when the value in the bound NumericUpDown control changes.

' Visual Basic
Public Event RateChanged(ByVal NewRate As Double)

Private Sub ProposedRateNumericUpDown_ValueChanged( _
    ByVal sender As Object, ByVal e As System.EventArgs) _
    Handles ProposedRateNumericUpDown.ValueChanged
    RaiseEvent RateChanged(ProposedRateNumericUpDown.Value * 0.01)
End Sub

// C#
public delegate void RateChangedEventDelegate(
   object sender, decimal NewRate);
public event RateChangedEventDelegate RateChanged;

private void proposedRateNumericUpDown_ValueChanged(object sender,
    EventArgs e)
{
    RateChangedEventDelegate temp = RateChanged;
    if (temp != null)
    {
        temp(this, this.proposedRateNumericUpDown.Value/100);
    }
}

In Solution Explorer, right-click Thisworkbook.vb (or ThisWorkbook.cs) and then click View Code on the shortcut menu.

Add an event handler for the RateChanged event of the profileControl control, to add the new rate to the Sheet2 worksheet in the InterestRate named range.

' Visual Basic
Private Sub profileControl_RateChanged(ByVal NewRate As Double) _
    Handles profileControl.RateChanged
    Globals.Sheet2.InterestRate.Value2 = NewRate
End Sub

For Visual C#, in ThisWorkbook_Startup, add a line of code to set up the handler for the RateChanged event just before the profileControl is added to the actions pane.

// C#
profileControl = new CustomerProfile();
profileControl.RateChanged +=    new CustomerProfile.RateChangedEventDelegate(profileControl_RateChanged);
this.ActionsPane.Controls.Add(profileControl);

In the ThisWorkbook class, add an event handler for the profileControlRateChanged.

// C#
private void profileControl_RateChanged(object sender, 
    decimal newRate)
{
    Globals.Sheet2.InterestRate.Value2 = newRate;
}

To bind an Excel ListObject to the data source

In Solution Explorer, right-click CustomerProfile.vb (or CustomerProfile.cs) and then click View Code on the shortcut menu.

Add a new read-only property named Connector that returns the BindingSource that the CustomerProfile control is using for the current data source.

' Visual Basic
Public ReadOnly Property Connector() As BindingSource
    Get
        Return Me.NewCustomersBindingSource
    End Get
End Property

// C#
public BindingSource Connector
{
    get
    { 
        return this.newCustomersBindingSource; 
    }
}

In Solution Explorer, right-click Sheet2.vb (or Sheet2.cs) and then click View Designer on the shortcut menu.
On the Data menu, point to Microsoft Office Excel Data, point to List, and then click Create List. The Create List dialog box appears.
Type =A12:F13 for the range, select My list has headers, and then click OK.
On the Data menu, point to Microsoft Office Excel Data, point to Filter, and then click AutoFilter to remove the filter controls from the list.
On the Data menu, point to Microsoft Office Excel Data, point to List, and then click Hide Border of Inactive Lists.
In Solution Explorer, right-click ThisWorkbook.vb (or ThisWorkbook.cs) and then click View Code.

Append the following code to ThisWorkbook_Startup.

' Visual Basic
With Globals.Sheet2.List1
    .AutoSetDataBoundColumnHeaders = False
    .DataBoundFormat = Excel.XlRangeAutoFormat.xlRangeAutoFormatNone
    .SetDataBinding(profileControl.Connector, _
        "FK_LoanData_NewCustomers")
End With

// C#
Globals.Sheet2.List1.AutoSetDataBoundColumnHeaders = false;
Globals.Sheet2.List1.DataBoundFormat = 
    Excel.XlRangeAutoFormat.xlRangeAutoFormatNone;
Globals.Sheet2.List1.SetDataBinding(
    profileControl.Connector, "FK_LoanData_NewCustomers");

This binds the ListObject on Sheet2 to the same BindingSource used by the CustomerProfile control in the actions pane, and does not change the formatting of the ListObject.

In Solution Explorer, right-click Sheet2.vb (or Sheet2.cs) and click View Designer.
Select cell A1 on Sheet2 so that this will be the active cell when the workbook opens.

Checkpoint

On the File menu, click Save All.
On the Debug menu, click Start Debugging to build and run the project. The workbook titled Debt Consolidation opens in Excel.

Notice that the data in the actions pane now appears in the CallSheet worksheet, as shown in Figure 16.

Note The Debt Consolidation workbook uses functions provided by the Analysis Toolpak Add-in for Excel. If you do not see values for all the calculations or if some calculations are returning a #NAME error, click the Tools menu in Excel and then click Add-Ins. Select the check box next to Analysis Toolpak and click OK. Close the workbook without saving your changes, exit Excel, and then repeat the Checkpoint steps.
Change the Proposed Rate and observe the changes in the InterestRate named range on the worksheet.
In the Include column of the DataGridView, select or clear check boxes and observe the changes in the ListObject control on the worksheet.
Close the workbook without saving changes and exit Excel.

Figure 16. The data now appears in the worksheet and the actions pane (click picture to see larger image)

Conclusion

In these labs, you learned how to create an asset allocation application and a debt consolidation application using Microsoft Visual Studio 2005 Tools for the Microsoft Office System and Microsoft Office Excel 2003. You learned how to use Windows Forms controls, host controls, the actions pane, smart tags, and data binding.

Additional Resources

For more information, see the following resources:

Visual Studio 2005 Tools for Office Hands-on Labs for Excel 2003

Contents

Setting Up the Labs

To prepare Excel for the labs

Lab 1—Connecting Data and Controls in an Excel Workbook

Exercise 1: Connecting and Binding Data

To create an Excel Workbook project

To create the sample database by using an SQL script

To add a data source to your project

To add a relation to the data source

To bind NamedRange controls to fields in the data source

To bind a ListObject control to fields in the data source

Checkpoint

Exercise 2: Using Windows Forms Controls

To add Windows Forms controls to the workbook for navigating the data source

Checkpoint

Exercise 3: Creating Smart Tags

To add smart tag expressions and actions

Checkpoint

To add code to execute an exchange of assets

To delete the added exchange records (Optional)

Checkpoint

Lab 2—Using Controls in the Actions Pane

Exercise 1: Adding a Windows Forms Control to the Actions Pane

To create an Excel Workbook project

To set the size and position for the actions pane

Checkpoint

Exercise 2: Adding a User Control to the Actions Pane

To create a user control

To add the user control to the actions pane

Checkpoint

Lab 3—Connecting Data in an Excel Workbook and Actions Pane

Exercise 1: Setting Up a Connection and Adding a Data Source

To create the sample database using an SQL script

To add a data source to your project

Exercise 2: Connecting, Binding, and Navigating Data

To select controls to use for binding NewCustomers fields

To add controls that are bound to the data source to the CustomerProfile component

To adjust the data grid display properties

To set NumericUpDown control properties and add data bindings

To fill the table adapters when the component loads

To toggle the Include field when a DataGridView row is clicked

To eliminate the placeholder label from the actions pane

Checkpoint

To update a named range based on changes in the actions pane

To bind an Excel ListObject to the data source

Checkpoint

Conclusion

Additional Resources

Additional resources