« Process   |   Submission Package »

Excel Files for Package Input

Designer Administrator
This manual is in pilot operation.

In a Submission Package Definition, you can register an Excel file to be used for data entry in place of Forms or in addition to them. In that Excel file, you can retrieve and reflect data using the functions of [Excel-Link]. In addition to the standard functions of [Excel-Link], several functions required for using Excel files for Submission Package data entry are provided.

Processing When Launching Excel

Specifically, when you launch an Excel file for Package Input from [Contributor], the following processes are automatically executed:

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

  • Automatic configuration of the server URL

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

  • Automatic embedding of parameters

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

  • Execution of data retrieval processing or execution of pre-processing macro (3) – FP_UserExit_OpenBook[1]

Each item is described below.

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

The [Excel-Link] add-in file is automatically downloaded and installed into Excel. Therefore, on PCs that use [Contributor], it is not necessary to install the [Excel-Link] add-in into Microsoft Excel in advance. If a different version of the [Excel-Link] add-in is installed, it is replaced with the version compatible with the fusion_place server program.

2. Automatic Configuration of the Server URL

The “Server URL” in the [Excel-Link] “Connection Settings” is automatically changed to the URL of the server to which the [Contributor] that launched Excel is connected.

3. Automatic Embedding of Parameters

When you open an Excel file for Package Input from [Contributor], various parameter values (data items whose values are determined at runtime) can be embedded into cells on the sheets of the opened Excel file. Examples of parameter items include the following:

  • Application labels and names

  • Labels or names of the Fiscal Year or Relative Period to which the currently selected Process belongs

  • Labels or names of the Submitter Participant of the currently selected Package Data

  • Labels or names of the Participants that reference the currently selected Package Data

Each parameter has a predefined “name.” If you assign that name to a cell on any sheet in the Excel file for Package Input, the value is embedded into that cell. You do not need to use all parameters. You can assign names only for the parameters you need. For example, assign the name “_PPR_APP_LABEL” to the cell into which you want to embed the Application label.

A list of parameters is available in List of Parameters for Excel Files for Package Input.

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

  • In Excel, there are two scopes for “Names”: names that are valid only within each sheet and names that are valid across the entire workbook. When assigning parameter names to cells, make them names that are valid across the entire workbook.

  • If you protect sheets, unlock the cells to which parameter names are assigned so that they are writable.

  • If labels to be embedded in cells are strings that can be interpreted as numeric values, set the “Format” of the cells with parameter names to “Text.” Otherwise, if the string to be set starts with zero, the leading zero is removed (for example, the label “0091” is embedded as the numeric value “91”).

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

When you open an Excel file for Package Input from [Contributor], the following macros are automatically executed:

  1. Workbook_Open macro (no arguments)

  2. Auto_Open macro (no arguments)

  3. FP_UserExit_OpenBook[1] macro (no arguments)

None of these macros is mandatory in the Excel file for Package Input. If the Workbook_Open macro or the Auto_Open macro is not included, it is simply not executed. If the FP_UserExit_OpenBook[1] macro is not included, as an alternative process, the data retrieval processing described next is executed. The purpose of using the FP_UserExit_OpenBook[1] macro is to describe alternative processing when data retrieval alone is insufficient.

The Workbook_Open macro is executed before automatic parameter embedding. The other two macros are executed after automatic parameter embedding, in the order of Auto_Open and then FP_UserExit_OpenBook[1].

Among these, the Workbook_Open macro and the Auto_Open macro are executed according to standard Microsoft Excel behavior. That is, these two macros are also executed when the Excel file is opened directly in Microsoft Excel.

In contrast, the FP_UserExit_OpenBook[1] macro is executed only when the Excel file for Package Input is opened from [Contributor]. By using this macro, you can describe processing that you want to execute only when opening the Excel file for Package Input from [Contributor]. If the FP_UserExit_OpenBook[1] macro does not exist, it is not executed and the data retrieval processing described below is executed instead.

Do not include processing that requires user interaction in these macros. This includes using functions such as MsgBox or InputBox. If such processing is included, the Excel file for Package Input cannot be opened correctly from [Contributor].

5. Execution of Data Retrieval Processing

After opening the Excel file for Package Input and executing the Workbook_Open macro and Auto_Open macro if they are included, the standard [Excel-Link] data retrieval processing is executed for the active sheet. However, as described above, if the FP_UserExit_OpenBook[1] macro is included, that macro is executed and data retrieval processing is not executed.

Package Recalculation When Reflecting Data

After reflecting data in an Excel file for Package Input, you can configure the system so that calculation processing by Forms included in the Package (see Saving Calculation Results in Submission Package Forms) is automatically executed. To do so, set the following four Parameter Cells in the Excel file for Package Input:

  • _PPR_APP_LABEL Application label

  • _PPR_VPT_LABEL Label of the View Participant ⚠

  • _PPR_CUR_LKEY Ledger Edition Key of the currently selected target data

  • _PPR_CUR_PKGKEY Key of the currently selected Package

Package recalculation is executed only when appropriate values are set in all four cells above. Normally, these cell values are automatically set when Excel is launched from [Contributor].

If you do not need to execute package recalculation, we recommend that you do not create the “_PPR_CUR_PKGKEY” parameter cell. This parameter cell is used only for package recalculation. By not creating this cell, you can suppress package recalculation without affecting other functions.

1. Add this macro to a standard module.