Factoring Add-ins for the 2007 Office System By Using Visual Studio 2005 Tools for the Office System SE

Summary: Learn how to create an add-in for Microsoft Office Excel 2007 that takes advantage of new application-level task pane support and Microsoft Office Fluent Ribbon support. Then see how you can modify the Excel 2007 solution so that you can use it from a Microsoft Office Word 2007 add-in. (28 printed pages)

Brian A. Randell, MVP, MCW Technologies, LLC

January 2008

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

Contents

Overview

Creating the Data Access View

Creating Excel 2007 Add-Ins

Adding Task Panes

Adding Office Fluent Ribbon Support

Completing the Add-in

Factoring the Office Fluent Ribbon Code

Factoring the Custom Task Pane Code

Adding Support for Microsoft Office Word 2007

Conclusion

Additional Resources

About the Author

Overview

With the release of the 2007 Microsoft Office system and Microsoft Visual Studio 2005 Tools Second Edition for the 2007 Microsoft Office System (Visual Studio 2005 Tools for Office SE), building rich solutions with Microsoft Visual Basic or Microsoft Visual C# is easier. Visual Studio 2005 Tools for Office SE allows you to create rich application level add-ins for Microsoft Office Excel 2007, Microsoft Office InfoPath 2007, Microsoft Office Outlook 2007, Microsoft Office PowerPoint 2007, Microsoft Office Visio 2007, and Microsoft Office Word 2007. However, sometimes new tools that offer ease of development can also encourage bad design. One remedy is to create solutions that you can easily modify to work with multiple applications. That way you can put your resources into creating one good design and then reuse that design, with minor changes, for other purposes.

In this article, you create an add-in for Office Excel 2007 that allows you to retrieve data from the Microsoft SQL Server 2005 AdventureWorks database and import it into an active Excel workbook as shown in Figure 1. The add-in takes advantage of new application-level task pane support and Microsoft Office Fluent Ribbon support.

Figure 1. The custom Excel 2007 add-in loading data from Adventure Works into Excel

After you verify that the add-in works as you expect, you factor the solution into discrete components that you can then use from the Word 2007 add-in shown in Figure 2.

Figure 2. The custom Word 2007 add-in using all same supporting assemblies as the Excel solution

Creating the Data Access View

To work through the scenarios in this article, you need a copy of Visual Studio 2005 that supports Visual Studio 2005 Tools for Office SE. You must install Visual Studio 2005 Tools for Office SE, Excel 2007, and Word 2007. In addition, you need a version of SQL Server 2005. Any edition works provided it has a copy of the AdventureWorks sample database installed.

The AdventureWorks database has a rich schema with many tables and views. To make the data access portion of this article easier to understand, you can create a predefined view with the following T-SQL script.

[SQL]

CREATE VIEW [dbo].[Bikes]
AS
SELECT 
Production.Product.ProductID, 
Production.Product.ProductNumber, 
Production.Product.Name, 
Production.ProductDescription.Description, 
Production.Product.ListPrice, 
Production.ProductPhoto.ThumbNailPhoto, 
Production.ProductPhoto.LargePhoto
FROM 
Production.Product 
INNER JOIN 
Production.ProductProductPhoto ON 
Production.Product.ProductID = 
Production.ProductProductPhoto.ProductID 
INNER JOIN
Production.ProductPhoto ON 
Production.ProductProductPhoto.ProductPhotoID = 
Production.ProductPhoto.ProductPhotoID 
INNER JOIN
Production.ProductSubcategory ON 
Production.Product.ProductSubcategoryID = 
Production.ProductSubcategory.ProductSubcategoryID 
INNER JOIN
Production.ProductCategory ON 
Production.ProductSubcategory.ProductCategoryID = 
Production.ProductCategory.ProductCategoryID 
INNER JOIN
Production.ProductModel ON 
Production.Product.ProductModelID = 
Production.ProductModel.ProductModelID 
INNER JOIN
Production.ProductModelProductDescriptionCulture ON 
Production.ProductModel.ProductModelID = 
Production.ProductModelProductDescriptionCulture.ProductModelID 
INNER JOIN
Production.ProductDescription ON 
Production.ProductModelProductDescriptionCulture.ProductDescriptionID = 
Production.ProductDescription.ProductDescriptionID
WHERE 
(Production.ProductModelProductDescriptionCulture.CultureID = N'en') 
AND 
(Production.ProductCategory.ProductCategoryID = 1)

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

Note
While you can write Visual Basic and Visual C# in a very similar fashion, the code included in this article generally uses built-in language constructs—such as Visual Basic’s MsgBox and WithEvents features—instead of the .NET Framework alternatives.

