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 (Choose 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, choose 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

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.

  

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.

  

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.

  

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!

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!

  

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

  

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.

  

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.

 

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 (Choose 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, choose 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

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.

  

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.

  

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.

  

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!

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!

  

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

  

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.

  

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.

  

Data Import From Internal Loop

Function DataImportFromInternalLoop(screenupdate As Boolean) As Boolean

Imports data via internal loop.

  

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.

  

Get Cell Value

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

Fetches value of the cell.

Rebuild View

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

Rebuilds the Jedox View.

 

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.

  

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.

image_pdfimage_print
Was this post helpful?
NoYes (+3 rating, 5 votes)
Loading...
Tagged: