Instead of running reports in TFS and exporting them to Excel, it is possible to run the TFS queries from Excel and get the results into Excel directly. This can even be automated using VBA.

Here I’ll explain the steps to take how to set up your environment so you can synchronize your TFS with Excel, and keep it synchronized, using VBA.

Steps to take:

Preparation

  1. Of course, make sure you have rights to access TFS
  2. Install Team Explorer on your PC. This will install the “Team” Add In in your local Office version. You’ll need this TFS Add-in to connect to TFS. (more info: http://msdn.microsoft.com/en-us/library/ms181661%28VS.90%29.aspx )
  3. Create a query in TFS that will return all the data you want. There are more locations to create the query, but I would strongly suggest to keep them all stored in the same place, TFS.

Get the results of your TFS query in Excel

  1. Run Excel
  2. Open up a clean and brand new worksheet (or tab, whatever you want to call it)
  3. Go to the “Team” tab in your ribbon (it should be there, otherwise try installing the “Team Explorer”) and select “New list
  4. Connect to your TFS server, and select your TFS query
  5. Your list should now appear within Excel

Refresh / synchronize TFS (work items) into Excel using VBA

  1. Create the two VBA functions in Excel (taken from and more info about TFS / Excel: http://blogs.msdn.com/b/team_foundation/archive/2010/11/06/programming-for-the-tfs-excel-add-in.aspx ). shtTFSExcel_Name is the name of the worksheet in Excel that contains your TFS query results.
  2. You’re done. Run the RefreshTeamQuery to update your TFS list.

 

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

Sub RefreshTeamQuery(shtTFSExcel_Name As String) ‘(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 Sub
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 = Worksheets(shtTFSExcel_Name).ListObjects(1).Range

teamQueryRange.Worksheet.Select
teamQueryRange.Select
refreshControl.Execute

activeSheet.Select

Application.ScreenUpdating = True
End Sub

 

Comments are closed.