Creating Excel 2007 Add-Ins

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

To create the Excel 2007 project

1. 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, for example, the menu items appear slightly differently. You may need to adjust the instructions below for your specific environment.

2. On the File menu, click New Project.

3. In the New Project dialog box, in the Project types pane, click Visual Basic.

4. Expand the Office node, and then select the 2007 Add-ins node.

5. In the Templates pane of the dialog box, click the Excel Add-in template.

6. In the Name field, type Excel-AW and then choose a location to save the project.

7. In the Solution Name field, type VSTO-AW. Ensure that the Create directory for solution option is selected and click OK.

   Visual Studio 2005 creates the solution with an Excel 2007 application add-in project and a Windows Installer Setup project.

8. On the Window menu, click Close All Documents.

In the following sections, you add the user interface (UI), the data access logic, and bind the data to the add-in. The first step is to create an application-level task pane. The task pane is used to gather 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 in the Office application of choice.

To add a custom task pane

1. In Visual Studio 2005, with the VSTO-AW solution loaded, right-click the Excel-AW node in Solution Explorer,point to Add and then click User Control.

2. In the Add New Item dialog box, type AWData in the Name field and click Add.

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

   The database you are using for this solution is the SQL Server 2005 sample AdventureWorks database. The AdventureWorks database contains data for Adventure Works Cycles, a fictitious large, multinational manufacturing company. The user control will display data from the database and provide a way for the add-in to transfer the information to the active Excel workbook. Therefore, the next step is to set up a data source connection to SQL Server 2005.

3. On the Data menu, click Add New Data Source.

4. On the first page of the Data Source Configuration Wizard, ensure that Database is selected and click Next.

5. Select New Connection and create a connection to the AdventureWorks database and then click Next.

6. When asked to save the connection string, accept the default and click Next.

7. On the Choose Your Database Objects page, expand the Views node and select the Bikes view and click Finish.

   The wizard generates a new strongly-typed dataset. Strongly-typed datasets make it easier to work with data retrieved from a database, helping you reduce errors and making data binding easier.

8. On the Data menu, click Show Data Sources.

9. In the Data Sources window, expand the Bikes node so that you can see all of the fields.

10. Click the down arrow and click Label to change all of the fields, except the two image fields, so they are configured to use Label controls instead of TextBox controls.

11. Change the two image fields to use PictureBox controls.

12. From the Data Sources window, drag all of the fields except the LargePhoto field to the design surface of the AWData user control.

13. After you add all of the controls, delete the autogenerated label for the thumbnail photo.

14. Select the PictureBox control for the thumbnail photo and set its SizeMode property to AutoSize.

15. On the File menu, click Save All to save your work.

Now that you have created the user control and linked to the database, you need to configure the add-in to register the control so that the add-in can display the control in the application task pane.

To register the control

1. In Solution Explorer, expand the Excel node, and then right-click the ThisAddin file.

2. Click View Code from the context menu.

3. Replace the ThisAddIn_Startup method with the following code.

   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 AWData
     Dim tp As Microsoft.Office.Tools.CustomTaskPane
     tp = Me.CustomTaskPanes.Add(ctl, "AdventureWorks")
     tp.DockPosition = _
     Microsoft.Office.Core.MsoCTPDockPosition.msoCTPDockPositionRight
     If Me.ribbon IsNot Nothing Then
       Me.ribbon.TaskPane = tp
     End If
     tp.Visible = True
   End Sub
   

4. On the File menu, click Save All to save your work.

5. On the Build menu, click Build Solution and ensure that the project compiles with no errors.

6. On the Debug menu, click Start Debugging to test the solution. Excel 2007 starts and the task pane is displayed.

7. Exit Excel and return to Visual Studio 2005.

8. In Solution Explorer, select the AWData.vb user control.

9. On the View menu, click Designer.

10. In the Properties pane, select the Events icon.

11. Scroll to the Load event and double-click the event name to generate an event handler.

12. In the event handler, add the following code.

    Me.BikesTableAdapter.Fill(Me.AdventureWorksDataSet.Bikes)
    

13. On the File menu, click Save All to save your work.

14. On the Build menu, click Build Solution and ensure that the project compiles with no errors.

15. On the Debug menu, click Start Debugging to test the solution.

    You can now browse the data returned from the AdventureWorks database in the task pane.

16. Exit Excel and return to Visual Studio 2005.

17. On the Window menu, click Close All Documents.

Adding Office Fluent Ribbon Support

At this point, the add-in displays the application task pane automatically at startup. However, you want to be able to open the task pane and display it again if you have closed it after startup. To do this, you need to add a toggle button on the Office Fluent Ribbon so you can easily display and hide the task pane as needed.

