Bringing SQL Server 2005 Data to Excel 2007 Using Visual Studio 2005 Tools for the Office System SE

Article
05/23/2014

Summary: Learn how to create a Microsoft Visual Studio 2005 Tools for the 2007 Microsoft Office system application add-in for Microsoft Office Excel 2007 that can work with data from a Microsoft SQL Server 2005 database. See how to hook up the add-in to the 2007 Microsoft Office Fluent Ribbon and a custom task pane. (23 printed pages)

Brian A. Randell, Microsoft MVP, MCW Technologies, LLC

November 2007

Applies to: Microsoft Visual Studio 2005 Tools for the 2007 Microsoft Office (Second Edition), Microsoft Office Excel 2007

Contents

Overview
Creating the Custom Stored Procedure
Creating the Excel 2007 Add-In
Adding Task Panes
Adding Office Fluent Ribbon Support for the Add-In
Controlling the State of the Office Fluent Ribbon Button
Loading the SQL Server Data
Saving Changes Back to the Database
Conclusion
Additional Resources
About the Author

Overview

From an end-user perspective, Microsoft Office Excel 2007 provides enormous power and flexibility when it comes to analyzing and manipulating data. The user can enter this data manually or by importing it from text files. Another popular feature is the ability to use dynamic data features of Excel to load data from a live Microsoft SQL Server database. For example, in Excel 2007, click the Excel 2007 Data tab and then click From Other Sources. A list of five different data sources is displayed from which to import data as shown in Figure 1.

Figure 1. The options on From Other Sources menu

The options on From Other Source menu

Although it is useful for more advanced users, it is often not wise to allow inexperienced users to have direct access to your SQL Server. In addition, because of business rules or possible regulatory requirements, it may not even be legal. In this case, you need to build a custom user interface (UI) that allows users to get the data they need into Excel 2007 and, where applicable, send updates back to the server.

This article shows how to create an application add-in for Excel 2007 that allows you to pull data from the SQL Server 2005 AdventureWorks database and put it into the active Excel workbook. In addition, you can, where appropriate, send updates back to SQL Server 2005. The add-in takes advantage of new application-level task pane support and Microsoft Office Fluent Ribbon support.

To work through the scenarios in this article, you need a copy of Microsoft Visual Studio 2005 that supports Visual Studio 2005 Tools for Office SE. You also need to install Visual Studio 2005 Tools for Office SE, Excel 2007, and SQL Server 2005—any edition works as long as it has a copy of the AdventureWorks sample database installed.

If you do not have the AdventureWorks sample database, you can download it from the SQL Server 2005 Samples and Sample Databases.

Note
Although it is possible to write Microsoft Visual Basic and Microsoft Visual C# in a similar fashion, the code written in this article generally uses built-in language constructs—such as the Visual Basic MsgBox and WithEvents features—instead of the Microsoft .NET Framework alternatives.

Creating the Custom Stored Procedure

The AdventureWorks database has a rich schema with many different objects. This example uses the stored procedure uspUpdateEmployeePersonalInfo to update employee data. However, you need a stored procedure to provide you with a list of existing data to review and modify. To make this easy, you can create the stored procedure manually with the following Transact-SQL (T-SQL) statements.

[T-SQL]
CREATE PROCEDURE HumanResources.GetEmployeePersonalInfo 

AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  -- Insert statements for procedure here.
  SELECT
    e.EmployeeID, c.Title, c.FirstName, c.MiddleName, c.LastName, 
    c.Suffix, e.Title AS JobTitle, c.Phone, c.EmailAddress,
    e.NationalIDNumber, e.MaritalStatus, e.Gender, e.BirthDate
  FROM
    HumanResources.Employee AS e 
INNER JOIN
    Person.Contact AS c 
ON 
    c.ContactID = e.ContactID 
INNER JOIN
    HumanResources.EmployeeAddress AS ea 
ON 
    e.EmployeeID = ea.EmployeeID
END
GO

There are a number of ways to create a stored procedure from T-SQL statements. Links to three of these techniques are provided in Additional Resources.

