CODEWITHSUNDEEP
excelvbatfsadd-in
VbaInterm
VbaInterm

Reputation: 21

How do I resolve a VBA 'Method Execute of a [TFS Excel Add-in CommandBarButton] failed' Run-time error

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

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.

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

Upvotes: 0

Views: 1357

Answers (1)

VbaInterm
VbaInterm

Reputation: 21

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.

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.

Upvotes: 2

Related Questions