To customize the Office Fluent Ribbon

1. On the Project menu, click Add New Item.

2. In the Add New Item dialog box, click Ribbon Support and 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.

3. Double-click Ribbon1.xml to open the code window.

4. Replace the existing XML content with the following code.

   <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="OnLoad">
     <ribbon>
       <tabs>
         <tab idMso="TabInsert">
           <group id="awData"
                  label="AdventureWorks">
             <toggleButton id="showDataTaskPane" 
                size="large"
                label="Data Task Pane"
                screentip="Show the Data Task Pane."
                onAction="showDataTaskPane_onAction" 
                getPressed="showDataTaskPane_getPressed"
                imageMso="GetExternalDataExistingConnections" />
           </group>
         </tab>
       </tabs>
     </ribbon>
   </customUI>
   

This markup instructs the Office application to add a toggle button for a new group to an existing Office Fluent Ribbon tab. It also defines links to two callback procedures. Callbacks procedures are used by the Office Fluent Ribbon to communicate with the add-in.

In Solution Explorer, double-click Ribbon1.vb 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 TaskPane As Microsoft.Office.Tools.CustomTaskPane

  Public Sub New()

  End Sub

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

    Return GetResourceText("Excel_AW.Ribbon1.xml")
  End Function

#Region "Ribbon Callbacks"

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

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

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

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

    If TaskPane IsNot Nothing Then
      Return TaskPane.Visible
    Else
      Return False
    End If
  End Function
#End Region

#Region "Helpers"
  Private Shared Function GetResourceText( _
    ByVal resourceName As String) As String

    Dim asm As Assembly = Assembly.GetExecutingAssembly()
    Dim resourceNames() As String = asm.GetManifestResourceNames()
    For i As Integer = 0 To resourceNames.Length - 1
      If String.Compare(resourceName, resourceNames(i), _
      StringComparison.OrdinalIgnoreCase) = 0 Then
        Using resourceReader As StreamReader = New StreamReader( _
        asm.GetManifestResourceStream(resourceNames(i)))
          If resourceReader IsNot Nothing Then
            Return resourceReader.ReadToEnd()
          End If
        End Using
      End If
    Next
    Return Nothing
  End Function

#End Region

End Class

1. Double-click ThisAddIn.vb node and, in 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 AWData
   Dim tp As Microsoft.Office.Tools.CustomTaskPane
   

2. Change the Dim command in each line to Private.

3. On the File menu, click Save All to save your work.

4. On the Build menu, click Build Solution and ensure that the project compiles with no errors.

5. On the Debug menu, click Start Debugging to test the solution.

6. In Excel 2007, click the Insert tab. You should see a group on the far right labeled AdventureWorks with a toggle button labeled Data Task Pane.

7. Click Data Task Pane and notice that the task pane is displayed. Click the button again and the task pane disappears.

8. Exit Excel, and return to Visual Studio 2005.

9. On the Window menu, click Close All Documents.

Completing the Add-in

At this point, the add-in is nearly complete. Your next step is to provide a way for the user to insert the data that appears in the task pane into an Excel worksheet. To do this, you must modify the user control and add some additional code that will load the data into the worksheet.

To load the data into the worksheet

1. In Solution Explorer, right-click the AWData.vb user control and click View Code.

2. At the top of the code window, add the following code.

   Imports System.IO
   Imports Core = Microsoft.Office.Core
   Imports Excel = Microsoft.Office.Interop.Excel
   

3. On the View menu, click Designer.

4. In the Properties pane, select the BikesBindingNavigator.

5. Right-click the BikeBindingNavigator in the component tray and select Edit Items.

6. In the Items Collection Editor, click Add button to add a new button to the control.

7. Change the Name property of the new button to tsbInsert.

8. Change the DisplayStyle property to Text.

9. Change the Text property to Insert.

10. Click OK to close the editor.

11. The new Insert button appears at the top of the user control. If you do not see the button, expand the user control to the right until the Insert button appears.

12. Select Insert, and then, in the Properties pane, select the Events icon.

13. Scroll to the Click event and double-click the event name to generate an event handler.

