« Execute Pivot Query   |   Requester »

Using Excel-Link Functions in Macros

This manual is in pilot operation.

VBA procedures are provided to use [Excel-Link] functions in macros.

1. Preparation

(Ensure that Excel-Link is registered as an add-in beforehand.)

To use [Excel-Link] functions in macros, you must configure "Reference Settings" so that the macro-containing workbook can access the [Excel-Link] add-in file.

Open the Visual Basic Editor via the Microsoft Excel menu, then select Tools  References. In the displayed dialog box, check the box to the left of "FP_EXCEL_LINK" in the "Available References" list.

2. Explanation of VBA Procedures

(1) Procedures to Call fusions Menu Functions from Macros

Each item in the fusions menu has a corresponding VBA procedure (Sub). These procedures do not take any arguments. By using these procedures, you can, for example, assign functions to buttons on a sheet, allowing users to execute functions by clicking them.

Procedure Description Corresponding fusions Menu Item

FP_Retrieve

Executes data retrieval for the currently selected sheet.

"Retrieve"

FP_Reflect

Executes data reflection for the currently selected sheet.

"Reflect"

FP_Clear

Executes sheet clear processing for the currently selected sheet.

"Clear"

FP_RetrieveReflect

Executes data retrieval and reflection for the currently selected sheet.

"Retrieve then Reflect"

FP_BatchRetrieve

Executes data retrieval for all sheets in the currently selected workbook with names starting with "FP_", processing them sequentially from left to right.

"Batch" > "Retrieve"

FP_BatchReflect

Executes data reflection for all sheets in the currently selected workbook with names starting with "FP_", processing them sequentially from left to right.

"Batch" > "Reflect"

FP_BatchClear

Executes sheet clear processing for all sheets in the currently selected workbook with names starting with "FP_", processing them sequentially from left to right.

"Batch" > "Clear"

FP_BatchRetrieveReflect

Executes data retrieval and reflection for all sheets in the currently selected workbook with names starting with "FP_", processing them sequentially from left to right.

"Batch" > "Retrieve then Reflect"

FP_PivotQuery

Executes a pivot query.

"PivotQuery"

FP_ChangeLocales

Displays a dialog box to switch the display language.

"Language"

FP_ConfigureConnection

Displays a dialog box to configure the connection settings.

"Connection"

FP_ConfigureRegions

Displays a dialog box to configure linked regions.

"Regions"

FP_CreateSpecifications

Outputs the linked region settings as a specification document.

"Specifications"

FP_PasteMember

Displays a member selection screen and pastes the selected member labels into the target cell/range.

"Paste Members"

FP_ConfigureSheetAttributes

Displays a dialog box to configure processing conditions applied to the entire sheet (not just linked regions).

"Sheet Attributes"

FP_ProtectSheet

Protects the sheet using the registered Excel-Link Sheet Password.

"Protect Sheet"

FP_ChangePassword

Displays a dialog box to register/change the Excel-Link Sheet Password.

"Sheet Password"

FP_ToggleLicenseReminder

Toggles the display of the license reminder.

"License Reminder Disabled"

FP_About

Displays the "About" dialog box.

"About"

(2) Procedures to Retrieve and Modify Excel-Link Settings

These procedures allow retrieving and modifying settings within [Excel-Link].

Procedure Description Arguments Return Value

FP_GetLocale

Retrieves the currently selected display language.

(None)

Japanese: "ja"
English: "en"

FP_SetLocale

Sets the display language.

Locale As String

Japanese: "ja"
English: "en"

(None)

FP_SetPassword

Sets the Excel-Link Sheet Password. Ensure the sheet is unprotected before execution. After execution, the sheet will be protected.

Sheet As Worksheet
Target Sheet

Password As String
Password

(None)

FP_IsLicenseReminderDisabled

Retrieves the display status of the license reminder.

(None)

Displayed: False
Hidden: True

FP_SetLicenseReminderDisabled

Sets the display status of the license reminder.

IsLicenseReminderDisabled As Boolean
Displayed: False
Hidden: True

(None)

FP_CopyConfigurations

Copies all Excel-Link configuration settings from one sheet to another.

SourceSheet As Worksheet
Source Sheet

