如何解决 VBA“[TFS Excel 加载项 CommandBarButton] 的方法执行失败"运行时错误 [英] How do I resolve a VBA 'Method Execute of a [TFS Excel Add-in CommandBarButton] failed' Run-time error

问题描述

我正在尝试编写一个宏来获取/刷新 Excel 2016 中的 Azure DevOps 查询(通过 TFS Excel 加载项/团队功能区)，以便我可以使用提取的数据来运行一些计算并生成进度报告.我在 2010 年在线找到了用于刷新团队查询的代码(即 (https://blogs.msdn.microsoft.com/team_foundation/2010/11/06/programming-for-the-tfs-excel-add-in/) 但是当我尝试运行宏时，我收到refreshControl.Execute"行的运行时错误，指示:运行时错误'-2147467259(80004005)'方法'执行' 对象 '_CommandBarButton' 失败".

I am trying to write a macro to get / refresh an Azure DevOps query in Excel 2016 (via the TFS Excel Add-In / Team Ribbon) so that I can use the data pulled to run some calculations and generate a progress report. I found the code online from 2010 to refresh the team query (i.e. 'Programming for the TFS Excel Add-in' at (https://blogs.msdn.microsoft.com/team_foundation/2010/11/06/programming-for-the-tfs-excel-add-in/) but when I try running the macro, I get a runtime error for the "refreshControl.Execute" line indicating: "Run-time error '-2147467259 (80004005)' Method 'execute' of object '_CommandBarButton' failed".

尽管出于某种原因，当我在该行之前放入 'Msgbox "" 行 (refreshControl.Execute) 时，它会成功运行并刷新，但我正在自动运行该过程，而无需人工干预(按计划进行)任务)所以我不能把那个 msgbox 放在那里，因为它总是会被生成.如果有帮助，我正在使用 Windows 10 和 Excel 2016 Professional Plus，代码如下.

For some reason though, when I put in a 'Msgbox "" ' line before that (refreshControl.Execute) line, it runs successfully and refreshes but i'm automating the process to run without requiring human intervention (as a scheduled task) so I can't keep that msgbox in there because it will always be generated. I am using Windows 10 and Excel 2016 Professional Plus if helpful and the code is below.

附言我尝试添加等待时间而不是 Msgbox(以防它是时间问题)，但仍然出现错误.(注意:手动按团队功能区中的刷新按钮工作正常).非常感谢解决方案或任何帮助.

P.S. I tried adding a wait time instead of the Msgbox (in case it was a timing issue) but still got the error. (Note: manually pressing the Refresh button in the Team Ribbon works fine). A resolution or any assistance would be much appreciated.

Sub Macro1()

Range("A1").Select
RefreshTeamQuery ("Sheet1")

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

Sub RefreshTeamQuery(shtTFSExcel_Name As String)

Dim actvSheet 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 actvSheet = ActiveWorkbook.activeSheet
Set teamQueryRange = Worksheets(shtTFSExcel_Name).ListObjects(1).Range

teamQueryRange.Worksheet.Select
teamQueryRange.Select

'Msgbox ""
refreshControl.Execute

actvSheet.Select
Application.ScreenUpdating = True

End Sub

推荐答案

我似乎通过在我的 Sub Macro1 的第一行中选择一个不同的单元格来解决它(即更改了 'Range("A1").选择'到'范围("A2").选择').我一直在网上搜索解决方案并遇到了这个页面(https://support.microsoft.com/en-ca/help/983119/run-time-error-2147467259-80004005-when-you-programmatically-set-a-pro) 这表明运行时错误‘-2147467259 (80004005)’"通常与受保护的工作表相关联，并作为一种解决方法取消保护工作表.

I seem to have resolved it by selecting a different cell in the first line of my Sub Macro1 (i.e. changed 'Range("A1").Select' to 'Range("A2").Select'). I had been searching online for a resolution and came across this page (https://support.microsoft.com/en-ca/help/983119/run-time-error-2147467259-80004005-when-you-programmatically-set-a-pro) which suggested that the "Run-time error '-2147467259 (80004005)' " was usually associated with protected worksheets and to unprotect the worksheet as a workaround.

即使我的工作表本身没有保护，TFS Excel 加载项查询结果使用生成的表区域的第一行(在我的情况下，第 1 行)仅提供信息，因此以某种方式保护"该行，因此无法选择或编辑它.因此，我更改了代码以选择第 2 行中的一个单元格，并且代码运行没有错误并刷新了查询结果.

Even though my worksheet itself is not protected, the TFS Excel Add-In query results uses the first row of the generated table area (in my case, row 1) to provide information only and so somehow is 'protecting' that row so it cant be selected or edited. I, therefore, changed my code to select a cell in the 2nd row and the code run without error and refreshed the query results.

我想分享这个，以防有人遇到相同或类似的问题，这会有所帮助.

I wanted to share this in case anyone had the same or similar issue where this would be helpful.

这篇关于如何解决 VBA“[TFS Excel 加载项 CommandBarButton] 的方法执行失败"运行时错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！