« Process   |   Submission Package »

Excel File for Package Input

Designer Administrator
This manual is in pilot operation.

In the Submission Package Definition, you can register an Excel file used for data input instead of (or in addition to) the form. In this Excel file, you can use the [Excel-Link] feature to obtain and reflect data. In addition to the standard features of [Excel-Link], there are several necessary functions prepared for using the Excel file for submission package data input.

Processing When Launching Excel

Specifically, when launching an Excel file for package input from the [Contributor], the following processes are automatically executed:

  • Automatic incorporation of the [Excel-Link] add-in

  • Automatic setting of the server URL

  • Execution of the pre-processing macro (1) - Workbook_Open macro

  • Automatic embedding of parameters

  • Execution of the pre-processing macro (2) - Auto_Open macro

  • Execution of the data retrieval process or the pre-processing macro (3) - UserExit_OpenBook macro

Each of these processes is explained below.

1. Automatic Incorporation of the Excel-Link Add-in

The [Excel-Link] add-in file is automatically downloaded and incorporated into Excel. Therefore, it is not necessary to pre-install the [Excel-Link] add-in in Microsoft Excel on the PC using the [Contributor]. If a different version of the [Excel-Link] add-in is already incorporated, it will be replaced with the one compatible with the version of the fusion_place server program.

2. Automatic Setting of the Server URL

The "Server URL" in the "Connection Settings" of [Excel-Link] is automatically changed to the URL of the connection server of the [Contributor] from which Excel is launched.

3. Automatic Embedding of Parameters

When opening an Excel file for package input from the [Contributor], various parameter values (data items determined at runtime) can be embedded in the cells on the sheet of the opened Excel file. Examples of parameter items include:

  • Labels or names of applications

  • Labels or names of the fiscal year or relative period to which the currently selected process belongs

  • Labels or names of the submitting participant of the currently selected package data

  • Labels or names of the participant referring to the currently selected package data

Each parameter has a predetermined "name." If you assign that name to a cell on any sheet in the Excel file for package input, the value will be embedded in that cell. You do not need to use all the parameters; you can assign the names to the necessary parameters only. For example, assign the name "_PPR_APP_LABEL" to the cell where you want to embed the application label.

The list of parameters is available in "Excel File Parameter List for Package Input."

When assigning parameter names to cells, please note the following points:

  • There are two types of scopes for "names" in Excel: names that are valid only within each sheet and names that are valid throughout the workbook. When assigning parameter names to cells, use names that are valid throughout the workbook.

  • If you protect the sheet, unlock the cells with parameter names and make them writable.

  • If the labels to be embedded in the cells can be interpreted as numbers, set the "Number Format" of the cells with parameter names to "Text." Otherwise, if the embedded string starts with a zero, that zero will be erased (e.g., the label "0091" will be embedded as the number "91").

4. Execution of Pre-Processing Macros (1), (2), (3)

When opening an Excel file for package input from the [Contributor], the following macros are automatically executed:

  1. Workbook_Open macro (no arguments)

  2. Auto_Open macro (no arguments)

  3. FP_UserExit_OpenBook macro (no arguments)

None of these macros are mandatory to include in the Excel file for package input. If the Workbook_Open macro and the Auto_Open macro are not included, they will simply not be executed. If the FP_UserExit_OpenBook macro is not included, the data retrieval process described next will be executed as an alternative (the purpose of the FP_UserExit_OpenBook macro is to describe an alternative process if the data retrieval process alone is insufficient).

The Workbook_Open macro is executed before the automatic embedding of parameters, and the other two macros, Auto_Open and FP_UserExit_OpenBook, are executed after the automatic embedding of parameters in that order.

The execution of the Workbook_Open macro and Auto_Open macro follows the usual specifications of Microsoft Excel. In other words, these two macros are executed even when the Excel file is directly opened in Microsoft Excel.

On the other hand, the FP_UserExit_OpenBook macro is executed only when the Excel file for package input is opened from the [Contributor]. Using this macro, you can describe processes that you want to execute only when the Excel file for package input is opened from the [Contributor]. If the FP_UserExit_OpenBook macro is not included, it will not be executed, and the data retrieval process (described below) will be executed instead.

Do not describe any process in these macros that requires user responses, such as MsgBox functions or InputBox functions. If such processes are described, the Excel file for package input cannot be correctly opened from the [Contributor].

5. Execution of Data Retrieval Process

After opening the Excel file for package input and executing the Workbook_Open and Auto_Open macros (if included), the usual data retrieval process of [Excel-Link] is executed on the active sheet. However, as mentioned above, if the FP_UserExit_OpenBook macro is included, it will be executed, and the data retrieval process will not be executed.

Recalculation of Package Upon Data Reflection

After reflecting the data in the Excel file for package input, the calculation process by the form included in the package (see Saving Calculation Results in the Submission Package Form) can be automatically executed. To do this, include the following four parameter cells in the Excel file for package input:

  • _PPR_APP_LABEL Application Label

  • _PPR_VPT_LABEL Label of the Referenced Participant

  • _PPR_CUR_LKEY Ledger Key of the Currently Selected Target Data

  • _PPR_CUR_PKGKEY Key of the Currently Selected Package

The package recalculation is executed only if all the above four cells have appropriate values set. Usually, these cell values are automatically set when Excel is launched from the [Contributor].

If you do not need to execute the package recalculation, it is recommended not to include the "_PPR_CUR_PKGKEY" parameter cell. Since this parameter cell is used only for package recalculation, excluding this cell will suppress the package recalculation without affecting other functions.