After you update the AdventureWorks database, you are ready to create an Excel 2007 Add-in project.

Creating the Excel 2007 Add-In

In the following procedures, you create the Excel 2007 add-in project.

To create the Excel 2007 project

Start Visual Studio 2005.

Note
This article assumes that you installed Visual Studio 2005 and are using the default General Development settings. If you are using the Visual Basic Developer settings, for example, the menu items are slightly different. You may need to adjust the instructions here for your specific environment.

On the File menu, click New Project.
In the New Project dialog box, in the Project types pane, click Visual Basic.
Expand the Office node, and then select the 2007 Add-ins node.
In the Templates pane of the dialog box, click the Excel Add-in template.
In the Name field, type ExcelData, and then select a location to save the project.
In the Solution Name field, type VSTOSEData. Ensure that there is a check mark next to Create directory for solution, and then click OK.

Visual Studio 2005 creates the solution with an Excel 2007 application add-in project and a Windows Installer Setup project.
On the Window menu, click Close All Documents.

At this point, the add-in does not do much. In the following sections, you add the UI, the data access logic, and connect everything to Excel 2007. The first thing you need to do is create an application-level task pane. The task pane gathers configuration data from the user to connect to the database.

Adding Task Panes

Application task panes are a great way to gather information from the user without the need to produce a modal dialog box. The first step you need to take is to build the UI using a Windows Forms User Control. After you create the user control, you only need to add a few lines of code to make it available to Excel 2007.

To add a custom task pane

In Visual Studio 2005, in Solution Explorer, right-click the ExcelData node, point to Add, and then click User Control.
In the Add New Item dialog box, in the Name field, type Configuration, and then click Add.

Visual Studio 2005 sets the necessary references to the assemblies your solution needs and opens the user control’s designer.

The database to use for this solution is the SQL Server 2005 AdventureWorks sample database. The AdventureWorks database contains data for Adventure Works Cycles, a fictitious large, multinational manufacturing company. The user control provides a place for the user to set configuration options for the add-in including the name of the SQL Server 2005 instance to use.
From the Toolbox in the Common Components tab, drag a Label control, a Textbox control, and a Button control onto the design surface, and position the controls as shown in Figure 2. If you do not see the Toolbox, click the View menu, and then click Toolbox.

Figure 2. Configuration user control with added controls
Set the Text property of the Label control to SQL Server Instance.
Set the Name property of the Textbox control to txtSQLServer.
Set the Name property of the Button control to btnSave, and set its Text property to Save.
Additionally, set the Enabled property of the Button control to False.
On the File menu, click Save All.
In Solution Explorer, right-click the ExcelData node, and then click Properties.
In the Properties window, click the Settings tab.
In the Settings grid, change the Name column to SQLServerInstance.
Change the Value column to Unknown.
On the File menu, click Save All to save your changes.
On the File menu, click Close to close the Properties window.
In the user control designer, select the text box.
In the Properties pane, select the Events icon.

Figure 3. Events icon in the Properties pane
Select the Click event, and then double-click it to generate an event handler and open the code editor.

Add the following code to the body of the Click event handler.

If Me.txtSQLServer.Text IsNot Nothing AndAlso _
   Me.txtSQLServer.Text.Length > 0 Then

   My.Settings.SQLServerInstance = Me.txtSQLServer.Text
End If

Below the Public Class Configuration statement, add the following class field.
```
Private loading As Boolean = True
```
Select the txtSQLServer text box.

In the Properties pane, double-click the TextChanged event, and add the following code to the body of the event.

If loading Then Exit Sub
' Enable the command button if there is text in the control.
Me.btnSave.Enabled = (Me.txtSQLServer.Text IsNot Nothing _
  AndAlso Me.txtSQLServer.Text.Length > 0)

Next, in the Properties pane for the Configuration user control, double-click Load to add an event handler, and add the following code to the body of the event handler.
```
If Not My.Settings.SQLServerInstance = "Unknown" Then
  Me.txtSQLServer.Text = My.Settings.SQLServerInstance
End If
loading = False
```
On the File menu, click Save All to save your changes.
On the Window menu, click Close All Documents.

