« Using Excel-Link Functions in Macros   |   Requester »

Execute Server-side Processing from Excel-Link (fusion_place >= 14.1)

This manual is in pilot operation.

This feature enables you to request calculation processing by forms and scripts from Excel and have it executed (on the server side).
No macro description is required for configuration. You can easily configure it based on an Excel sheet that describes the processing conditions.

1. How to Execute Processing

You can execute server-side calculation processing using one of the following two methods.

Method How to Specify Processing Details

a. Execute server-side calculation processing after executing Reflect or Retrieve and Reflect.
The data after execution is displayed on the sheet after the Reflect or Retrieve and Reflect processing.

For each sheet on which you want to execute server-side calculation processing, prepare a processing instruction sheet named the sheet name + "_FPCTL1".
The processing instruction sheet can be executed even if it is hidden.
The starting cell of the processing instruction table (the Data Presence Header cell) must be cell A1.

b. Execute server-side calculation processing by calling a procedure from a macro, independent of Reflect or Retrieve and Reflect.

You can describe the processing instruction table starting from any cell on any sheet.
Pass the starting cell of the processing instruction table (the Data Presence Header cell) to the procedure as an argument.

2. Processing Instruction Table

The processing instruction table must be a rectangular cell range on a specific sheet.
The first row of the processing instruction table is the header row and specifies headers that indicate the contents of each column. Rows from the second row onward are detail rows and describe processing execution instructions.

The contents of the processing instruction table are as follows.

Item Count Header Detail Row Settings

Data Presence

1

X

Enter any character string in rows to be used.
Processing proceeds from the second row. When a row where this column is blank appears, no further rows are processed.
If this column is not blank but any of the Type through Participant columns is blank, that row is skipped and the next instruction data (to the right, then downward) is processed.

Type

1

TYPE

For forms, F
For scripts, S

Application

1

APPL

Application label

Action

1

ACTION

Form or script label

Ledger Edition

1

EDITION

Key of the Ledger Edition (WORKSPACE, etc.)

Participant

1

PARTICIPANT

Label of the Participant used for calculation processing.
The user using [Excel-Link] must have execution authority.

Parameter Value

n

"P:" + Dimension label
Example)
P:#FY
P:#SCENARIO

Specify parameter values (member labels) to be passed to the form or script for each dimension.

The columns from Type through Parameter Value can also be repeated in the horizontal direction.
The "TYPE" column indicates the delimiter of tables arranged horizontally.
For example, by specifying "X" in cell A1, "TYPE" in cell A2 and "APPL" in cell A3, arranging items to the right in the column direction, and then writing "TYPE" to the right of that, you can start another table.

The order of items other than the "TYPE" column is not fixed. Uppercase and lowercase letters in the processing instruction table are not distinguished, and leading and trailing spaces in strings are ignored.

3. Details of Processing Execution

(a) Automatically Execute Server-side Calculation Processing After Executing Reflect or Retrieve and Reflect

  1. When "Data Reflect" or "Data Retrieve and Reflect" processing is executed, the specified form or script processing is executed.
    The same applies to "Batch Processing / Data Reflect" and "Batch Processing / Data Retrieve and Reflect".

  2. This processing is executed only for sheets that have a corresponding processing instruction sheet.

  3. The processing order is as follows.
    (1) Execute normal Data Reflect or Data Retrieve and Reflect processing
    (2) Execute server-side calculation processing by forms [1] or scripts according to the processing instruction table
    (3) Retrieve data after processing and update the data on the Excel sheet to the latest
    (4) For Excel files launched from Contributor, execute package recalculation processing (if specified)
    After processing, ledger data after executing the forms or scripts described in the processing instruction table is displayed on the Excel sheet.
    Note that the additional processing added by the presence of the processing instruction table is (2) and (3).

  4. Server-side calculation processing reads and executes the contents of the processing instruction table with priority given to the horizontal direction.
    If there are multiple tables, processing of the first detail row of all tables is executed in the horizontal direction, and then processing proceeds to the next row.

  5. If an error or warning occurs, processing is stopped at that point and a message is displayed.

(b) Execute Processing by Calling a Procedure from a Macro, Independent of Reflect or Retrieve and Reflect

Procedure specifications

Procedure Name Description Arguments Return Value

FP_RunServerCalc

Executes server-side calculation processing according to the processing instruction table specified by the argument.

TableRange As Range Starting cell of the processing instruction table (Data Presence Header cell)

(None)

  1. Execute FP_RunServerCalc with the argument specified.

  2. If an error occurs, VBA Err is raised.
    Handling such as displaying messages related to the raised error must be implemented on the calling side.

1. If triggers are set for the form, the triggers are executed.