TFS in Excel: Refreshing a TFS Query Using VBA Macro in Excel


You have a Team Foundation Server connected worksheet in Excel 2010, and want to automate the clicking of the Refresh button on the Team ribbon.


Add these two procedures to your VBA macro:

Private Sub RefreshTeamQueryOnWorksheet(worksheetName 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(worksheetName).ListObjects(1).Range



    Application.ScreenUpdating = True
End Sub

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

    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
    End If
End Function

You can then create a macro that updates the query on a specific worksheet, for example “Open Bugs” like in the below example:

Public Sub UpdateAndExportBurndownCharts()
    RefreshTeamQueryOnWorksheet ("Open Bugs")
End Sub

More Info

The code in this solution is based on the code in the following blog posts:

RSS feed | Trackback URI

Comments »

No comments yet.

Name (required)
E-mail (required - never shown publicly)
Your Comment (smaller size | larger size)
You may use <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> in your comment.