With the user control created and code added, you need to have the add-in register the control so that the control is displayed in an application task pane.
In Solution Explorer, right-click the ThisAddin.vb node, and then click View Code.
On the View menu, click Code to display the code window.

Modify the ThisAddIn_Startup event handler to look like the following.

Private Sub ThisAddIn_Startup( _
  ByVal sender As Object, ByVal e As System.EventArgs) _
  Handles Me.Startup

  ' Start of Visual Studio 2005 Tools for Office SE-generated code
  Me.Application = CType( _
    Microsoft.Office.Tools.Excel.ExcelLocale1033Proxy.Wrap( _
    GetType(Excel.Application), Me.Application), _
    Excel.Application)
  ' End of Visual Studio 2005 Tools for Office SE-generated code
  Dim ctl As New Configuration
  Dim tp As Microsoft.Office.Tools.CustomTaskPane
  tp = Me.CustomTaskPanes.Add(ctl, "Configure")
  tp.DockPosition = _
  Microsoft.Office.Core.MsoCTPDockPosition.msoCTPDockPositionRight
  tp.Visible = True
End Sub

On the File menu, click Save All to save your work.
On the Build menu, click Build Solution and ensure the project compiles with no errors.
On the Debug menu, click Start Debugging to test the solution. Excel is started.
Verify that the task pane is displayed in Excel, and then close Excel and return to Visual Studio 2005.
On the Window menu, click Close All Documents.

Adding Office Fluent Ribbon Support for the Add-In

As you saw when you tested the add-in, the task pane is displayed when Excel starts. However, you also need to provide a way to turn the task pane on and off so that it is available only as needed, such as when the user needs to set the SQL Server 2005 instance name. The 2007 Microsoft Office Fluent Ribbon allows you to add UI controls to perform this and other tasks. The following procedures add a toggle button to display and hide the task pane and buttons for Load, Refresh, and Save operations.

To customize the Office Fluent Ribbon

Right-click the ExcelData node, point to Add, and then click New Item.
In the Add New Item dialog box, click Ribbon Support, and then click Add.

Visual Studio adds two files to the project: Ribbon1.xml contains XML markup to modify the Office Fluent Ribbon and Ribbon1.vb contains the code that adds functionality to the customized Ribbon.
Double-click Ribbon1.xml to open the code window.

Replace the existing XML content with the following.

<customUI 
  xmlns="http://schemas.microsoft.com/office/2006/01/customui" 
  onLoad="OnLoad">
  <ribbon>
    <tabs>
      <tab id="AdventureWorks" label="Adventure Works">
        <group id="Data"
               label="Data">
          <button id="btnLoad" label="Load" 
                  screentip="Load Employee Data" size="large" 
                  imageMso="GetExternalDataFromOtherSources" 
                  onAction="OnLoadData" enabled="false" />
          <button id="btnRefresh" label="Refresh" 
                  screentip="Refresh Employee Data" size="large" 
                  imageMso="DataRefreshAll"
                  onAction="OnRefresh" enabled="false" />
          <button id="btnSave" label="Save" 
                  screentip="Save Employee Data" size="large" 
                  imageMso="DatabaseSqlServer" 
                  onAction="OnSave" enabled="false" />
        </group>
        <group id="Configuration"
               label="Configuration">
          <toggleButton id="tbTaskPane" label="Configure Task Pane"
            screentip="Configure the Adventure Works Data Add-in."
            size="large"
            imageMso="ServerProperties"
            onAction="OnToggleTaskPane" />
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

This markup adds a new tab titled Adventure Works to the Office Fluent Ribbon. The new tab contains two groups, titled Data and Configuration, containing three buttons and a single toggle button, respectively. These buttons trigger callback procedures, which is the way that the Office Fluent Ribbon communicates with your code.

On the Window menu, click Close All Documents. Click Yes in the save confirmation dialog box.
In Solution Explorer, right-click the Ribbon1.xml node, and click Exclude from Project.
In Solution Explorer, right-click the ExcelData node, and then click Properties.
In Project Designer, select the Resources tab.
Click the arrow beside the Add Resource button, and click Add Existing File as shown in Figure 4.

