Jedox Excel Add-in COM Interface

image_pdfimage_print

Some of the functionality of Jedox Excel Add-in is exposed via COM interface and as such can be used from VBA in Excel. You can use it to export, import, and 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, please follow these steps to enable and test Jedox COM functionality:

  1. Open VBA Editor either by pressing <ALT>+<F11> on the keyboard or by right-clicking on Sheet tab and choosing “View Code”.COM API step1
  2. Once in VBA Editor, select Tools → References… In the References dialog look for “Jedox Excel Add-in” and add tick in the checkbox to add reference:COM API step2COM API step3
  3. Now open View > Object Browser and select Jedox_Palo_XlAddin in the Object filter combobox. Click on IPaloEngineCom class to display all the members available in this class.COM API step4COM API step5This is the list of supported methods available in IPaloEngineCom class:

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

    • ForceServerListUpdate
    • ConnectionClosed
    • GetConnectionDataInfo
    • GetServerSubSetContent
    • HandleKeyStroke
    • SUCCESSChartUpdate
    • TrustfileFullName
  4. You use these methods to create a simple macro that uses ChooseElements method and displays the result when the button is clicked:
    COM API step6COM API step7COM API step9COM API step8COM API step10COM API step11

  

Executing Jedox Spreadsheet Functions

In addition to methods available in COM interface, it is also possible to run every Jedox Spreadsheet function via Application.Run. Please note that not every function will return the same result as when run in 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.

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.

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 string representing the name of selected element in dialog.

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.

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 cube. Note: data within slice will be lost!

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 slice will be lost!

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

  

Data Export From Cube

Function DataExportFromCube(serverDatabase As String, cubeName As String, elementsArray, compareValue1 As String, compareOperator1 As Long, boolOperator As Long, compareValue2 As String, compareOperator2 As Long, ignoreEmptyCells As Boolean, baseElementsOnly As Boolean, fileName As String, valueSeparator As String, append As Boolean, [useRules As Boolean = False], [withHeader As Boolean = False], [decpoint As String = “.”], [use_utf8 As Boolean = False]) As Boolean

Exports slice from a cube. Available values for compareOperator1 and 2 are:

<=0 means always true, regardless of compareValue1
1 correspond to =
2 correspond to >=
3 correspond to >
4 correspond to <=
5 correspond to <
6 correspond to !=
>6 reserved for future use

Available values for boolOperator are:

<=0 means AND with compareoperator2 set to always true
0 correspond to AND
1 correspond to OR
2 correspond to XOR
>2 reserved for future use

Sub VBAExportFromCube()
    Dim server_database As String
    Dim cube As String
    Dim comparevalue1 As String
    Dim compareoperator1 As Long
    Dim comparevalue2 As String
    Dim compareoperator2 As Long
    Dim booloperator As Long
    Dim ignore_empty_cells As Boolean
    Dim baselements_only As Boolean
    Dim exportfile As String
    Dim separator As String
    Dim doappend As Boolean
    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"
    comparevalue1 = "0"
    compareoperator1 = 0
    comparevalue2 = "0"
    compareoperator2 = 0
    booloperator = 0
    ignore_empty_cells = True
    baselements_only = True
    exportfile = "C:\testexport.txt"
    separator = Chr(9) ' the entries are separated by Tabulators
    decimalpoint = "."
    doappend = False
    Years(1) = "2010"
    Years(2) = "2011"
    subcube(3) = Application.Run("PALO.DIMENSION_LIST_ELEMENTS", server_database, "Months")
    subcube(4) = Years
    retVal = obj.DataExportFromCube(server_database, cube, subcube, comparevalue1, compareoperator1, booloperator, comparevalue2, compareoperator1, ignore_empty_cells, baselements_only, exportfile, separator, doappend)
    MsgBox (retVal)
End Sub

  

Data Import From CSV

Function DataImportFromCSV(importCsvFileName As String, hasHeader As Boolean, separator As String, decimalPoint As String, screenupdate As Boolean) As Boolean

Imports data from CSV file.

Sub VBAImportFromCsv()
    Dim importFile As String
    Dim separator As String
    Dim decimalPoint As String
    Dim screenUpdating As Boolean
    Dim obj As Jedox_Palo_XlAddin.IPaloEngineCom
    Set obj = New Jedox_Palo_XlAddin.ComInterface

    importFile = "C:\testimport.txt"
    separator = Chr(9) ' the entries are separated by Tabulators
    decimalPoint = "."
    screenUpdating = True
    retVal = obj.DataImportFromCSV(importfile, False, separator, decimalPoint, screenUpdating)
    MsgBox (retVal)
End Sub

  

Data Import From Cube

Function DataImportFromCube(serverDatabase As String, cubeName As String, elementsArray, compareValue1 As String, compareOperator1 As Long, boolOperator As Long, compareValue2 As String, compareOperator2 As Long, ignoreEmptyCells As Boolean, baseElementsOnly As Boolean, screenupdate As Boolean) As Boolean

Imports data from cube. For information on compareOperator and boolOperator parameters see DataExportFromCube.

Sub VBAImportFromCube()
    Dim server_database As String
    Dim cube As String
    Dim comparevalue1 As String
    Dim compareoperator1 As Long
    Dim comparevalue2 As String
    Dim compareoperator2 As Long
    Dim booloperator As Long
    Dim ignore_empty_cells As Boolean
    Dim baselements_only As Boolean
    Dim screenupdating As Boolean
    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"
    comparevalue1 = "0"
    compareoperator1 = 0
    comparevalue2 = "0"
    compareoperator2 = 0
    booloperator = 0
    ignore_empty_cells = True
    baselements_only = True
    Years(1) = "2010"
    Years(2) = "2011"
    subcube(3) = Application.Run("PALO.DIMENSION_LIST_ELEMENTS", server_database, "Months")
    subcube(4) = Years
    screenupdating = False
    retVal = obj.DataImportFromCube(server_database, cube, subcube, comparevalue1, compareoperator1, booloperator, comparevalue2, compareoperator1, ignore_empty_cells, baselements_only, screenupdating)
    MsgBox (retVal)

End Sub

  

Data Import From Internal Loop

Function DataImportFromInternalLoop(screenupdate As Boolean) As Boolean

Imports data via internal loop.

Sub VBAImportInternalLoop()
    Dim screenUpdating As Boolean
    Dim obj As Jedox_Palo_XlAddin.IPaloEngineCom
    Set obj = New Jedox_Palo_XlAddin.ComInterface

    screenUpdating = False
    retVal = obj.DataImportFromInternalLoop(screenUpdating)
    MsgBox (retVal)
End Sub

  

Data Import From ODBC

Function DataImportFromODBC(dsn As String, UserName As String, password As String, sqlExpression As String, screenupdate As Boolean) As Boolean

Imports data from ODBC source.

Sub VBAImportFromODBC()
    Dim dsn As String
    Dim userId As String
    Dim password As String
    Dim selectStatement As String
    Dim screenUpdating As Boolean
    Dim obj As Jedox_Palo_XlAddin.IPaloEngineCom
    Set obj = New Jedox_Palo_XlAddin.ComInterface

    dsn = "DSN4Palo"
    userId = "Palo"
    password = "Palo"
    selectStatement = "select * from sheet1"
    screenUpdating = False
    retVal = obj.DataImportFromODBC(dsn, userId, password, selectStatement, screenUpdating)
    MsgBox (returnvalue)
End Sub

  

Get Cell Value

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

Fetches value of the cell.

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.

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 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 stays untouched. If it is 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

 

image_pdfimage_print
Tagged: