Public Const TFSRawDataTable As String = "VSTS_27ab052f_ba68_4653_a713_d78539354a92" 'Change this to your table name
Public Sub RefreshAllTFS()
'Turn off updating the UI so we don't get a lot of flicker
'Application.ScreenUpdating = False
'Make sure we turn screen updating back on if we get any errors
On Error GoTo ErrorHandler
'Save the current worksheet and selection range
Dim curWs As Worksheet
Set curWs = ThisWorkbook.ActiveSheet
Dim curSel As Range
Set curSel = Application.Selection
'Iterate over all worksheets in the current workbook
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.ProtectContents Then
MsgBox "Skipping protected worksheet " + ws.Name
End If
'Activate the worksheet or the selection change (below)
'will not work
ws.Activate
'Iterate over all listobjects on the worksheet
Dim list As ListObject
For Each list In ws.ListObjects
Call PerformRefresh(list)
Next
Next
'Restore the originally selected worksheet and selected range
curWs.Activate
curSel.Select
Application.ScreenUpdating = True
'UpdateAllPivots
ReFilterActiveItems
GoTo Success
ErrorHandler:
Application.ScreenUpdating = True
MsgBox "An error occurred while refreshing the Team Foundation lists"
Success:
Application.StatusBar = "Finished refreshing Team Foundation Lists"
End Sub
Private Function PerformRefresh(list As ListObject)
Dim i As Integer
'See if this is a Team Foundation list. Note: 'ELead' string may change to 'VSTS' in the final release!
If Left(list.Name, 4) = "VSTS" Then
'Find the 'refresh' button.
Dim refreshButton As CommandBarControl
Set refreshButton = Application.CommandBars.FindControl(Tag:="IDC_REFRESH")
'Save the current worksheet's selection
Dim wsSel As Range
Set wsSel = Application.Selection
list.Range.Select
'Seems that sometimes it takes a little while for the button
'to become enabled. I've never seen it take more than 2 seconds,
'though on slower machines it may take longer...
For i = 1 To 10
If Not refreshButton.Enabled Then
'Wait for the UI to settle down to ensure that the toolbar button has
'had time to be enabled
Application.Wait (Now + TimeValue("0:00:01"))
DoEvents
End If
Next
If refreshButton.Enabled Then
refreshButton.Execute
Else
MsgBox "Refresh button not enabled!"
End If
'Restore the selection on the worksheet
wsSel.Select
End If
End Function