Figure 4. Adding a resource file to the project
In the Add existing file to resources dialog box, navigate to the folder that contains the ExcelData add-in project file. Select Ribbon1.xml, and then click Open.
On the File menu, click Save All to save your changes.
On the File menu, click Close to close the Properties window.

Double-click the Ribbon1.vb node and replace the entire contents of the file with the following code.

Imports System
Imports System.Collections.Generic
Imports System.Diagnostics
Imports System.IO
Imports System.Text
Imports System.Reflection
Imports System.Runtime.InteropServices
Imports System.Windows.Forms
Imports Office = Microsoft.Office.Core

Partial Public Class ThisAddIn
  Private ribbon As Ribbon1

  Protected Overrides Function RequestService( _
    ByVal serviceGuid As Guid) As Object

    If serviceGuid = _
      GetType(Office.IRibbonExtensibility).GUID Then

      If ribbon Is Nothing Then
        ribbon = New Ribbon1()
      End If

      Return ribbon
    End If

    Return MyBase.RequestService(serviceGuid)
  End Function
End Class

<ComVisible(True)> _
  Public Class Ribbon1
  Implements Office.IRibbonExtensibility

  Private ribbon As Office.IRibbonUI

  Public Sub New()
  End Sub

  Public Function GetCustomUI( _
    ByVal ribbonID As String) As String _
    Implements Office.IRibbonExtensibility.GetCustomUI

    Return My.Resources.Ribbon1
  End Function

#Region "Ribbon Callbacks"

  Public Sub OnLoad(ByVal ribbonUI As Office.IRibbonUI)
    Me.ribbon = ribbonUI
  End Sub

  Public Sub OnToggleTaskPane( _
    ByVal control As Office.IRibbonControl, _
    ByVal isPressed As Boolean)

  End Sub

#End Region
End Class

This code removes some sample code and comments from the original code. In addition, it replaces the code to load the custom XML with simpler code that uses the new resource management features available to applications based on the Microsoft .NET Framework 2.0.

Double-click the ThisAddin.vb node to open the code window.
Cut the following lines of code from the ThisAddIn_Startup event procedure, and make them class fields instead by pasting the lines after the public class ThisAddIn statement.
```
Dim ctl As New Configuration
Dim tp As Microsoft.Office.Tools.CustomTaskPane
```
Change the Dim commands to Private.
On the File menu, click Save All to save your changes.
On the Build menu, click Build Solution and ensure that the project compiles with no errors.
In the Ribbon1.vb code window, add the following class field for the Ribbon1 class.
```
Public TaskPane As Microsoft.Office.Tools.CustomTaskPane
```

In the callback procedure OnToggleTaskPane, add the following code.

If TaskPane IsNot Nothing Then
  TaskPane.Visible = isPressed
End If

In the ThisAddin.vb code window, replace the last line of code from the ThisAddIn_Startup event handler as follows.

Replace:
```
tp.Visible = True
```
With:
```
Me.ribbon.TaskPane = tp
```
The line assigns the task pane reference to the Ribbon class TaskPane property.
On the File menu, click Save All to save your work.
On the Build menu, click Build Solution and ensure the project compiles with no errors.
On the Debug menu, click Start Debugging to test the solution.
In Excel, select the new Adventure Works tab to see the new UI as shown in Figure 5.

Figure 5. The Adventure Works tab
Click Configure Task Pane to display the task pane. Click the toggle button again to close the task pane.
Exit Excel, and return to Visual Studio 2005.
On the Window menu, click Close All Documents.

Controlling the State of the Office Fluent Ribbon Button

At this point in the project, only the toggle button is enabled and working. The other three buttons on the Adventure Works tab are disabled by default. Next, you want to ensure that these buttons are only enabled in the proper context. For example, you want the Load button enabled as long as the SQL Server 2005 instance name is set. The Refresh button should only be enabled if data is loaded and the sheet with the data is the current active sheet. Finally, you want the Save button enabled if the user changes the data and the sheet with the data is the active sheet.

