1

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

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.
enter image description here

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
enter image description here

Add-ins
enter image description here

or object browser
enter image description here

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.

Community
  • 1
  • 1
SeekingSigma
  • 78
  • 5
  • I found an old thread about this that remains unanswered for years. They talk about using `SendKeys` to press the buttons to cause the refresh. I don't recommend doing that. Bad things will happen. https://community.dynamics.com/ax/f/microsoft-dynamics-ax-forum/87586/executing-dynamics-ax-add-in-commands-from-macro-vba-code – HackSlash Jan 13 '22 at 21:37
  • Thanks for the link. I have also seen sendkeys and UIAutomation for keyboard shortcut and even clicking emulation, but definitely don't to implement such a roundabout solution. I feel I am missing something simple but can't seem to find anything on Google. – SeekingSigma Jan 13 '22 at 23:05
  • Just because something should exist doesn't mean it does. I can't find the API documentation for this Add-in either. Your best bet would be to get a handle on the object with something like `Set NAVObject = CreateObject("Microsoft.Dynamics.NAV.ExcelAddin")` and then explore the `NAVObject` in your locals window during a debug session. – HackSlash Jan 14 '22 at 00:04
  • Note: It may not export any useful public methods. – HackSlash Jan 14 '22 at 00:05

1 Answers1

0

Try to refresh the ListObject itself.

With ThisWorkbook.Worksheets("Sales Orders")
    .ListObjects("NavDataRegion").Refresh
End With
HackSlash
  • 4,944
  • 2
  • 18
  • 44
  • WARNING: This is untested air code. I don't have Microsoft Dynamics NAV Excel Add-in and I cannot test. – HackSlash Jan 13 '22 at 21:43
  • Thanks for the suggestion. I get an error: "Application-defined or object-defined error" when trying to run the code, so I guess the add-in has a different process to refresh the data. – SeekingSigma Jan 13 '22 at 23:01