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