14. In the event handler, add the following code.

    Dim xl As Excel.Application = Globals.ThisAddIn.Application
    Dim range As Excel.Range = xl.ActiveCell
    
    With range
      .Offset(0, 0).Value2 = Me.ProductIDLabel1.Text
      .Offset(1, 0).Value2 = Me.ProductNumberLabel1.Text
      .Offset(2, 0).Value2 = Me.ListPriceLabel1.Text
      .Offset(3, 0).Value2 = Me.DescriptionLabel1.Text
      .Offset(4, 0).Value2 = "Image:"
    End With
    
    With range.CurrentRegion
      .HorizontalAlignment = Excel.Constants.xlGeneral
      .VerticalAlignment = Excel.Constants.xlTop
      .Font.Bold = True
      .Columns.AutoFit()
    End With
    
    range.Offset(0, 1).Value2 = ProductIDLabel1.Text
    range.Offset(1, 1).Value2 = ProductNumberLabel1.Text
    
    Dim priceRange As Excel.Range = range.Offset(2, 1)
    With priceRange
      .NumberFormat = "$#,##0.00"
      .Value2 = ListPriceLabel1.Text
    End With
    
    Dim descRange As Excel.Range = range.Offset(3, 1)
    With descRange
      .HorizontalAlignment = Excel.Constants.xlGeneral
      .VerticalAlignment = Excel.Constants.xlTop
      .WrapText = True
      .ColumnWidth = 50
    End With
    range.Offset(3, 1).Value2 = DescriptionLabel1.Text
    
    Dim dvr As DataRowView = _
      CType(Me.BikesBindingSource.Current, DataRowView)
    Dim br As AdventureWorksDataSet.BikesRow = _
      CType(dvr.Row, AdventureWorksDataSet.BikesRow)
    
    Using bmp As New Bitmap(New MemoryStream(br.LargePhoto))
      Dim imgLeft, imgTop, imgWidth, imgHeight As Integer
      imgLeft = CInt(range.Offset(4, 1).Left)
      imgTop = CInt(range.Offset(4, 1).Top) + 2
      imgWidth = bmp.Width
      imgHeight = bmp.Height
    
      Dim tmpPath As String = Path.GetTempFileName()
      bmp.Save(tmpPath)
    
      Dim ws As Excel.Worksheet = _
        CType(xl.ActiveSheet, Excel.Worksheet)
      Dim s As Excel.Shape = ws.Shapes.AddPicture(tmpPath, _
        Core.MsoTriState.msoFalse, Core.MsoTriState.msoCTrue, _
        imgLeft, imgTop, imgWidth, imgHeight)
      s.Placement = Excel.XlPlacement.xlMove
    End Using
    

15. On the File menu, click Save All to save your work.

16. On the Build menu, click Build Solution and ensure that the project compiles with no errors.

17. On the Debug menu, click Start Debugging to test the solution.

18. On the task pane, click Insert. The information in the task pane is inserted into the worksheet. If you do not see the Insert button, expand the left-edge of the task pane until the Insert button appears.

19. When you are finished, close Excel and return to Visual Studio 2005.

At this point, the add-in is complete. However, the only way to reuse the solution in another add-in would be to insert the code into the new solution by using cut and paste. In the next section, you split the solution so that the code is segmented (factored) into three assemblies. One assembly stores all of the UI code and markup for the Office Fluent Ribbon. A second assembly stores the code and layout related to the user control and data access. Finally, the third assembly contains all of the host-specific add-in code (in this case, code that applies to Excel 2007). After you have factored the solution, you can hook the assemblies into a Word 2007 add-in.

Factoring the Office Fluent Ribbon Code

Factoring the Office Fluent Ribbon code involves adding a new class library project to the solution. In the following procedures, you import the Ribbon class and XML markup file into the new class library from the add-in project. You then set the necessary references. Finally, you change the code so that the Office Fluent Ribbon markup is loaded from the new assembly and all the callback procedures are linked.

To factor the Office Fluent Ribbon code

1. In Visual Studio 2005 in the VSTO-AW solution, on the File menu, point to Add, and then click New Project.

2. In the Add New Project dialog box in the Project types pane, select Visual Basic to expand the node.

3. Click the Windows node.

4. In the Templates pane, click the Class Library template.

5. Change the Name to Ribbon-AW and click OK.

6. In the Solution Explorer, right-click Class1.vb and click Delete. Click OK to confirm the deletion.

7. In the Solution Explorer, right-click the Ribbon-AW node, point to Add, and then click Existing Item.

8. In the Add Existing Item dialog box, navigate to the sources files for Excel-AW folder that contains the Ribbon1.vb file, select the file and click Add.

9. Using the Properties pane, Rename the file Ribbon1.vb to AWData.vb.

10. In the Solution Explorer, right-click the Ribbon-AW project node and click Properties.

11. Click the Resources tab.

12. Click Strings and select Filse from the dropdown menu, click Add Resources, and then click Add Existing File.

13. Navigate to the folder containing the source files for the Excel-AW project and select the Ribbon1.xml file, and then click Open.

14. Using the Properties pane, change the name from Ribbon1 to AWData.

15. On the File menu, click Save All to save your work.

