Using Excel VBA, can you publish a query that you have made changes in?

Question

Excel VBA ADO/TFS add in supports refreshing an ADO query in a worksheet using the IDC_REFRESH constant. See (Execute action failed on CommandBarButton to update workitems) for details.

I'd like to be able to publish a query using a vba macro as well but haven't been able to figure that out.

I've searched numerous times, looking for documentation on the ability to invoke the Publish button.

With no success. I also tried modifying my code to find the Publish button/control using IDC_PUBLISH and the same code referenced in the stackoverflow link above.

Does anyone out there know if this is possible at all?

Answer 1

OK, Kevin Lu-MSFT gave me enough insight to sort this out. Thank you Kevin! Here's an updated version of the subroutine that has been floating around for years. This subroutine takes the control name substring of interest as an argument and then executes that control.

I have tested this and it works! I hope it helps someone else.

Call ExecuteTeamControlOnWorksheet("OneFeature", "IDC_REFRESH") ' Execute Refresh
Call ExecuteTeamControlOnWorksheet("OneFeature", "IDC_SYNC") ' Execute Publish

Private Sub ExecuteTeamControlOnWorksheet(worksheetName As String, teamControlName As String)

    Dim activeSheet As Worksheet
    Dim teamQueryRange As Range
    Dim refreshControl As CommandBarControl
    
    Set refreshControl = FindTeamControl(teamControlName)

    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(2).Range

    teamQueryRange.Worksheet.Select
    teamQueryRange.Select
        
    refreshControl.Execute
    

    activeSheet.Select
    

    Application.ScreenUpdating = True
    
    
    'MsgBox "Completed " + worksheetName + " Query "
    
    Debug.Print "Completed " + worksheetName + " Query "
        
End Sub

Private Function FindTeamControl(tagName As String) As CommandBarControl

    Dim commandBar As commandBar
    Dim teamCommandBar As commandBar
    Dim control As CommandBarControl
    
' Caption is the name displayed on the Control on the Excel Team Tab
' Tag is the Tag name for that control. This is the string this
' The original example of this dropped the -TBB off the end, 
' so I've been following this practice
' To find the Refresh button, use IDC_REFRESH, for Publish use IDC_SYNC
'   Caption               : Tag
'   New List              : IDC_NEW_WI_LIST-TBB
'   Get Work Items        : IDC_IMPORT-TBB
'   Publish               : IDC_SYNC-TBB
'   Refresh               : IDC_REFRESH-TBB
'   List                  : IDC_CONFIGURE_LIST-TBB
'   Choose Columns        : IDC_COLUMN_CHOOSER-TBB
'   Links and Attachments : IDC_LINKS_ATTACHMENTS-TBB
'   Open in Web Access    : IDC_OPEN_IN_WEB_ACCESS-TBB
'   Select User           : IDC_IDENTITY_PICKER-TBB
'   Add Tree Level        : IDC_ADD_SUBLEVEL_COLUMN-TBB
'   Add Child             : IDC_ADD_NEW_CHILD-TBB
'   Indent                : IDC_INDENT-TBB
'   Outdent               : IDC_OUTDENT-TBB

    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