Solutions @ hans-eric.com

Problem

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.

Solution

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

    teamQueryRange.Worksheet.Select
    teamQueryRange.Select
    refreshControl.Execute

    activeSheet.Select

    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
    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

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:

• Connect TFS to Excel
• Programming for the TFS Excel Add-in