16. In the AWData.vb code window, replace the one line of code in the GetCustomUI method with the following:

    Return My.Resources.AWData
    

17. Delete the following block of code from AWData.vb.

    #Region "Helpers"
      Private Shared Function GetResourceText( _
        ByVal resourceName As String) As String
    
        Dim asm As Assembly = Assembly.GetExecutingAssembly()
        Dim resourceNames() As String = asm.GetManifestResourceNames()
        For i As Integer = 0 To resourceNames.Length - 1
          If String.Compare(resourceName, resourceNames(i), _
          StringComparison.OrdinalIgnoreCase) = 0 Then
            Using resourceReader As StreamReader = New StreamReader( _
            asm.GetManifestResourceStream(resourceNames(i)))
              If resourceReader IsNot Nothing Then
                Return resourceReader.ReadToEnd()
              End If
            End Using
          End If
        Next
        Return Nothing
      End Function
    
    #End Region
    

18. In Solution Explorer, right-click the Ribbon-AW node and click Add Reference.

19. Click the .NET tab and select the following assemblies (use the Ctrl key to multi-select):

    • Microsoft.Office.Tools.Common

    • Microsoft.Office.Tools.Common2007

    • Microsoft.VisualStudio.Tools.Applications.Runtime

20. Click OK.

21. In Solution Explorer, right-click the Ribbon-AW node and click Add Reference.

22. Click the COM tab, select Microsoft Office 12.0 Object Library, and click OK.

23. In Solution Explorer, right-click the Ribbon1.xml node in the Excel-AW project and click Delete. Confirm the deletion.

24. In Solution Explorer, right-click the Excel-AW node and click Add Reference.

25. Click the Projects tab, select Ribbon-AW, and click OK.

26. In the Excel-AW project, cut the RequestService override procedure from Ribbon1.vb and paste it into ThisAddIn class in the ThisAddIn.vb file.

27. Cut the Office Fluent Ribbon reference declaration Private ribbon As Ribbon1 from the Ribbon1.vb file and paste it into ThisAddIn class.

28. Change the class that is referenced and instantiated for the Office Fluent Ribbon variable from Ribbon1 to Ribbon_AW.AWData. ThisAddIn class in the Excel-AW project should look like the following code.

    Public Class ThisAddIn
      Private ctl As New AWData
      Private tp As Microsoft.Office.Tools.CustomTaskPane
      Private ribbon As Ribbon_AW.AWData
    
      Private Sub ThisAddIn_Startup( _
        ByVal sender As Object, ByVal e As System.EventArgs) _
        Handles Me.Startup
    
        ' Start of VSTO generated code
        Me.Application = CType( _
          Microsoft.Office.Tools.Excel.ExcelLocale1033Proxy.Wrap( _
          GetType(Excel.Application), Me.Application), _
          Excel.Application)
        ' End of VSTO generated code
        tp = Me.CustomTaskPanes.Add(ctl, "AdventureWorks")
        tp.DockPosition = _
          Microsoft.Office.Core.MsoCTPDockPosition.msoCTPDockPositionRight
        If Me.ribbon IsNot Nothing Then
          Me.ribbon.TaskPane = tp
        End If
      End Sub
    
      Private Sub ThisAddIn_Shutdown( _
        ByVal sender As Object, ByVal e As System.EventArgs) _
        Handles Me.Shutdown
    
      End Sub
    
      Protected Overrides Function RequestService( _
      ByVal serviceGuid As Guid) As Object
    
        If serviceGuid = _
          GetType(Office.IRibbonExtensibility).GUID Then
    
          If ribbon Is Nothing Then
            ribbon = New Ribbon_AW.AWData
          End If
          Return ribbon
        End If
    
        Return MyBase.RequestService(serviceGuid)
      End Function
    End Class
    

29. Delete the Ribbon1.vb node from the Excel-AW project.

30. On the File menu, click Save All to save your work.

31. On the Build menu, click Build Solution and ensure that the project compiles with no errors.

32. On the Debug menu, click Start Debugging to test the solution.

33. Verify that the Data Task Pane button on the Office Fluent Ribbon still displays and hides the task pane.

34. Close Excel when you are finished testing the project.

Now that you have segmented the Office Fluent Ribbon add-in solution into three assemblies, you must also segment the code that creates the custom task pane.

Factoring the Custom Task Pane Code

Separating the user control into separate assemblies is a bit more complicated than the process for factoring the add-in solution. First, you need to provide the user control with properties for all of the data currently exposed as properties of the contained controls. Second, you need to move the code that executes when you click the button on the binding navigator that inserts the data into the Excel workbook to the add-in. You also need to add a custom event to link the assemblies at runtime.

Factoring the custom task pane