Unlike CommandBar buttons used in earlier versions of Microsoft Office, you cannot change the button’s enabled state directly. Instead, the Office Fluent Ribbon must call your code to check the state of the button. In this next section, you add the code to enable the Load button. You also add code that enables the other buttons after the data is loaded. Finally, you add code to set the state of the toggle button if the user closes the task pane by using the task pane’s Close button (the X in the upper-right corner of the pane).

To set the state of the toggle button

In Solution Explorer, double-click the Ribbon1.vb node to open the code window.
In the Ribbon Callbacks region, add the following method.
```
Public Function TaskPane_getPressed( _
   ByVal control As Office.IRibbonControl) As Boolean

   Return Me.TaskPane.Visible
End Function
```
To ensure that the Office Fluent Ribbon calls this procedure, you must bind the callback procedure to the toggle button and add code that indicates to the Office Fluent Ribbon to check the state of the button. First, you modify the XML for the toggle button. Second, you provide an event handler for the VisibleChanged event of the task pane. In this event handler, the code invalidates the toggle button, which causes the Office Fluent Ribbon to call the callback procedure you just added.
Double-click the Ribbon1.xml node, and add the following attribute to the tbTaskPane toggle button.
```
getPressed ="TaskPane_getPressed"
```

Next, add the following method to the Ribbon1 class in the Ribbon1.vb node.

Public Sub Refresh(ByVal controlID As String)
   Me.ribbon.InvalidateControl(controlID)
End Sub

Next, add the following event handler to the ThisAddIn class.

Public Sub OnVisibleChanged( _
 ByVal sender As Object, ByVal e As EventArgs)

   Me.ribbon.Refresh("tbTaskPane")
End Sub

Finally, add the following line of code as the last line in the ThisAddIn_Startup event handler.
```
AddHandler tp.VisibleChanged, AddressOf Me.OnVisibleChanged
```
On the File menu, click Save All to save your work.
On the Build menu, click Build Solution and ensure the project compiles with no errors.
On the Debug menu, click Start Debugging to test the solution. Excel starts and a workbook is displayed.
In Excel, click the Adventure Works tab.
Click the Configure Task Pane toggle button to display the task pane.
Close the task pane by using the X button in the upper-right corner of the pane. Notice how the toggle button’s state changes.
Close Excel, and return to Visual Studio 2005.

To control the enabled state of the Load button, you must make modifications to the add-in similar to those in the previous procedures. In addition, you must modify the user control to communicate whether the state of the add-in configuration is valid; in other words, whether or not a server instance name is set.

To validate the Load button

Double-click the Ribbon1.xml node and replace the enabled attribute of the btnLoad button with the following attribute.
```
getEnabled="btnLoad_getEnabled"
```
Next, add the following class field to the Ribbon1 class in the Ribbon1.vb file.
```
Public configOK As Boolean = False
```

Then add the following callback procedure to the Ribbon1 class.

Public Function btnLoad_getEnabled( _
   ByVal control As Office.IRibbonControl) As Boolean

   Return configOK
End Function

On the File menu, click Save All to save your work.
In Solution Explorer, right-click the Configuration.vb node, and then click View Code to open the code window.
Add the following class-level event declaration to the Configuration class.
```
Public Event ConfigChanged As System.EventHandler
```

Add the following btnSave_Click event handler to trigger the ConfigChanged event when the SQLServerInstance setting is changed. The modified procedure should appear as follows.

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

   If Me.txtSQLServer.Text IsNot Nothing AndAlso _
      Me.txtSQLServer.Text.Length > 0 Then

      My.Settings.SQLServerInstance = Me.txtSQLServer.Text
      RaiseEvent ConfigChanged(Me, EventArgs.Empty)
   End If
End Sub

Next, add the following event handler to the ThisAddIn class in the ThisAddIn.vb file.

Public Sub OnConfigChanged( _
 ByVal sender As Object, ByVal e As EventArgs)

   Me.ribbon.configOK = True
   Me.ribbon.Refresh("btnLoad")
End Sub

