canvasplanning:userguide:macros

Macros

CL_SpreadRefresh – execute all spreads in the workbook.

CL_LockSpreadCells – lock all cells currently selected. The font color of selected cells is changed to red. The values of locked cells are included in the spread results, but values of locked cells are unchanged by spreads.

CL_ClearSpreadCellLocks – unlock all cells currently locked. The font color of unlocked cells is changed to the original setting before locking.

CL_UndoSpread – undo spread results. When spreads take place values prior to the spread are cached in memory, so spreads can be undone. Up to the last 20 spreads are cached.

CL_Logon – open the Logon dialog box

CL_OpenAssignments – open the Assignments dialog box

CL_OpenBookPublish – open the Book Publisher dialog box

CL_OpenDataManager – open the Data Manager dialog box

CL_RefreshReport – refresh all defined reports in the current workbook. (Note: If you experience problems with this macro in Excel 2013, use CL_MacroRefreshReports(True, False, True) instead.)

CL_MacroRefreshReports( boolean bSchedulePrompt, boolean bScheduleRefresh, boolean bRecalcFormulas) – refresh reports and input schedules with optional parameters. (Note: To work around a limitation currently in Excel 2013, when running Excel 2013 this macro is executed asynchronously.)

  • bSchedulePrompt – true / false. True = display the confirmation dialog box before refreshing input schedules. False = suppress the confirmation dialog box before refreshing input schedules. Default value is True.
  • bScheduleRefresh – true / false. True = refresh input schedules. False = do not refresh input schedules while refreshing reports. Default value is True.
  • bRecalFormulas – true / false. True = recalculate CL functions as part of refresh. False = do not recalculate CL functions as part of refresh. Default value is True;

CL_OpenControlPanel – open the Control Panel

CL_CloseControlPanel – close the Control Panel

CL_NewReport – open the Report Wizard to create a new report

CL_SendData – send data for all input schedules in the current workbook

CL_GetSendStatus – retrieve the current send status and display the Send Status dialog box

CL_OpenWorkbook (string sWorkbook, boolean bAutoRefresh) – open a workbook from shared library

  • sWorkbook – (optional) name of the workbook to open. The name must include the path to the file in the Shared Library. If no name is supplied, or an empty is supplied, the Open Shared Library dialog box is opened for the user to select a file.
  • bAutoRefresh – (optional) enable or disable the automatic refresh of the workbook when opened. True is the deault which enables the automatic refresh. False is used to disable the automatic refresh when the workbook is opened. This is useful in macros which need to set range values which affect the results in the report before a workbook is initially refreshed when opening from the Shared Library. This parameter is only used when a workbook name is supplied.
  • Return value – True when a workbook is successfully opened. False when no workbook can be opened.

CL_OpenSelectApplication – open the application selection dialog box to change applications

CL_SelectApplication(string sApplication, string sModel) – change application to the specified Application and Model

  • sApplication – name of the Application to select.
  • sModel – (optional) name of the Model to select. If no Model is supplied, then a logon at the Application level will be attempted.
  • Returns – true / false. True = successfully selected Application and Model. False = failed, selected Application and Model remain unchanged.

CL_IsRefreshing – use to determine if the VBA macro code is being called during a report refresh cycle. This can be important because it is NOT safe to perform many operations such as selecting cells or inserting rows and columns during a report refresh cycle. This macro returns True if a refresh cycle is underway, otherwise it returns False. It can be use within VBA code with a command as follows:

IF RUN(“CL_IsRefreshing”) THEN EXIT SUB

CL_SendModelLoadData(string sSheetname, boolean bDisplayDialogBoxes, string sModelname, string sRangename) – send data just like selecting the DataManager dialog box and loading data

  • sSheetname – the worksheet tab name in the current workbook to load data for.
  • bDisplayDialogBoxes – true / false. True = display dialog boxes just like interactive load from Data Manager dialog box. False = suppress dialog boxes.
  • sModelname – the name of the Model to load data to. If value is missing then the Model from the Current View is used.
  • sRangename – the named range to load data from.
  • Returns – true / false. True = success with no messages. False = failed with messages.

