vba 如何使 Excel 从 TFS 2010 工作项查询中自动刷新

How to make Excel automatically refresh from TFS 2010 workitem query

提问by kroonwijk

We are using the default provided MSF Agile 5.0 process template from Microsoft for running our projects. Specifically, the Iteration Backlog Excel sheet is very useful for doing project management.

我们使用 Microsoft 提供的默认 MSF Agile 5.0 流程模板来运行我们的项目。具体来说，Iteration Backlog Excel 表对于进行项目管理非常有用。

We have however run into situations that the Iteration Backlog on sheet number 1 was not up-to-date. After opening the Excel workbook, the user has to explicitlyclick the Refresh button on the Team tab to get the most recent data into view.

然而，我们遇到了第 1 页上的迭代积压工作不是最新的情况。打开 Excel 工作簿后，用户必须明确单击“团队”选项卡上的“刷新”按钮才能查看最新数据。

Question: how can we force Excel (2007) to refresh the Iteration Backlog on opening the Workbook and synchronize with the TFS 2010 workitem query it is connected to?

问题：我们如何强制 Excel (2007) 在打开工作簿时刷新迭代积压并与其连接的 TFS 2010 工作项查询同步？

The suggestion provided by others to record a Macro for clicking the Refresh button does not work, because the recorded macro is not capable of refreshing a query with a tree hierachy (at least, an error occurs executing the Macro telling me so). The recorded macro does something else that just clicking the button :-)

其他人提供的记录宏以单击“刷新”按钮的建议不起作用，因为记录的宏无法使用树层次结构刷新查询（至少，执行宏时会发生错误告诉我）。录制的宏执行其他操作，只需单击按钮 :-)

回答by Anonymous Type

Some primers from MSDN library on list types
Types of lists
Converting a Input list to Query list

从MSDN库在列表类型的一些引物
列表的类型
转换一个输入列表，查询列表

Now on to the issue at hand.
As the previous answerer said you need code that runs from the workbook open event. I believe that part you already knew.
The refreshall method is generic and only works for data connections, formulas and regular sharepoint lists.
You need to use the Team menu from the ribbon.
The following code snippet shows how, plus the method by which to grab the list object that represents the table holding the workitem data.
Synchronize TFS and Excel via VBA

现在谈谈手头的问题。
正如前面的回答者所说，您需要从工作簿打开事件运行的代码。我相信那部分你已经知道了。
refreshall 方法是通用的，仅适用于数据连接、公式和常规共享点列表。
您需要使用功能区中的团队菜单。
下面的代码片段展示了如何以及获取代表包含工作项数据的表的列表对象的方法。
通过 VBA 同步 TFS 和 Excel

In case the link breaks partial reproduction of code follows (just activation of Team menu). Already the MSDN link in their article looks broken (or maybe not..)

如果链接中断了代码的部分复制（只需激活团队菜单）。他们文章中的 MSDN 链接看起来已经坏了（或者可能没有……）

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

回答by Warren Parks

I tried to just edit Anonymous Type's answer but my edit was rejected so making new answer. He was missing part of the code from the RefreshTeamQuery method as shown in linked article(hereis a more direct link to original code).

我试图只编辑匿名类型的答案，但我的编辑被拒绝了，所以做出了新的答案。如链接文章中所示，他缺少 RefreshTeamQuery 方法中的部分代码（这里是原始代码的更直接链接）。

I'm also still having issues calling this from the workbook open event because I don't think those buttons are created in toolbar or somehow linked to worksheet when the wookbook is opened. Using code on a button works fine though.

我仍然遇到从工作簿打开事件调用它的问题，因为我认为这些按钮不是在工具栏中创建的，也不是在打开 wookbook 时以某种方式链接到工作表的。不过，在按钮上使用代码效果很好。

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

回答by zumalifeguard

This version is similar, but it has the option where you don't have to pass in a range, but simply assume the TFS table has been clicked on (selected) by the user.

这个版本是类似的，但它有一个选项，你不必传入一个范围，而只是假设用户已经点击（选择）了 TFS 表。

The original functionality is also there:

原始功能也在那里：

Sub RefreshTeamQuery()
    Dim sel As Range: Set sel = Application.Selection: If sel Is Nothing Then Exit Sub
    Dim lo As ListObject: Set lo = sel.ListObject: If lo Is Nothing Then Exit Sub
    RefreshTeamQueryWithList lo
End Sub

Sub RefreshTeamQueryWithList(lo As ListObject)

    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

    On Error GoTo errorHandler

    '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 = lo.Range

    teamQueryRange.Worksheet.Select
    teamQueryRange.Select
    refreshControl.Execute

    activeSheet.Select
    Application.ScreenUpdating = True

errorHandler:
    If Not activeSheet Is Nothing Then 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

回答by LevaR

As I know, there is an VB function which refreshes all xls-file data sources: ActiveWorkbook.RefreshAll

据我所知，有一个 VB 函数可以刷新所有 xls 文件数据源：ActiveWorkbook.RefreshAll

You only need to hook it up to open workbook event.

您只需要连接它即可打开工作簿事件。