Add the following lines of code at the end of the existing ThisAddIn_Startup event handler.

AddHandler ctl.ConfigChanged, AddressOf Me.OnConfigChanged
If Not My.Settings.SQLServerInstance = "Unknown" Then
  Me.ribbon.configOK = True
  Me.ribbon.Refresh("btnLoad")
End If

On the File menu, click Save All to save your work.
On the Build menu, click Build Solution and ensure that the project compiles with no errors.
On the Debug menu, click Start Debugging to test the solution. Excel starts.
In Excel, select the Adventure Works tab.
Click Configure Task Pane to display the task pane.
In the SQL Server Instance text box, type a valid server name, and then click Save. Notice that this enables Load.
Exit Excel, and return to Visual Studio 2005.
On the Window menu, click Close All Documents.

Loading the SQL Server Data

When the user clicks the Load button, you want the add-in to access the SQL Server 2005 instance specified by the user, execute the custom stored procedure GetEmployeePersonalInfo, gather the resulting data into a dataset specified in the System.Data.DataSet namespace, and load the data into a table in the current worksheet. The code in the following procedure does just that.

To configure the Load button

In Solution Explorer, double-click the ThisAddIn.vb node to open the code window.

Add the following code to the ThisAddIn class.

' Put in the class declarations section.
Private employeeData As DataSet
Private employeeWS As Excel.Worksheet
Private rngList As Excel.Range

Friend Sub LoadData()
   Try
     Me.Application.ScreenUpdating = False

     Dim connectionString As String = String.Format( _
       "Server={0};Database=AdventureWorks;" & _
       "Trusted_Connection=True;", My.Settings.SQLServerInstance)

     Dim sp As String = "HumanResources.GetEmployeePersonalInfo"
     Using con As New SqlConnection(connectionString)
       Using cmd As New SqlCommand(sp, con)
         cmd.CommandType = CommandType.StoredProcedure
         Dim sda As New SqlDataAdapter(cmd)
        employeeData = New DataSet
         sda.Fill(employeeData)

         Dim dt As DataTable = employeeData.Tables(0)

         Dim endPoint As String = Me.Application.ConvertFormula( _
           "R" & dt.Rows.Count + 1 & "C" & dt.Columns.Count, _
           Excel.XlReferenceStyle.xlR1C1, _
           Excel.XlReferenceStyle.xlA1, _
           Excel.XlReferenceType.xlRelative)

         Dim strRange As String = "A1:" & endPoint

         employeeWS = DirectCast(Me.Application.Sheets.Add(), _
           Excel.Worksheet)
         employeeWS.Name = "Employees"

         rngList = _ 
           DirectCast(employeeWS.Range(strRange), Excel.Range)
         rngList.Value2 = Me.ConvertDataTableToArray(dt)

         Dim lo As Excel.ListObject
         lo = employeeWS.ListObjects.Add( _
           Excel.XlListObjectSourceType.xlSrcRange, _
           rngList, , Excel.XlYesNoGuess.xlYes)
         lo.Name = "Employees"

         rngList.Columns.AutoFit()

         employeeWS.Activate()

        ' AddHandler employeeWS.Change, _
        '  AddressOf Me.employeeWS_Change

       End Using
     End Using
   Catch ex As Exception
     MsgBox("An unexpected error occured." & vbCrLf & _
       ex.Message, MsgBoxStyle.Exclamation, ex.Source)
   Finally
     Me.Application.ScreenUpdating = True
   End Try
End Sub


Private Function ConvertDataTableToArray( _
   ByVal dt As DataTable) As Object(,)

   Dim c As Integer = 0
   Dim data(dt.Rows.Count, dt.Columns.Count - 1) As Object

   For Each col As DataColumn In dt.Columns
     data(0, c) = col.ColumnName
     c += 1
   Next

   Dim dr As DataRow
   For i As Integer = 0 To dt.Rows.Count - 1
     dr = dt.Rows(i)
     For j As Integer = 0 To dt.Columns.Count - 1
       data(i + 1, j) = dr(j)
     Next j
   Next i

   Return data
End Function