CL_RunModelBusinessRule(string sRuleName, boolean bDisplayDialogBoxes, string sModelname, array sMbrs1, array sMbrs2, …) – send data just like selecting the DataManager dialog box and loading data

  • sRuleName – the name of the Business Rule to run as displayed on the Run Rule tab of the Data Manager..
  • bDisplayDialogBoxes – true / false. True = display dialog boxes just like interactive run rule from the Data Manager dialog box. False = suppress dialog boxes.
  • sModelname – the name of the Model to load data to. If value is missing then the Model from the Current View is used.
  • sMbrs1, sMbrs2, …` – a list of members for the each parameter as displayed on the Run Rule tab of the Data Manager. The order of the member list variables supplied must be the same order as the parameter list displayed in the Data Manager. The list of members must be member names from the appropriate dimension for the parameter. The list of member names can be passed as an Array or as a Range in the workbook. A missing variable is the same as not selecting any members for a parameter in the Data Manager, which automatically selects all members for the dimension. Invalid members are ignored. A list containing all invalid members will fail with a message indicating which parameter contained all invalid members. Any error message can be retrieved with the CL_GetLastErrorMsg macro.
  • Returns – true / false. True = success with no messages. False = failed with messages.

CL_ExtractModelData(array sOptions, boolean bDisplayDialogBoxes, string sModelname, array sDimensions, array sMbrs1, array sMbrs2, …) – send data just like selecting the DataManager dialog box and loading data

  • sOptions– a list of options to use for the extract operation, the list can be supplied as a VB Array or an Excel Range. In the case only one option is used it can also be supplied as VB String.
    1. SUPPRESSIFZERO – eliminate all records with a value of zero.
    2. NORESOLVESENDTOMBRS – leave all members as they are found in the Fact table.
  • bDisplayDialogBoxes – true / false. True = display dialog boxes just like interactive extract from the Data Manager dialog box. False = suppress dialog boxes.
  • sModelname – the name of the Model to extract data from. If the value is missing then the Model from the Current View is used.
  • sDimensions – a list of dimension names indicating which dimension each parameter member list comes from. The number of entries in this list must match the number of sMbrs parameters used. The list can be supplied as a VB Array or an Excel Range.
  • sMbrs1, sMbrs2, …` – a list of members for each dimension as selected on the Extract tab of the Data Manager. The order of the member list variables supplied must be the same order as the dimension list supplied in the sDimensions parameter. The list of members must be member names from the appropriate dimension. The list of member names can be passed as a VB Array or as a Range in the workbook. A missing variable is the same as not selecting any members for a parameter in the Data Manager, which automatically selects all members for the dimension. Invalid members are ignored. A list containing all invalid members will fail with a message indicating which parameter contained all invalid members. Any error message can be retrieved with the CL_GetLastErrorMsg macro. A reference to a parameter number in an error message refers to an sMbrs parameter.
  • Returns – true / false. True = success with no messages. False = failed with messages.

