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

List of control tags for TFS Project Add-In

We hope you find this information useful!

Thanks!

Yogita