DestinationSheet As Worksheet
Destination Sheet

(None)

3. Procedures for Complex Processing

When executing Retrieve/Reflect Data from fusions menu, only the currently selected sheet is processed. However, if you want to process multiple sheets based on various conditions, you can use the following procedures to create macros that selectively process target sheets based on specific conditions.

If you simply want to process multiple sheets at once, you can use the Batch Processing function from the fusions menu instead of writing a macro.
Procedure Description Arguments Return Value

FP_RetrieveSilently

Executes data retrieval for the specified sheet.

Sheet As Worksheet
Target sheet

True if target linked regions exist, False otherwise.

FP_ReflectSilently

Executes data reflection for the specified sheet.

(Same as above)

(Same as above)

FP_RetrieveReflectSilently

Executes data retrieval and reflection for the specified sheet.

(Same as above)

(Same as above)

FP_ClearSilently

Executes sheet clear processing for the specified sheet.

(Same as above)

(Same as above)

FP_AdvancedPasteMember

Displays a member selection screen and pastes selected member labels into the specified range.

TargetRange As Range
Range for pasting member labels

Optional Arguments:

ApplicationLabel As String
Application label IsApplicationSelectable As Boolean
If True (default), allows application selection on the member selection screen. If False, does not allow selection.

DimensionLabel As String
Dimension label

IsDimensionSelectable As Boolean
If True (default), allows dimension selection on the member selection screen. If False, does not allow selection.

NamePastingMode As Integer
Specifies whether to paste member names along with labels and how to paste them.

0: Do not paste.
1: Paste if the target cell for names is blank (default).
2: Paste even if the target cell for names is not blank.

(None)

FP_GetParticipantsForCurrentUser

Returns a list of participant units assigned to the current user in the specified application.

ApplicationLabel As String Application label

Collection Contains elements of the FP_Descriptor class, which has the following properties:

Label
Name

Each stores the label and name of the participant unit.

FP_ShowMenu

Toggles menu visibility.

Show As Boolean
Set to True to display, or False to hide. The setting is lost upon Excel restart (menus are displayed by default upon restart).

(None)

FP_ShowRibbon

Toggles ribbon visibility.

Show As Boolean
Set to True to display, or False to hide. The setting is retained even after restarting Excel.

(None)

(fusion_place >= 14.1) FP_CallWebApi

Calls the Web-API.

WebApiRequest As WebApiRequest
Information required to execute the Web-API

WebApiRequest has the following properties:

WebApiType As String
Type of Web-API to execute

Description As String
Description of Web-API

Parameters As Collection
Web-API parameters
Parameters can be added using the addParameter method, specifying Name, Key, and Value.

Contents As Collection
Request contents
Contents can be added using the addContents method.

WebApiResponse
Web-API execution result

WebApiResponse has the following properties:

WebApiType As String
Type of executed Web-API

Description As String
Description of Web-API

WebApiCode As FP_WebApiCode
Execution result code of Web-API

WebApiCodeString As String
Execution result code of Web-API (string)

Message As String
Execution result message of Web-API

StartedAt As String
Start time of Web-API execution

EndedAt As String
End time of Web-API execution

ReturnedContents As Collection
Contents of Web-API execution result, stored one row at a time.

ResponseXml As String
Response XML

(fusion_place >= 14.1) FP_CreateWebApiRequest

Generates a WebApiRequest object.

WebApiType As String
Type of Web-API to execute

Description As String
Description of Web-API

WebApiRequest
Information required to execute Web-API

3. Code Samples

(1) Silent Procedures

Among the procedures listed above, those with "Silently" at the end of their names (hereafter referred to as silent procedures) differ from FP_Retrieve, FP_Reflect, FP_RetrieveReflect, and FP_Clear (hereafter referred to as interactive procedures) in the following ways:

  1. The target sheet is specified as an argument.

    Interactive procedures always process the currently selected sheet.

  2. They return a value (Function procedure).

    Interactive procedures do not return a value (Sub procedure).

  3. They do not display a message box upon completion.

    Interactive procedures display a message box upon completion, but silent procedures do not. This prevents interruptions when processing multiple sheets, as displaying a message box after each sheet would disrupt the workflow.

  4. If there is an error in the sheet configuration, they raise an error instead of displaying a message box.

    Interactive procedures display a message box if there is an error in the sheet configuration, but silent procedures raise an error instead.
    Calling macros can handle errors using the On Error statement, allowing for flexible handling such as skipping errors and continuing with the next sheet.
    When an error occurs, its details are stored in Err.Description.

