Prior to Visual Studio 2008 SP1, you couldn’t use common VSTO features like controls in the document and databinding to list objects at the add-in level.  But with SP1 we introduced a way for you to use these features at the add-in level.  In this section some of the wierd ways Idid things in section 4 will start to make more sense—I designed the code in Section 4 to be easily portable to an add-in.

One thing that won’t port to the add-in level is cached data—that feature only works when code is at the document level.

Choose File > Add > New Project.. and create a new Excel add-in project.  I’ll be using VB in this example.

Add a web reference to the BugService as described in Article 4.  Be sure to name the reference BugService.

Now, double click on your ThisAddIn file.  In the ThisAddIn class, add this as a member variable.  We won’t be adding any our dataset as a member variable because we can’t cache it at the add-in level, so there is no reason to make it a class member variable.

    Public buttonDictionary As New Dictionary(Of Excel.Worksheet, Microsoft.Office.Tools.Excel.Controls.Button)

Next, we will put some calls to our AddListObjectAndButton function.  Add an event handler for the NewWorkbook, WorkbookBeforeClose, and WorkbookOpen events.  You will also put some code in ThisAddIn_Startup.  Also, handle the SheetBeforeRightClick event temporarily until we add a ribbon in the next article—this will make it so that if you right click on a worksheet, the bug data will be added to the workbook.

    Private Sub ThisAddIn_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
        If Application.ActiveWorkbook IsNot Nothing Then
            PopulateSpreadsheet(False)
        End If
    End Sub

    Private Sub ThisAddIn_Shutdown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shutdown

    End Sub

    Private Sub Application_NewWorkbook(ByVal Wb As Excel.Workbook) Handles Application.NewWorkbook
        PopulateSpreadsheet(False)
    End Sub

    Private Sub Application_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Microsoft.Office.Interop.Excel.Range, ByRef Cancel As Boolean) Handles Application.SheetBeforeRightClick
        PopulateSpreadsheet(True)
    End Sub

    Private Sub Application_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, ByRef Cancel As Boolean) Handles Application.WorkbookBeforeClose
        CleanUpDynamicButton()
    End Sub

    Private Sub Application_WorkbookOpen(ByVal Wb As Excel.Workbook) Handles Application.WorkbookOpen
        PopulateSpreadsheet(False)
    End Sub

The rest of the code is almost identical to the code we wrote in Article 4.  PopulateSpreadsheet has these changes from the document to the add-in level.

1) Instead of the line “vstoSheet = GetVstoObject(s)” it changes to “vstoSheet = s.GetVstoObject()”.  At the add-in level, GetVstoObject is an extension method to the Microsoft.Office.Interop.Excel.Worksheet type.

2) We don’t have the “If NeedsFill(“bugDataSet”)” if clause—cached data isn’t supported at the add-in level.

3) Since bugDataset isn’t needed to be declared at the class member level due to no cached data, we declare it local in PopulateSpreadsheet.  Also you might need to add an “Imports System.Data" to the top of the file.

4) We’ll also add a line at the top of PopulateSpreadsheet to detect if the name of the active workbook is “BugWorkbook”—the wokrbook we created in Section 4.  If it is, we’ll immediately return so as not to have our add-in conflict with our document level solution.

Other than these four changes, the remaining code should look very familiar:

  Public Sub PopulateSpreadsheet(ByVal alwaysAdd As Boolean)
        If Application.ActiveWorkbook.Name = "BugWorkbook" Then Exit Sub

        Dim foundList As Excel.ListObject = Nothing
        Dim s As Excel.Worksheet = Nothing
        Dim bugDataset As DataSet


        foundList = FindList()

        ' If not there create it if always add is true
        If foundList Is Nothing Then
            If alwaysAdd = True Then
                s = Application.ActiveSheet
                foundList = s.ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcRange, s.Range("A1", "D2"))
                foundList.Name = "BugListObject"
            Else
                Exit Sub
            End If
        Else
            s = TryCast(foundList.Parent, Excel.Worksheet)
        End If

        ' Dynamically create the list object
        Dim vstoSheet As Microsoft.Office.Tools.Excel.Worksheet
        vstoSheet = s.GetVstoObject()

        ' Do we already have a listobject, if so exit.
        If vstoSheet.Controls.Contains("BugListObject") Then
            Exit Sub
        End If

        Dim list As Microsoft.Office.Tools.Excel.ListObject = vstoSheet.Controls.AddListObject(foundList)

        'If NeedsFill("bugDataset") Then
        Application.StatusBar = "Updating data from bug web service..."
        Dim bugService As BugService.BugService = New BugService.BugService()
        bugDataset = bugService.BulkDataExport("", "")
        'End If

        list.AutoSetDataBoundColumnHeaders = True
        list.DataSource = bugDataset.Tables(0)
        list.Range.Columns.AutoFit()
        list.Disconnect()

        Dim buttonRange As Excel.Range = s.Range("E1")
        Dim pivotButton As Microsoft.Office.Tools.Excel.Controls.Button
        pivotButton = vstoSheet.Controls.AddButton(buttonRange, "CreatePivotTable")
        buttonDictionary.Add(s, pivotButton)
        AddHandler pivotButton.Click, AddressOf pivotButton_Click
        pivotButton.Text = "Pivot"

        Application.StatusBar = ""
        s.Name = "Bug Data"
        s.Activate()
    End Sub

    Private Function FindList() As Excel.ListObject
        ' Do we already have a created list object?
        Dim l As Excel.ListObject = Nothing
        Dim s As Excel.Worksheet = Nothing
        For Each s In Application.ActiveWorkbook.Worksheets
            For Each l In s.ListObjects
                If l.Name = "BugListObject" Then
                    Return l
                End If
            Next
        Next
        Return Nothing
    End Function

    Private Sub pivotButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim s As Excel.Worksheet = Nothing
        Dim pivotCache As Excel.PivotCache
        Dim pivotTables As Excel.PivotTables
        Dim pivotTable As Excel.PivotTable
        Dim pivotField As Excel.PivotField

        s = Application.ActiveWorkbook.Worksheets.Add()
        pivotCache = s.Parent.PivotCaches.Create(Excel.XlPivotTableSourceType.xlDatabase, FindList().Range, Excel.XlPivotTableVersionList.xlPivotTableVersion12)
        pivotTables = s.PivotTables()
        pivotTable = pivotTables.Add(pivotCache, s.Range("A1"))

        pivotTable.AddDataField(pivotTable.PivotFields(4))
        pivotTable.AddFields("Date")

        pivotField = pivotTable.PivotFields("Column")
        pivotField.Orientation = Excel.XlPivotFieldOrientation.xlPageField
        pivotField.CurrentPage = "Active"

        pivotField = pivotTable.PivotFields("Team")
        pivotField.Orientation = Excel.XlPivotFieldOrientation.xlPageField
        Dim project As String = InputBox("Enter the team", "Team name", "Project - Office Client")
        pivotField.CurrentPage = project

        s.Name = project
    End Sub

    Private Sub CleanUpDynamicButton()
        Try
            Dim l As Excel.ListObject = FindList()
            If (l Is Nothing) Then
                Exit Sub
            End If
            Dim vstoButton As Microsoft.Office.Tools.Excel.Controls.Button
            vstoButton = buttonDictionary(l.Parent)
            RemoveHandler vstoButton.Click, AddressOf pivotButton_Click
            buttonDictionary.Remove(l.Parent)
        Catch ex As Exception

        End Try
    End Sub

Now, when you run this add-in and right click on the worksheet, the bugs dataset will be added to the workbook along with the “Pivot” button.

Next up, we’ll enhance our solution with a custom ribbon.