How to invoke TFS Add-in controls from Macro code

Programmatically invoking TFS Excel and Project add-in functionality such as Publish and Refresh might be desirable in some cases. For ex. consider the scenario where you want to refresh the work items automatically when the sheet is first opened so you are working on the latest items. Or, you have custom macros to calculate the schedule and this refreshes the list first to bring in the latest updates to the work items.

 

We would love to hear from you about scenarios where you use custom macros or code in TFS bound Excel and Project lists.

 

Here is a sample macro that Swamy, one of our Developers, wrote that shows how to invoke the TFS “Refresh” action.

 

Private Sub RefreshClick()

    Dim popup As CommandBarPopup

    Dim control As CommandBarControl

    Dim bMenuItemFound As Boolean

    bMenuItemFound = False

   

    ' Find Team menu (under Add-Ins tab in Excel12)

    Set popup = Application.CommandBars.FindControl(Type:=msoControlPopup, Tag:="IDC_WORK_ITEMS_MENU", Visible:=True)

   

    If Not (popup Is Nothing) Then

        For i = 1 To popup.Controls.Count

           Set control = popup.Controls.Item(i)

           If ((control.Tag = "IDC_REFRESH") And (control.Enabled = True)) Then

               

                bMenuItemFound = True

                ' Simulate Click event

                control.Execute

                

                ' Exit for loop as we have found "Refresh" menu item

                Exit For

           End If

        Next i

    Else

        MsgBox ("Cannot find Team Menu")

    End If

 

   

    If bMenuItemFound = False Then

        MsgBox ("Cannot find Refresh menuitem or Refresh menuitem is not enabled")

    End If

           

End Sub

 

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

 

List of control tags for TFS Project Add-In

Control	Tag
Team menu	IDC_WORK_ITEMS_MENU
Choose Team Project	IDC_NEW_WI_LIST
Get Work Items	IDC_IMPORT
Publish	IDC_SYNC
Refresh	IDC_REFRESH
Links and Attachments	IDC_LINKS_ATTACHMENTS
Edit Areas and Iterations (menu)	IDC_CSSEDIT

 

We hope you find this information useful!

Thanks!

Yogita