Call COM Add-In Function in VBA

Question

I want to call the "Refresh" function of the Microsoft Dynamics NAV Excel Add-in which has a button on the ribbon using VBA.

I tried recording a macro when clicking the button but the code does not work and throws a runtime error due to the Selection.AutoFilter lines.

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveWorkbook.Names("ConnectionInfo").Delete
    ActiveWorkbook.Names.Add Name:="ConnectionInfo", RefersToR1C1:= _
        "='Sales Orders'!R1C1:R1C16"
    ActiveWorkbook.Names("ObjectInfo").Delete
    ActiveWorkbook.Names.Add Name:="ObjectInfo", RefersToR1C1:= _
        "='Sales Orders'!R2C1:R2C16"
    ActiveWorkbook.Names("ConnectionInfo").Delete
    ActiveWorkbook.Names.Add Name:="ConnectionInfo", RefersToR1C1:= _
        "='Sales Orders'!R1C1:R1C16"
    ActiveWorkbook.Names("ObjectInfo").Delete
    ActiveWorkbook.Names.Add Name:="ObjectInfo", RefersToR1C1:= _
        "='Sales Orders'!R2C1:R2C16"
    With ActiveWorkbook.Worksheets("Sales Orders").ListObjects("NavDataRegion"). _
        Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Selection.AutoFilter
Selection.AutoFilter
End Sub

The macro names the ranges in the data containing the connection address and NAV list name, but doesn't do anything with them.

It is a VSTO COM add-in and as far as I know does not show up in:

VBA References

Add-ins

or object browser

I ran this code:

Sub comDATA()
    Dim oCAI As COMAddIn
    For Each oCAI In Application.COMAddIns
        Debug.Print oCAI.Description & vbTab & oCAI.progID
    Next oCAI
End Sub

and found the ProgID is Microsoft.Dynamics.NAV.ExcelAddin, but I don't know the Refresh button's function name or how to execute it.

Answer 1

Try to refresh the ListObject itself.

With ThisWorkbook.Worksheets("Sales Orders")
    .ListObjects("NavDataRegion").Refresh
End With