The following sample macros perform batch processing on all sheets:

  '
  ' Retrieve data for all sheets
  '
  Public Sub Retrieve()
      Dim Sheet As Worksheet

      On Error GoTo ERR_HANDLER

      For Each Sheet In ActiveWorkbook.Worksheets
          FP_RetrieveSilently Sheet
      Next
      Exit Sub

  ERR_HANDLER:
      MsgBox "Error occurred on sheet " & Sheet.Name & ": " & vbCrLf & vbCrLf & Err.Description, vbOKOnly + vbExclamation
  End Sub

  '
  ' Reflect data for all sheets
  '
  Public Sub Reflect()
      Dim Sheet As Worksheet

      On Error GoTo ERR_HANDLER

      For Each Sheet In ActiveWorkbook.Worksheets
          FP_ReflectSilently Sheet
      Next
      Exit Sub

  ERR_HANDLER:
      MsgBox "Error occurred on sheet " & Sheet.Name & ": " & vbCrLf & vbCrLf & Err.Description, vbOKOnly + vbExclamation
  End Sub

(2) FP_GetParticipatsForCurrentUser

Outputs the labels and names of participant units in an application labeled "DEMO" that the currently logged-in user has execution authority for, to the debug window.

  Sub PrintParticipants()
      Dim Participants As Collection
      Dim Participant As FP_Descriptor

      Set Participants = FP_GetParticipatsForCurrentUser("DEMO")

      For Each Participant In Participants
          Debug.Print Participant.Label & " - " & Participant.Name
      Next

  End Sub

(3) (fusion_place >= 14.1) FP_CallWebApi, FP_CreateWebApiRequest

Executes Importing Dimension Members.

  Sub CallUpdateDimension()
      Dim request As WebApiRequest
      Dim response As WebApiResponse

      ' Create request object
      Set request = FP_CreateWebApiRequest("UPDATE_DIMENSION", "Update SHOHIN dimension")
      ' parameters
      request.addParameter "APPLICATION", "", "DEMO"
      request.addParameter "DIMENSION", "", "SHOHIN"
      request.addParameter "ROLE", "", "DESIGNER"
      ' contents
      request.addContents "ADD_OR_UPDATE_MEMBER,HDR,LABEL,REMOVE_CHILDREN,NAME:en,NAME:ja,P:#MEMBER_TYPE,P:#LEAF,P:#ACTIVE,PARENT,IS_ROOT"
      request.addContents "ADD_OR_UPDATE_MEMBER , DTL, ""T01"", ""TRUE"", """", ""TEST"", ""TEMPLATE"", ""TRUE"", ""TRUE"", ""SHOHIN_KEI"", ""FALSE"""

      ' Execute Web-API
      Set response = FP_CallWebAPI(request)

      If response.WebApiCode = API_SUCCESSFUL Then
          MsgBox "Success"
      Else
          MsgBox "Failed" & vbCrLf & response.WebApiCodeString & ":" & response.Message
      End If
  End Sub

Executes Exporting Dimension Members.

  Sub CallExportDimension()
      Dim request As WebApiRequest
      Dim response As WebApiResponse

      ' Create request object
      Set request = FP_CreateWebApiRequest("EXPORT_DIMENSION", "Export SHOHIN dimension")
      ' parameters
      request.addParameter "APPLICATION", "", "DEMO"
      request.addParameter "DIMENSION", "", "SHOHIN"

      ' Execute Web-API
      Set response = FP_CallWebAPI(request)

      If response.WebApiCode = API_SUCCESSFUL Then
          MsgBox "Success"

          Dim returnedContent As Variant
          For Each returnedContent In response.ReturnedContents
              MsgBox returnedContent
          Next returnedContent
      Else
          MsgBox "Failed" & vbCrLf & response.WebApiCodeString & ":" & response.Message
      End If
  End Sub