Partager via

Programming for the TFS Excel Add-in

  • Article

 

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

Control

Tag

Team menu

IDC_WORK_ITEMS_MENU

New List

IDC_NEW_WI_LIST

Get Work Items

IDC_IMPORT

Publish

IDC_SYNC

Refresh

IDC_REFRESH

Configure List

IDC_CONFIGURE_LIST

Choose Columns

IDC_COLUMN_CHOOSER

Links and Attachments

IDC_LINKS_ATTACHMENTS

Edit Areas and Iterations (menu)

IDC_CSSEDIT

Server Connection

IDC_CONFIGURE_MENU                 

Open in Web Access

IDC_OPEN_IN_WEB_ACCESS             

Process Guidance

IDC_PROCESS_GUIDANCE  

Project Portal

IDC_PROJECT_PORTAL                 

Team Foundation Help

IDC_VSTS_HELP                      

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