1. In Visual Studio 2005 in the VSTO-AW solution, on the File menu, point to Add, and then click New Project.

2. In the Add New Project dialog box, in the Project types pane, expand the Visual Basic node, and then click the Windows node.

3. In the Templates pane of the dialog box, select the Class Library template.

4. Change the Name to TaskPane-AW and click OK.

5. In Solution Explorer, right click Class1.vb and click Delete. Click OK to confirm the deletion.

6. Right-click the TaskPane-AW node, point to Add, and then click Existing Item.

7. In the Add Existing Item dialog box, navigate to the Excel-AW folder that contains the AWData.vb file. Select the file and click Add. Repeat this process, this time adding the AdventureWorksDataSet.xsd file.

8. After you import AdventureWorksDataSet.xsd, you need to set the Custom Tool property of the file to MSDataSetGenerator by using the Properties window.

9. Right-click the AWData node in the TaskPane-AW project and click View Code.

10. Remove all of the Imports statements except for System.IO.

11. Remove all of the code in the tsbInsertData_Click method.

12. In Solution Explorer, right-click AWData.vb in the Excel-AW project and click Delete.

13. On the File menu, click Save All to save your work.

14. On the Build menu, click Build Solution. You should get five compile errors in the TaskPane-AW project. You need to double-click each error, which takes you to the problem. Replace the project prefix Excel_AW with TaskPane_AW.

15. After you fix the compile errors, replace the contents of the AWData.vb class with the following code.

    Imports System.IO
    Imports System.Drawing
    
    Public Class AWData
      Public Event InsertData As System.EventHandler
    
      Private Sub AWData_Load( _
        ByVal sender As System.Object, ByVal e As System.EventArgs) _
        Handles MyBase.Load
    
        Me.BikesTableAdapter.Fill(Me.AdventureWorksDataSet.Bikes)
      End Sub
    
      Private Sub tsbInsertData_Click( _
        ByVal sender As System.Object, ByVal e As System.EventArgs) _
        Handles tsbInsertData.Click
    
        RaiseEvent InsertData(Me, EventArgs.Empty)
      End Sub
      Public ReadOnly Property ProductIDText() As String
        Get
          Return Me.ProductIDLabel.Text
        End Get
      End Property
      Public ReadOnly Property ProductNumberText() As String
        Get
          Return Me.ProductNumberLabel.Text
        End Get
      End Property
      Public ReadOnly Property NameText() As String
        Get
          Return Me.NameLabel.Text
        End Get
      End Property
      Public ReadOnly Property ListPriceText() As String
        Get
          Return Me.ListPriceLabel.Text
        End Get
      End Property
      Public ReadOnly Property DescriptionText() As String
        Get
          Return Me.DescriptionLabel.Text
        End Get
      End Property
    
      Public ReadOnly Property ProductIDData() As String
        Get
          Return Me.ProductIDLabel1.Text
        End Get
      End Property
      Public ReadOnly Property ProductNumberData() As String
        Get
          Return Me.ProductNumberLabel1.Text
        End Get
      End Property
      Public ReadOnly Property NameData() As String
        Get
          Return Me.NameLabel1.Text
        End Get
      End Property
      Public ReadOnly Property ListPriceData() As String
        Get
          Return Me.ListPriceLabel1.Text
        End Get
      End Property
      Public ReadOnly Property DescriptionData() As String
        Get
          Return Me.DescriptionLabel1.Text
        End Get
      End Property
    
      Public ReadOnly Property ThumbnailImage() As Bitmap
        Get
          Return CType(Me.ThumbNailPhotoPictureBox.Image, Bitmap)
        End Get
      End Property
      Public ReadOnly Property LargeImage() As Bitmap
        Get
          Dim dvr As DataRowView = _
            CType(Me.BikesBindingSource.Current, DataRowView)
          Dim br As AdventureWorksDataSet.BikesRow = _
            CType(dvr.Row, AdventureWorksDataSet.BikesRow)
    
          Return New Bitmap(New MemoryStream(br.LargePhoto))
        End Get
      End Property
    End Class
    

16. On the File menu, click Save All to save your work.

17. On the Build menu, click Build Solution.

18. In Solution Explorer, right-click the Excel-AW node, and click Add Reference.

19. Click the Projects tab, click TaskPane-AW, and click OK.

