Home » Synchronize TFS and Excel via VBA

  • Synchronize TFS and Excel via VBA

    Synchronize TFS and Excel via VBA

    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

     

    Share this