Excel Add-in COM Interface

Some of the functionality of the Jedox Excel Add-in is exposed via COM interface and as such can be used from VBA in Excel. You can use it to clear cube data; fetch cell or alias values; or display an element picker dialog (Select Elements).

Before you can use these methods in VBA, you have to make VBA aware of the existence of Jedox. Provided Jedox is already installed on your system, and Jedox Excel Add-in is enabled in Excel, follow these steps to enable and test the Jedox COM functionality:

1. Open the VBA Editor either by pressing <ALT>+<F11> on the keyboard or by right-clicking on Sheet tab and choosing View Code.
2. Once in VBA Editor, select Tools > References... In the References dialog look for Jedox Excel Add-in and add a tick in the checkbox to add reference.
3. Open View > Object Browserand select Jedox_Palo_XlAddin in the Object Browser Combobox. Click on IPaloEngineCom class to display all the members available in this class.
   
4. This is the list of supported methods available in IPaloEngineCom class:
   • AliasLookup
   • ChooseElements
   • ChooseElementsEx
   • ClearPartialCube
   • ClearWholeCube
   • GetCellValue
   • RebuildView
   • SaveAsSnapshot

   In addition to these, when exploring IPaloEngineCom, you can also find the following members, which are used internally. They are subject to change and should not be used:

   • ForceServerListUpdate
   • ConnectionClosed
   • GetConnectionDataInfo
   • HandleKeyStroke
   • SUCCESSChartUpdate
   • TrustfileFullName
5. You use these methods to create a simple macro that uses ChooseElements method and displays the result when you click a button:
   
   
   
   
   
   

Executing Jedox Spreadsheet Functions

In addition to the methods available in COM interface, it is also possible to run every Jedox Spreadsheet function via Application.Run.

Note: not every function returns the same result as when running it in the spreadsheet. This is due to the fact that some functions within spreadsheets are context-aware and when run outside of spreadsheets (e.g. in VBA), this context is missing.

1
2
3
4
5
6
7
Sub VBAApplicationRun()
    Dim obj As Jedox_Palo_XlAddin.IPaloEngineCom
    Set obj = New Jedox_Palo_XlAddin.ComInterface

    retVal = Application.Run("PALO.DATA", "localhost/Demo", "Sales", "All Products", "Europe", "Year", "All Years", "Variance", "Units")
    MsgBox (retVal)
End Sub

Appendix - Function Usage

Alias Lookup

Function AliasLookup(serverDatabase As String, dimension As String, attribute As String, elementAlias As String, index As Long) As String

Returns element name for provided alias value.

1
2
3
4
5
6
7
Sub VBAAliasLookup()
    Dim obj As Jedox_Palo_XlAddin.IPaloEngineCom
    Set obj = New Jedox_Palo_XlAddin.ComInterface

    retVal = obj.AliasLookup("localhost/Biker", "Customers", "deutsch", "Österreich", 1)
    MsgBox (retVal)
End Sub

Choose Elements

Function ChooseElements(serverDatabase As String, dimensionName As String) As String

Displays Choose Elements dialog and returns a string representing the name of the selected element in the dialog.

1
2
3
4
5
6
7
Sub VBAChooseElements()
    Dim obj As Jedox_Palo_XlAddin.IPaloEngineCom
    Set obj = New Jedox_Palo_XlAddin.ComInterface

    retVal = obj.ChooseElements("localhost/Biker", "Customers")
    MsgBox (retVal)
End Sub

Choose Elements Ex

Function ChooseElementsEx(serverDatabase As String, dimensionName As String, multi As Boolean, attribute As String, [returnName As Boolean = False]) As String()

Variation of the ChooseElements function; supports multiple element selections and attribute usage.

1
2
3
4
5
6
7
Sub VBAChooseElementsEx()
    Dim obj As Jedox_Palo_XlAddin.IPaloEngineCom
    Set obj = New Jedox_Palo_XlAddin.ComInterface

    retVal = obj.ChooseElementsEx("localhost/Biker", "Customers", True, "deutsch", True)
    MsgBox (retVal(0))
End Sub

Clear Partial Cube