20. Open the ThisAddIn class and replace the contents with the following.

    Imports System.IO
    Imports Core = Microsoft.Office.Core
    
    Public Class ThisAddIn
      Private WithEvents ctl As New TaskPane_AW.AWData
      Private tp As Microsoft.Office.Tools.CustomTaskPane
      Private ribbon As Ribbon_AW.AWData
    
      Private Sub ThisAddIn_Startup( _
        ByVal sender As Object, ByVal e As System.EventArgs) _
        Handles Me.Startup
    
        ' Start of VSTO generated code
        Me.Application = CType( _
          Microsoft.Office.Tools.Excel.ExcelLocale1033Proxy.Wrap( _
          GetType(Excel.Application), Me.Application), _
          Excel.Application)
        ' End of VSTO generated code
        tp = Me.CustomTaskPanes.Add(ctl, "AdventureWorks")
        tp.DockPosition = _
          Microsoft.Office.Core.MsoCTPDockPosition.msoCTPDockPositionRight
        If Me.ribbon IsNot Nothing Then
          Me.ribbon.TaskPane = tp
        End If
      End Sub
    
      Private Sub ThisAddIn_Shutdown( _
        ByVal sender As Object, ByVal e As System.EventArgs) _
        Handles Me.Shutdown
    
      End Sub
    
      Protected Overrides Function RequestService( _
      ByVal serviceGuid As Guid) As Object
    
        If serviceGuid = _
          GetType(Office.IRibbonExtensibility).GUID Then
    
          If ribbon Is Nothing Then
            ribbon = New Ribbon_AW.AWData
          End If
          Return ribbon
        End If
    
        Return MyBase.RequestService(serviceGuid)
      End Function
    
      Private Sub ctl_InsertData(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles ctl.InsertData
    
        Dim range As Excel.Range = Me.Application.ActiveCell
    
        With range
          .Offset(0, 0).Value2 = ctl.ProductIDText
          .Offset(1, 0).Value2 = ctl.ProductNumberText
          .Offset(2, 0).Value2 = ctl.NameText
          .Offset(3, 0).Value2 = ctl.ListPriceText
          .Offset(4, 0).Value2 = ctl.DescriptionText
          .Offset(5, 0).Value2 = "Image:"
        End With
    
        With range.CurrentRegion
          .HorizontalAlignment = Excel.Constants.xlGeneral
          .VerticalAlignment = Excel.Constants.xlTop
          .Font.Bold = True
          .Columns.AutoFit()
        End With
    
        range.Offset(0, 1).Value2 = ctl.ProductIDData
        range.Offset(1, 1).Value2 = ctl.ProductNumberData
        range.Offset(2, 1).Value2 = ctl.NameData
    
        Dim priceRange As Excel.Range = range.Offset(3, 1)
        With priceRange
          .NumberFormat = "$#,##0.00"
          .Value2 = ctl.ListPriceData
        End With
    
        Dim descRange As Excel.Range = range.Offset(4, 1)
        With descRange
          .HorizontalAlignment = Excel.Constants.xlGeneral
          .VerticalAlignment = Excel.Constants.xlTop
          .WrapText = True
          .ColumnWidth = 50
          .Value2 = ctl.DescriptionData
        End With
    
        Dim bmp As Bitmap = ctl.LargeImage
        Dim imgLeft, imgTop, imgWidth, imgHeight As Integer
        imgLeft = CInt(range.Offset(5, 1).Left)
        imgTop = CInt(range.Offset(5, 1).Top) + 2
        imgWidth = bmp.Width
        imgHeight = bmp.Height
    
        Dim tmpPath As String = Path.GetTempFileName()
        bmp.Save(tmpPath)
    
        Dim ws As Excel.Worksheet = _
          CType(Me.Application.ActiveSheet, Excel.Worksheet)
        Dim s As Excel.Shape = ws.Shapes.AddPicture(tmpPath, _
          Core.MsoTriState.msoFalse, Core.MsoTriState.msoCTrue, _
          imgLeft, imgTop, imgWidth, imgHeight)
        s.Placement = Excel.XlPlacement.xlMove
      End Sub
    End Class
    

21. On the File menu, click Save All to save your work.

22. On the Build menu, click Build Solution and ensure that the project compiles with no errors.

23. On the Debug menu, click Start Debugging to test the solution.

24. Close Excel when you finish testing.

After completing these procedures, the project is now factored into three assemblies instead of one. In the following section, you see how this works in a Word 2007 solution.

Adding Support for Microsoft Office Word 2007

The Excel 2007 add-in allows a user to browse product data from the AdventureWorks database and then insert the data into an Excel workbook. In the previous procedures, you factored the code for reuse. In the following steps, you build a Word 2007 add-in to support importing the data from Excel into a Word 2007 document.

To create the Word 2007 add-in

1. Start Visual Studio 2005 if it is not already running.

2. Select File menu, click New Project.

3. In the New Project dialog box in the Project types pane, expand the Visual Basic node, expand the Office node, and select the 2007 Add-ins node.

4. In the Templates pane of the dialog box, select the Word Add-in template.

5. In the Name field, type Word-AW and choose a location for the project.

6. In the Solution Name field, type VSTO-AW-R. Ensure that Create directory for solution is selected, and click OK.

   Visual Studio 2005 creates the solution with a Word application add-in project and a Windows Installer Setup project.

7. On the Window menu, click Close All Documents.

8. In Solution Explorer, right-click the Word-AW node and click Add Reference.

9. Click the Browse tab, the Ribbon-AW.dll file, and click OK.

10. Repeat steps 8 and 9 for the TaskPane-AW.dll file.

11. Replace the contents of ThisAddIn.vb file with the following code.

    Imports System.IO
    Imports Core = Microsoft.Office.Core
    
    Public Class ThisAddIn
      Private WithEvents ctl As New TaskPane_AW.AWData
      Private tp As Microsoft.Office.Tools.CustomTaskPane
      Private ribbon As Ribbon_AW.AWData
    
      Private Sub ThisAddIn_Startup( _
        ByVal sender As Object, ByVal e As System.EventArgs) _
        Handles Me.Startup
    
        tp = Me.CustomTaskPanes.Add(ctl, "AdventureWorks")
        tp.DockPosition = _
          Core.MsoCTPDockPosition.msoCTPDockPositionRight
        If Me.ribbon IsNot Nothing Then
          Me.ribbon.TaskPane = tp
        End If
      End Sub
    
      Private Sub ThisAddIn_Shutdown( _ 
        ByVal sender As Object, ByVal e As System.EventArgs) _ 
        Handles Me.Shutdown
    
      End Sub
    
      Protected Overrides Function RequestService( _ 
        ByVal serviceGuid As Guid) As Object
    
        If serviceGuid = _
          GetType(Office.IRibbonExtensibility).GUID Then
    
          If ribbon Is Nothing Then
            ribbon = New Ribbon_AW.AWData
          End If
          Return ribbon
        End If
    
        Return MyBase.RequestService(serviceGuid)
      End Function
    End Class
    

12. On the File menu, click Save All to save your work.

13. On the Build menu, click Build Solution and ensure that the project compiles with no errors.

14. On the Debug menu, click Start Debugging to test the solution. At this point, you should be able to display and hide the task pane from the Office Fluent Ribbon and browse the data. The only step left to do is add code that is specific to Word.

15. Insert the following method into the ThisAddIn class.

    Private Sub ctl_InsertData( _
      ByVal sender As Object, ByVal e As System.EventArgs) _
      Handles ctl.InsertData
    
      Dim doc As Word.Document = Me.Application.ActiveDocument
    
      Dim bmp As Bitmap = ctl.LargeImage
      Dim tmpPath As String = Path.GetTempFileName()
      bmp.Save(tmpPath)
    
      Dim ils As Word.InlineShape = _
        doc.Range.InlineShapes.AddPicture(tmpPath)
    
      Using sw As New StringWriter
        With ctl
          sw.WriteLine("{0}{1}{2}", _
            .ProductIDText, vbTab, .ProductIDData)
          sw.WriteLine("{0}{1}{2}", _
            .ProductNumberText, vbTab, .ProductNumberData)
          sw.WriteLine("{0}{1}{2}", .NameText, vbTab, .NameData)
          sw.WriteLine("{0}{1}{2}", _
            .ListPriceText, vbTab, .ListPriceData)
          sw.WriteLine(.DescriptionText)
          sw.WriteLine(.DescriptionData)
        End With
    
        doc.Range.InsertBefore(sw.ToString())
      End Using
    End Sub
    

16. On the File menu, click Save All to save your work.

17. On the Build menu, click Build Solution and ensure that the project compiles with no errors.

18. On the Debug menu, click Start Debugging to test the solution.

Your project code now supports two Microsoft Office host applications with no significant code duplication.

Conclusion

Visual Studio 2005 Tools for Office SE, coupled with the 2007 Office system, provides a rich development platform for building solutions. When you build your solutions, plan for re-use. Spend the time upfront to create factored and partitioned solutions. The time spent you spend in the beginning will pay off many times in the end.

Additional Resources

You can find more information on the resources discussed in this article at the following locations:

• AdventureWorks OLTP Database Diagram

• Adventure Works Cycles Business Scenarios

• Customizing the 2007 Office Fluent Ribbon for Developers (Part 1 of 3)

• Customizing the 2007 Office Fluent Ribbon for Developers (Part 2 of 3)

• Customizing the 2007 Office Fluent Ribbon for Developers (Part 3 of 3)

• Creating Custom Task Panes in the 2007 Office System

About the Author

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