CL_ClearModelData(array sOptions, boolean bDisplayDialogBoxes, string sModelname, array sDimensions, array sMbrs1, array sMbrs2, …) – send data just like selecting the DataManager dialog box and loading data

  • sOptions– no options currently supported, so include an empty parameter.
  • bDisplayDialogBoxes – true / false. True = display dialog boxes just like interactive run rule from the Data Manager dialog box. False = suppress dialog boxes.
  • sModelname – the name of the Model to load data to. If value is missing then the Model from the Current View is used.
  • sDimensions – a list of dimension names indicating which dimension each parameter member list comes from. The number of entries in this list must match the number of sMbrsx parameters used. The list can be supplied as a VB Array or an Excel Range.
  • sMbrs1, sMbrs2, …` – a list of members for each dimension as selected on the Clear tab of the Data Manager. The order of the member list variables supplied must be the same order as the dimension list supplied in the sDimensions parameter. The list of members must be member names from the appropriate dimension. The list of member names can be passed as a VB Array or as a Range in the workbook. A missing variable is the same as not selecting any members for a parameter in the Data Manager, which automatically selects all members for the dimension. Invalid members are ignored. A list containing all invalid members will fail with a message indicating which parameter contained all invalid members. Any error message can be retrieved with the CL_GetLastErrorMsg macro. (Note: It is invalid to attempt to clear the entire application database by not supplying any member parameters.)
  • Returns – true / false. True = success with no messages. False = failed with messages.

CL_GetLastErrorMsg – Used together with the CL_SendLoadData macro to retrieve any error messages when the macro call returns false. This macro must be called immediately after the failing macro before the last error message can be replaced by some other message.

  • Returns – returns a string with the last error message

CL_SelectCVMember(string sMembername, Range PropertyFilter) – open the member selector dialog box for the user to select a new member as the currently selected member. The dialog box opens to the dimension containing the member passed to it.

  • sMembername – name of the member to be initially selected when the member selector dialog box opens
  • PropertyFilter – an Excel range 4 columns wide containing the filter the Member Selector will open with.
    1. AndOr – valid entries are blank, “And”, “Or”. This entry should be blank for first row.
    2. Property – valid property name for the node dimension
    3. Like – valid entries are “Like”, “NotLike”
    4. Value – filter value for the property. * can be used as a wild card to select all values that start with something.

(Note: If a Property name of “LEGALENTITYTIMEFILTER” is used, the time filter is set for a LegalEntity type dimension.)

  • Returns – name of the member selected. If the user cancels, nothing is returned and the CV remains unchanged.

CL_SelectMember(string sMembername, Range PropertyFilter) – open the member selector dialog box for the user to select a member. This member selection does not change the CV selection, but instead is just passed back as the return value. This allows for easy prompting for a member selection to be used in macros. The dialog box opens to the dimension containing the member passed in.

  • sMembername – name of the member to be initially selected when the member selector dialog box opens.
  • PropertyFilter – an Excel range 4 columns wide containing the filter the Member Selector will open with.
    1. AndOr – valid entries are blank, “And”, “Or”. This entry should be blank for first row.
    2. Property – valid property name for the node dimension
    3. Like – valid entries are “Like”, “NotLike”
    4. Value – filter value for the property. * can be used as a wild card to select all values that start with something.

(Note: If a Property name of “LEGALENTITYTIMEFILTER” is used, the time filter is set for a LegalEntity type dimension.)

  • Returns – name of the member selected. If the user cancels, nothing is returned.

CL_SelectMembers(string sDimension, array sMembernames, Range PropertyFilter) – open the member selector dialog box for the user to select a member. This member selection does not change the CV selection, but instead is just passed back as the return value. This allows for easy prompting for a member selection to be used in macros. The dialog box opens to the dimension containing the member passed in.

  • sDimension – name of the dimension that members are to be selected from.
  • sMembernames – (optional) list of the members to be initially selected when the member selector dialog box opens. The list of members must be member names from the indicated dimension. The list of member names can be passed as a VB Array or as a Range in the workbook.
  • PropertyFilter – (optional)an Excel range 4 columns wide containing the filter the Member Selector will open with.
    1. AndOr – valid entries are blank, “And”, “Or”. This entry should be blank for first row.
    2. Property – valid property name for the node dimension
    3. Like – valid entries are “Like”, “NotLike”
    4. Value – filter value for the property. * can be used as a wild card to select all values that start with something.

(Note: If a Property name of “LEGALENTITYTIMEFILTER” is used, the time filter is set for a LegalEntity type dimension.)

  • Returns – list of members selected. The list of member names is returned a vb array. If the user cancels, nothing is returned.

CL_SetCVMember(string sMembername) – set the currently selected CV member programmatically.

  • sMemberName – name of the member to be set in the CV.
  • Returns – true / false. True = successfully changed CV. False = failed because member name was invalid.

CL_SelectCVModel() – open the Model selector dialog box for the user to select a new model as the currently selected model.

CL_SelectModel() – open the Model selector dialog box for the user to select a model. The model selection does not change the CV selection. The selection is passed back as the return value.

CL_SetCVModel(string sModelname)

CL_ResetDrilldown(string Reportname) – reset any drill downs against the named report and refresh the report to the starting location in the application.

  • sReportname – name of the report to have all drill downs reset.
  • Returns – true / false. True = successfully reset drill downs. False = reset failed.

CL_PublishBook() – publishes the book definition contained in the currently selected workbook.

  • Returns – true / false. True = successfully published the book. False = failed, normally because the currently selected workbook does not contain a book definition.

CL_GetInfo(string sRequest, string sParam1, string sParam2, string sParam3) – retrieves information about the open session.

  • Returns – a table of information as an array. Each supported Request has a specific set of columns of information that are returned. The supported requests and columns in the array returned are listed below.
  • CV – current view
    1. Dimension name
    2. Dimension description
    3. Member name
    4. Member description
    5. Member unique name
  • CVModel – current model
    1. Currently selected Model name
  • Userid – user currently logged on
    1. User name – user name as entered on the logon dialog box
    2. Is administrator – 1 = User is an administrator, 2 = User is not an administrator
  • DocLib – file / folder lists from the Shared Document Library. Param1 is the folder you wish to get a listing for. To get started with a listing of the root directory, leave Param1 blank.
    1. Filename
    2. Path – path within the Shared Library to this file / folder
    3. Is folder – 1 = Folder, 0 = File
  • Models – list of models currently available for this logon session
    1. Model name
  • Dimensions – dimensions information
    1. Dimension name
    2. Is Secured – 1 = Dimension is secured, 0 = Dimension is not secured.
  • App – application information
    1. Application name
    2. Model name based on current view selection
    3. Server url
    4. Server port
    5. Shared library folder
  • AppVars – application variable definitions for a Model. Param1 can be used to select the Model variables should be returned for. If no value is supplied for Param1, variables will be returned for the Model currently selected in the current view. A row is returned for each application variable available for use.
    1. Variable name
    2. Variable name as a Function name with parameters when parameters are required
    3. Definition of the Variable
  • AppVarValue – value of requested application variable. Param1 is the name of the variable to retrieve a value for. Param2 is the Model to retrieve a variable for. If no value is supplied for Param2, the Model currently selected in the current view will be used. Variable value is resolved including any parameter values as a function call with parameters when necessary. See item 2 in AppVars for required syntax on variables requiring parameters. Only 1 row is returned.
    1. Value of the variable resolved based on the current view settings as necessary
  • SendStatus – outstanding send status information
    1. Submission id
  • UserActionRestrictions – list of actions excluded by security for this user
    1. Restriction name

CL_MacroLogon(string ServerUrl, string Userid, string Password, string Application, string Model) – logon to an application without opening the Logon dialog box. Provide support for automation to allow things such as Publishing Books automatically with the Windows Scheduler.

  • ServerUrl – url to access the server. The same url entered in the logon dialog box for interactive logon
  • Userid – valid domain\username to access the server.
  • Password – valid password for the userid.
  • Application – name of the application. The same application name displayed in the Application Selector for interactive logon.
  • Model – (optional) name of the model. The same model name selected in the Application Selector for interactive logon. If no model is supplied then a logon at the application level is attempted.
  • Return value – returns a string. If the string is empty the logon was successful. If the logon fails the string will contain an error message.

(Note: To load the Callisto.xll without the interactive logon dialog box automatically appearing place a file named “NoAutoLogon” in the same folder as the callisto.xll file. The file does not need to contain anything. The Callisto.xll looks for the presence of this file before opening the interactive logon dialog box. If the file is found the callisto.xll loads but does not perform a logon, so then the only valid actions are to call CL_Logon to open the interactive logon dialog box or to call CL_MacroLogon to logon to an application.)

CL_CancelSend() – cancels a schedule send before data is sent to the server when called during a Before Send event. This allows an input schedule to be validated by a user macro and only send data to the server when the input schedule passes validation. If this macro is called at any other time, it does not do anything.


Example of how to set the Current View from a member name contained in cell A3 on Sheet1:

Sub SetCVMember()
    Dim rg As Range

    ‘ cell A3 on Sheet1 contains CV member to be set
    Set rg = Worksheets("Sheet1").Range("A3")
    ret = Run("CL_SetCVMember", rg.Value)

    ' force Ribbon task pane to refresh
    Worksheets("Sheet2").Select
    Worksheets("Sheet1").Select
End Sub

Example of how to Select a member from VB code and place the member name in cell A3 on Sheet1:

Sub SelectMember()
  Dim rg As Range
  Dim ret

  Set rg = Worksheets("Sheet1").Range("A3")
  ret = Run("CL_SelectMember", rg.Value)
  rg.Value = ret
End Sub

Example of how to Logon to the Excel Addin with VB code without opening the Logon Dialog box.

Public Sub Logon()
    vRet = Run("CL_MacroLogon", "http://localhost", "domain\username", "password", "Sample", "Finance")
    If Len(vRet) > 0 Then
        MsgBox vRet
    End If
End Sub
  • canvasplanning/userguide/macros.txt
  • Last modified: 2014/09/01 10:17
  • by pcevli