This code connects to the AdventureWorks database on the server specified by the user. It executes the GetEmployeePersonalInfo stored procedures and loads the data into a dataset. After loading, the code creates an Excel worksheet and an Excel Range that contains all the data, including an extra row for header data. It then uses a helper method, the ConvertDataTableToArray method, to repackage the data into a matrix array of Objects. The code puts the array into the Range and then wraps the Range in a ListObject object, which creates a Table object that Excel formats and adds the Auto Filter column headers. Finally, the code auto sizes the columns to fit the data and activates the new worksheet.

Next, connect the LoadData method to a Click event handler for the Load button.
In Solution Explorer, double-click the Ribbon1.vb node.

In the code window, add the following class field for the Ribbon1 class.
```
Public Parent As ExcelData.ThisAddIn
```

Add the following callback procedure to the Ribbon1 class.

Public Sub OnLoadData(ByVal control As Office.IRibbonControl)
   Parent.LoadData()
End Sub

Add the following line of code at the end of the ThisAddIn_Startup event handler.
```
Me.ribbon.Parent = Me
```
Add the following namespace reference above the Public Class ThisAddIn statement.
```
Imports System.Data.SqlClient
```
On the File menu, click Save All to save your work.
On the Build menu, click Build Solution and ensure that the project compiles with no errors.
On the Debug menu, click Start Debugging to test the solution.
In Excel, select the Adventure Works tab.
Click Configure Task Pane to deploy that task pane.
Type the name of your server in the SQL Server Instance text box on the Task Pane.
After the Load button is enabled, click it. The result should appear as shown in Figure 6.

Figure 6. The Employees data is loaded from the custom add-in
Exit Excel without saving the workbook, and return to Visual Studio 2005.
In Visual Studio 2005, on the Window menu, click Close All Documents.

Saving Changes Back to the Database

To save changes back to the database, you must tell the add-in what data the user changed. In the following procedures, you add an event handler that captures the Worksheet_Changed event. The event handler causes the add-in to update the in-memory dataset with changes as they occur. After the first changes occur, the add-in enables the Save button on the Office Fluent Ribbon. Finally, when the user clicks Save, the add-in posts the changes back to SQL Server by using the existing uspUpdateEmployeePersonalInfo stored procedure.

To save changes to the database

First, double-click the Ribbon1.xml node, and replace the enabled attribute for the btnSave button with the following attribute.
```
getEnabled="btnSave_getEnabled"
```
In Solution Explorer, double-click the Ribbon1.vb node to open the code window.
Add the following code class field in the Ribbon1 class.
```
Public dataChanged As Boolean = False
```

Add the following callback procedure to the Ribbon1 class.

Public Function btnSave_getEnabled( _
 ByVal control As Office.IRibbonControl) As Boolean

   Return dataChanged
End Function

Double-click the ThisAddin.vb node to open the code window and add the following code. This code handles the worksheet Change event and saves the data back to the server.

Private Sub employeeWS_Change( _
  ByVal Target As Microsoft.Office.Interop.Excel.Range)

   If Me.Application.Intersect(rngList, Target) IsNot Nothing Then
     If Target.Row > 1 Then
       Dim intRow As Integer = Target.Row - 2
       Dim intColumn As Integer = Target.Column - 1

       Dim dt As DataTable = employeeData.Tables(0)
       dt.Rows(intRow)(intColumn) = Target.Value
       Me.ribbon.dataChanged = True
       Me.ribbon.Refresh("btnSave")
     End If
   End If

End Sub

