Run a button in an Excel-Add-In (Jedox) using VBA

Question

I have the following simple Excel spreadsheet:

      A             B       
1    Revenue       5.000    =PALO.DATAC("Jedox Server";"P&L";"Revenue")
2    Costs        -3.000    =PALO.DATAC("Jedox Server";"P&L";"COSTS")
3    Profit        2.000    =SUM(B1:B2)
4
5

As you can see in cell B1 and B2 I am ussing the BI-Tool https://www.jedox.com/en/ in order to insert the Revenue and the Costs from an OLAP-Cube.

All this works perfectly so far.

---

With the Excel-Add-In of the BI-Tool I can also create a so-called OLAP Screenshot:

When I cklick on this button a new file is created in which the formulas in Cell B1 and B2 are deleted and only the values are displayed (in this case: 5.000 and -3.000).

Now, I want that I can trigger this button with a VBA code. Something like this:

Sub OLAP_Screenshot()
Go to Excel-Addin "Jedox"
Click on "Speichern als Snapshot"
End Sub

Do you have any idea how the VBA code must look like to activate this button in the BI-Tool?

Answer 1

A simple solution would be to map the shortcut:

SendKeys ("%Y1D"), True

Where %= alt, activates keyboard shortcuts Y1= ribbon shortcut (change to what the Jedox plugin is mapped as) D = button shortcut (change to what the Speichern als Snapshot button is mapped as)

WARNING: You should only use the SendKeys Method if no other option is available, because it can cause problems, if the wrong window is active when the code runs.

Alternatively look in the VBA object library and add: tools>references>Jedox

From here it would look something like: (I do not have the plugin installed)

Sub SAS

Dim Jedox As JedoxExcelAddIn
Set Jedox= Application.COMAddIns.Item("Jedox.ExcelAddIn").Object
Jedox.'Speichern als Snapshot'

End Sub