Function ClearPartialCube(serverDatabase As String, cubeName As String, elementsArray) As Boolean

Clears slice within the cube.

Note: data within the slice will be lost!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub VBAClearPartialCube()
    Dim server_database As String
    Dim cube As String
    Dim Years(1 To 2) As Variant
    Dim subcube(1 To 6) As Variant
    Dim obj As Jedox_Palo_XlAddin.IPaloEngineCom
    Set obj = New Jedox_Palo_XlAddin.ComInterface

    server_database = "localhost/Demo"
    cube = "Sales"
    Years(1) = "2010"
    Years(2) = "2011"
    subcube(3) = Application.Run("PALO.DIMENSION_LIST_ELEMENTS", server_database, "Months")
    subcube(4) = Years
    retVal = obj.ClearPartialCube(server_database, cube, subcube)
    MsgBox (retVal)
End Sub

Note: the number behind the subcube has to match the order number of the dimension in the cube!

Clear Whole Cube

Function ClearWholeCube(serverDatabase As String, cubeName As String) As Boolean

Clears entire cube.

Note: data within the slice will be lost!

1
2
3
4
5
6
7
Sub VBAClearWholeCube()
    Dim obj As Jedox_Palo_XlAddin.IPaloEngineCom
    Set obj = New Jedox_Palo_XlAddin.ComInterface

    retVal = obj.ClearWholeCube("localhost/Demo", "Sales")
    MsgBox (retVal)
End Sub

Get Cell Value

Function GetCellValue(serverDatabase As String, cubeName As String, elementsArray)

Fetches the value of the cell.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Sub VBAGetCellValue()
    Dim server_database As String
    Dim cube As String
    Dim coordinates(1 To 6) As Variant
    Dim obj As Jedox_Palo_XlAddin.IPaloEngineCom
    Set obj = New Jedox_Palo_XlAddin.ComInterface

    server_database = "localhost/Demo"
    cube = "Sales"
    coordinates(1) = "All Products"
    coordinates(2) = "Europe"
    coordinates(3) = "Year"
    coordinates(4) = "All Years"
    coordinates(5) = "Variance"
    coordinates(6) = "Units"
    retVal= obj.GetCellValue(server_database, cube, coordinates)
    MsgBox (retVal)
End Sub

Rebuild View

Function RebuildView(). It has no parameters at all. Implemented as of Jedox Version 7.0 SR2.

Rebuilds the Jedox View.

1
2
3
4
5
6
Sub Restore()
     Dim obj As Jedox_Palo_XlAddin.IPaloEngineCom
     Set obj = New Jedox_Palo_XlAddin.ComInterface

     rebuildOk = obj.RebuildView()
 End Sub

Save As Snapshot

Function SaveAsSnapshot(OnlyPaloFormulas As Boolean, ToOneDrive As Boolean, Replace As Boolean)

Creates a snapshot of current Excel workbook. Implemented as of Jedox Version 7.0 SR2.

Description of arguments:

• OnlyPaloFormulas can be True or False. If it is True, COM Interface creates a copy snapshot of the current Excel workbook without PALO-Formulas. All standard excels formulas stay untouched. If it is set to False, COM Interface creates a copy snapshot of the current Excel workbook without any formulas.
• ToOneDrive (optional, default False) can be True or False. If it is True, COM Interface creates a copy snapshot of the current Excel workbook without PALO-Formulas too OneDrive directory, if such directory can be found.
• Replace (optional, default False) can be True or False. If it is True, all previous parameters would be ignored and COM Interface creates in-place (overwrites) snapshot of current Excel workbook without PALO-Formulas.

Sub Snapshot()
     Dim obj As Jedox_Palo_XlAddin.IPaloEngineCom
     Set obj = New Jedox_Palo_XlAddin.ComInterface

     snapshotOk = obj.SaveAsSnapshot(True, True, True)
 End Sub

Sub Refresh()
     Dim obj As Jedox_Palo_XlAddin.IPaloEngineCom
     Set obj = New Jedox_Palo_XlAddin.ComInterface

     rebuildOk = obj.RebuildView()
     snapshotOk = obj.SaveAsSnapshot (True, True, True)
 End Sub

Updated December 9, 2024