Programming for the TFS Excel Add-in

Some folks have asked about how to program against the TFS Excel Add-In. The summary is that we don’t have a public API for the TFS Add-in but we do support using the ribbon.

Here are the ribbon commands and an example of how to use them.

List of control tags for TFS Excel Add-In

Here is how you find the Team command bar for and loop through the controls:

Private Function FindTeamControl(tagName As String) As CommandBarControl

    Dim commandBar As commandBar

    Dim teamCommandBar As commandBar

    Dim control As CommandBarControl

    For Each commandBar In Application.CommandBars

        If commandBar.Name = "Team" Then

            Set teamCommandBar = commandBar

            Exit For

        End If

    Next

    If Not teamCommandBar Is Nothing Then

        For Each control In teamCommandBar.Controls

            If InStr(1, control.tag, tagName) Then

                Set FindTeamControl = control

                Exit Function

            End If

        Next

    End If

End Function

Here is an example of refreshing:

Private Function RefreshTeamQuery(rangeName As String)

    Dim activeSheet As Worksheet

    Dim teamQueryRange As range

    Dim refreshControl As CommandBarControl

    Set refreshControl = FindTeamControl("IDC_REFRESH")

    If refreshControl Is Nothing Then

        MsgBox "Could not find Team Foundation commands in Ribbon. Please make sure that the Team Foundation Excel plugin is installed.", vbCritical

        Exit Function

    End If

    ' Disable screen updating temporarily so that the user doesn't see us selecting a range

    Application.ScreenUpdating = False

    ' Capture the currently active sheet, we will need it later

    Set activeSheet = ActiveWorkbook.activeSheet

    Set teamQueryRange = range(rangeName)

    teamQueryRange.Worksheet.Select

    teamQueryRange.Select

    refreshControl.Execute

    activeSheet.Select

    Application.ScreenUpdating = True

End Function

Comments

• Anonymous
  November 06, 2010
  Thanks for this post! Any hints on how to avoid the odd beahvior of the Visual Basic Editor in Excel when Work Items are loaded into a Sheet? Spaces are removed automatically and so forth... Sven