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

Comments

• Anonymous
  March 16, 2007
  The Teams WIT Tools Blog on How to invoke TFS Add-in controls from Macro code and Tell us what you think...

• Anonymous
  March 21, 2007
  If you are using Microsoft excel,project for some times, you might find using macros really ease your

• Anonymous
  October 11, 2010
  We just upgraded to TFS 2010 and this code no longer works as it did.  In an attempt just to get stuff working again I did the following...    Dim bMenuItemFound As Boolean    Dim wb As Workbook    Dim ss As Worksheet    Dim n As Integer    Dim refreshCounter As Integer    Dim rng As Range    Dim bar As CommandBar    Dim control As CommandBarControl    bMenuItemFound = False    Set wb = Application.ActiveWorkbook    For n = 2 To wb.Worksheets.Count        Set ss = wb.Worksheets.Item(n)        ss.Activate        ' select a cell in the spreadsheet to enable the refresh button        Set rng = ss.Cells(3, 1)        If Not rng Is Nothing Then            rng.Select            rng.Activate        End If        For Each bar In Application.CommandBars            'Debug.Print bar.Name            If bar.Name = "Team" Then                For Each control In bar.Controls                    If control.Tag = "IDC_REFRESH-TBB" Then                        On Error Resume Next                        control.Execute                        On Error GoTo 0                        bMenuItemFound = True                        refreshCounter = refreshCounter + 1                        Exit For                    End If                Next            End If            If bMenuItemFound Then                bMenuItemFound = False                Exit For            End If        Next    Next n    Set ss = wb.Worksheets.Item(1)    ss.Activate    MsgBox ("refreshed " & CStr(refreshCounter) & " spreadsheets of " & CStr(wb.Worksheets.Count)) Set ss = Nothing Set wb = Nothing

• Anonymous
  July 14, 2014
  The comment has been removed

• Anonymous
  October 31, 2014
  I have an excel file with numerous identical sheets which i am using to retrieve a status report from my employees at work. In those sheets they have specific cells to update and those cells are being updated using a generic function i am calling using Sub Worksheet_Change in each sheet. recently i have added for each sheet a TFS table via the Team tab etc.. The problem is when I am pressing the refresh button at the Team tab, the generic function of Worksheet_Change is being called, and getting inside a very long loop that will end eventually. Is there a way to catch the event when the Refresh Button is being pressed, and check it and then exit the sub of worksheet_change, if it is not pressed i would like to function to run. Thank you very much for your assistance.

• Anonymous
  November 02, 2015
  Is there a way, where i can invoke TFS from the VBA code to get the test cases status (Results)?

• Anonymous
  January 04, 2016
  I want to extract reports from TFS through Macros. So how do i do this?