Friend Sub SaveData()
   Try
     Dim dv As New DataView(employeeData.Tables(0))
     dv.RowStateFilter = DataViewRowState.ModifiedCurrent
     Dim connectionString As String = String.Format( _
       "Server={0};Database=AdventureWorks;" & _
       "Trusted_Connection=True;", My.Settings.SQLServerInstance)

     Dim sp As String = "HumanResources.uspUpdateEmployeePersonalInfo"

     Using con As New SqlConnection(connectionString)
       Using cmd As New SqlCommand(sp, con)
         cmd.CommandType = CommandType.StoredProcedure

         cmd.Parameters.Add(BuildPrm("RETURN_VALUE", _
           SqlDbType.Int, DbType.Int32, _
           ParameterDirection.ReturnValue, 4))

         cmd.Parameters.Add(BuildPrm("@EmployeeID", _
           SqlDbType.Int, DbType.Int32, _
           ParameterDirection.Input, 4))

         cmd.Parameters.Add(BuildPrm("@NationalIDNumber", _
           SqlDbType.NVarChar, DbType.String, _
           ParameterDirection.Input, 15))

         cmd.Parameters.Add(BuildPrm("@BirthDate", _
           SqlDbType.DateTime, DbType.Date, _
           ParameterDirection.Input, 8))

         cmd.Parameters.Add(BuildPrm("@MaritalStatus", _
           SqlDbType.NChar, DbType.String, _
           ParameterDirection.Input, 1))

         cmd.Parameters.Add(BuildPrm("@Gender", _
           SqlDbType.NChar, DbType.String, _
           ParameterDirection.Input, 1))

         con.Open()

         For Each dvr As DataRowView In dv

           cmd.Parameters.Item(1).Value = dvr(0)
           cmd.Parameters.Item(2).Value = dvr(9)
           cmd.Parameters.Item(3).Value = dvr(12)
           cmd.Parameters.Item(4).Value = dvr(10)
           cmd.Parameters.Item(5).Value = dvr(11)

           cmd.ExecuteNonQuery()
         Next
       End Using
     End Using

     MsgBox("Changes were saved to the server.", _
       MsgBoxStyle.Information, "Save")

     Me.ribbon.dataChanged = False
     Me.ribbon.Refresh("btnSave")
   Catch ex As Exception
     MsgBox("An unexpected error occured." & vbCrLf & _
             ex.Message, MsgBoxStyle.Exclamation, ex.Source)
   End Try
End Sub

Private Function BuildPrm(ByVal Name As String, _
  ByVal SType As SqlDbType, ByVal Type As DbType, _
  ByVal Direction As ParameterDirection, ByVal Size As Integer) _
  As SqlParameter

   Dim prm As New SqlParameter(Name, SType)
   With prm
     .DbType = Type
     .Direction = Direction
     .Size = Size
   End With

   Return prm
End Function

In the existing LoadData procedure, find the following two lines of commented code. Uncomment each statement by removing the apostrophe.
```
' AddHandler employeeWS.Change, _
'  AddressOf Me.employeeWS_Change
```

In the Ribbon1.vb code window, add the following code to the Ribbon1 class.

Public Sub OnSave(ByVal control As Office.IRibbonControl)
   Parent.SaveData()
End Sub

On the File menu, click Save All to save your work.
On the Build menu, click Build Solution and ensure the project compiles with no errors.
On the Debug menu, click Start Debugging to test the solution.
Click the Adventure Works tab, and then click Configure Task Pane.
Type the name of your server in the text box, and click Save on the task pane.
On the tab, click Load to load the data into the worksheet.
Modify any of the five columns listed in the SaveData method (NationalIDNumber, BirthDate, MaritalStatus, or Gender, but excluding the EmployeeID column), and then click Save on the tab to save the change back to the database.
You can verify the change by inspecting the HumanResources.Employee table in the AdventureWorks database in the SQL Server Management Studio.
When you finish testing the project, exit Excel and Visual Studio 2005.

Conclusion

Visual Studio 2005 Tools for Office SE and Excel 2007 provide a rich development platform for building data-enabled solutions. With just a little bit of code and the Office Fluent Ribbon, it is easy to enable your users to use the rich analysis features and effective UI of Excel 2007 with a SQL Server 2005 database and stored procedures.

Additional Resources

You can find more information on the techniques described in this article at the following locations:

About the Author

Brian A. Randell is a senior consultant with MCW Technologies, LLC. Brian spends his time teaching technologies based on the Microsoft .NET Framework to developers and exploring new and emerging technologies. He also helps clients worldwide with system design and implementation. Brian is a Microsoft MVP. You can reach him from his blog.