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 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
The code in this solution is based on the code in the following blog posts: