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 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, follow these steps to enable and test the Jedox COM functionality:
- Open the VBA Editor either by pressing <ALT>+<F11> on the keyboard or by right-clicking on Sheet tab and choosing View Code.
- 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:
- Open View > Object Browser and select Jedox_Palo_XlAddin in the Object Browser combobox. Click on IPaloEngineCom class to display all the members available in this class.This is the list of supported methods available in IPaloEngineCom class:
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:
- 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.
Appendix – Function Usage
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.
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.
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 the cube.
Note: data within the 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 the 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 the 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 an 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 the value of the cell.
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 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.