Excel Calculate activity

An activity that lets you write (temporarily or permanently) and read from a Microsoft Excel file in the same step.



Configure the Excel Calculate activity

To configure the Excel Calculate activity, do the procedure in this topic.

Good to Know

  • In most text fields, you can use process data variables as an alternative to literal data values.
  • You can configure whether this activity waits for other activities before it runs.

    For more information, refer to How Do I Configure an Activity to Wait for Other Incoming Activities?

  • Documentation for Integrations with Third-Party, External, or Open Source Technologies

    The highest priority for AgilePoint NX Product Documentation is accuracy. Whenever feasible, AgilePoint provides input details, business rules, or example values for third-party integrations. These can include any technology that is not native to AgilePoint NX, such as Salesforce, Active Directory, or REST. However this information is subject to change without notice to AgilePoint. Because we can not guarantee the accuracy of this information, the details provided for third-party product input values, as well as examples for third-party product integrations, are limited in the AgilePoint documentation. It is the responsibility of third-party vendors to provide documentation related to these aspects of their software and services.

    For examples of use case implementations or configuration field input values for third-party products, AgilePoint recommends these resources:

    • AgilePoint Community Forums - An AgilePoint-moderated, crowd-sourcing user forum where you can ask questions about specific techniques, the solutions to use cases, workarounds, or other topics that may not be covered in the Product Documentation.
    • Professional Services - If you can not find the information you need for your specific business problem, mentoring is available through AgilePoint Professional Services.
    • Personalized Training - AgilePoint can provide personalized training for your organization. To request personalized training, contact AgilePoint Sales.
    • Third-Party Vendor Documentation - Whenever feasible, AgilePoint provides links to third-party vendor documentation.

      This does not include technology standards or protocols, such as JavaScript, REST, or FTP. Resources for these technical standards are publicly available on the Internet.

How to Start

  1. On the Application Explorer screen, do one of these:
  2. Do one of these:
    • Add an activity:
      1. In the Process Builder, go to the Activity Library, and open the SharePoint SharePoint icon tab.
      2. On the SharePoint SharePoint icon tab, drag the Excel Calculate Excel Calculate icon activity onto your process.
    • Change an activity:
      1. In your process, double-click your activity.

Procedure

  1. Complete the fields on the General Configuration screen.

    For more information, refer to Configure General Options for a System Activity.

  2. Click Design Time Repository Configuration Design Time Repository Configuration icon.
  3. Complete the fields on the Design Time Template Configuration screen.
  4. Click Run Time Repository Run Time Repository icon.
  5. On the Runtime Repository screen, select the SharePoint as target location for your file..
  6. Click Run Time Repository Configuration Run Time Repository Configuration icon.
  7. Complete the fields on the Runtime Template Configuration screen.
  8. Click Save To Repository Save To Repository icon.
  9. On the Save To Repository screen, select the repository to save your file.
  10. Click Save To Repository Configuration Save To Repository Configuration icon.
  11. On the Save To Repository Configuration screen, configure the repository to save your file.
  12. Click Excel Calculate Configuration Excel Calculate Configuration icon.
  13. Configure Excel Calculate Configuration screen.

    For more information, refer to Read and Create an Excel File in SharePoint

  14. (Optional) Click Advanced Advanced icon > E-mail Notifications E-mail Notifications icon.

    For more information, refer to Configure E-mail Notifications for Any Activity.

General Configuration

Specifies the common information for a system activity.

Screen



Fields

Field Name Definition

Display Name

Function:
Specifies the activity name that shows in your process.
Accepted Values:
One line of text that can have spaces.
Default Value:
None
Accepts Process Data Variables:
No
Example:
Refer to:

Description

Function:
Specifies an optional text description for your activity.
Accepted Values:
More than one line of text.
Default Value:
None
Accepts Process Data Variables:
No

Design Time Template Configuration

Specifies the data source for your design time template in SharePoint.



Fields

Field Name Definition

SharePoint

Function:
Specifies the access token that connects to your SharePoint site.
Accepted Values:
A list of access tokens configured for your environment.
Default Value:
None

Add Token Add Token icon

Opens this Screen:
Access Token for SharePoint
Function of this Screen:
Configure an access token to connect to SharePoint.

Site

Function:
Specifies the URL for your SharePoint site.
Accepted Values:
A valid SharePoint site URL
Default Value:
None
Accepts Process Data Variables:
Yes
Example:

Document Library

Function:
Specifies the name of the SharePoint document library.
Accepted Values:
A valid SharePoint document library name.
Default Value:
None
Accepts Process Data Variables:
Yes

File Path

Function:
Specifies the SharePoint file. The activity uses the location of the specified file when the process runs.
Accepted Values:
A valid absolute path for the file.
Default Value:
None
Accepts Process Data Variables:
No

Runtime Template Configuration

Specifies the data source for your runtime template in SharePoint.



Fields

Field Name Definition

SharePoint

Function:
Specifies the access token that connects to your SharePoint site.
Accepted Values:
A list of access tokens configured for your environment.
Default Value:
None

Add Token Add Token icon

Opens this Screen:
Access Token for SharePoint
Function of this Screen:
Configure an access token to connect to SharePoint.

Site

Function:
Specifies the URL for your SharePoint site.
Accepted Values:
A valid SharePoint site URL
Default Value:
None
Accepts Process Data Variables:
Yes
Example:

Document Library

Function:
Specifies the name of the SharePoint document library.
Accepted Values:
A valid SharePoint document library name.
Default Value:
None
Accepts Process Data Variables:
Yes

File Path

Function:
Specifies the SharePoint file. The activity uses the location of the specified file when the process runs.
Accepted Values:
  • Choose File Path - Specifies an absolute path for the file.
  • Dynamic File Path - Specifies a process data variable that stores the relative path of the file.
Default Value:
Dynamic File ID
Accepts Process Data Variables:
Yes

Save To Repository Configuration

Specifies the target location for your Microsoft Excel document on SharePoint.



Fields

Field Name Definition

SharePoint

Function:
Specifies the access token that connects to your SharePoint site.
Accepted Values:
A list of access tokens configured for your environment.
Default Value:
None

Add Token Add Token icon

Opens this Screen:
Access Token for SharePoint
Function of this Screen:
Configure an access token to connect to SharePoint.

Site

Function:
Specifies the URL for your SharePoint site.
Accepted Values:
A valid SharePoint site URL
Default Value:
None
Accepts Process Data Variables:
Yes
Example:

Document Library

Function:
Specifies the name of the SharePoint document library.
Accepted Values:
A valid SharePoint document library name.
Default Value:
None
Accepts Process Data Variables:
Yes

Folder Path

Function:
Specifies the path of the folder.
Accepted Values:
  • Choose Folder Path - Specifies an absolute path for the folder.
  • Dynamic Folder Path - Specifies a process data variable that stores the relative path of the folder.
Default Value:
Dynamic Folder Path
Example:
  • ${HomeDirectory}\sample2.doc
  • MyFolder1/Sample3.doc

Overwrite If File Exists

Function:
Specifies if the activity replaces the file if it already exist with the same name at the specified target location.
Accepted Values:
  • Selected - The activity replaces the file if it exists with the same name at the target location.
  • Deselected - The activity saves the file with a different name.
Default Value:
Selected

Excel Calculate Configuration

Specifies an operation to create, read, and store data from a Microsoft Excel file.



Fields

Field Name Definition

SharePoint

Function:
Specifies the access token that connects to your SharePoint site.
Accepted Values:
A list of access tokens configured for your environment.
Default Value:
None

Excel Services URL

Function:
Specifies the URL of SharePoint Excel Services.
Accepted Values:
Default Value:
None
Accepts Process Data Variables:
Yes
Example:
  • http://ind-01:8888/_vti_bin/ExcelService.asmx
  • $\{SharePointURL\}$\{ExcelService\}

Runtime Template

Function:
Specifies the name of Microsoft Word or Microsoft Excel template file the activity uses to create Microsoft Word or Excel file when the process runs.
Accepted Values:
  • A valid template file name.
  • A process data variable.

    You can use Process Data screen to specify a process data variable.

Default Value:
None
Accepts Process Data Variables:
Yes

Design Time Template

Function:
Specifies the name of Microsoft Excel template file that is used when you are creating or changing a process.

Once you enter Microsoft Excel template file name in the Design time Template box, the named fields box is populated with all the named fields from Microsoft Excel sheet.

The design time template document is used only to access the named fields of Microsoft Excel sheet. This document is not used at runtime.

Accepted Values:

A valid Microsoft Excel template file name with the full path.

Note the following:

  • The runtime template file and the design time template file must have the same named fields. This indicates that both the files must have same content.

  • Also, while designing in Process Builder, the file must be accessible to the application designer from their computer using the Browse button.
Default Value:
None
Accepts Process Data Variables:
No
Example:
C:\Excel\ExcelSheet\ExcelServices.xlsx

Save To

Function:
Specifies the location and file name to save the resulting Microsoft Excel document.
Accepted Values:
Default Value:
None
Accepts Process Data Variables:
Yes
Example:
http://ind-01:8888/ExcelServicesDemo/${/my:myFields/my:SaveWrite}.xlsx

Click to Load Excel Fields

Function:
Specifies the named fields populated from Microsoft Excel sheet once you specify the design time Template file.

This field is used for the mapping purpose. You must map Microsoft Excel named fields to the process data variable to:

  • Write to an Excel document.
  • Read from an Excel document.

A named field in Microsoft Excel is the name of a cell or range that is used instead of the cell's reference name. For example, empName, a named field, is easier to use instead of its cell's reference name, C21.

Accepted Values:
A named field selected from the list.
Default Value:
None
Accepts Process Data Variables:
No

Move All

Function:
Moves all the fields in the Excel Field box at one time.

Excel Field

Function:
Specifies Microsoft Excel field to map to the process value. The mapping is performed to change the fields in Microsoft Excel document, supplied as output.
Accepted Values:
Read only - A Microsoft Excel field.

Use the Move All button to move the fields from Click to Load Excel Fields box to the Excel Field column.

You can also drag the Microsoft Excel fields from the Click to Load Excel Fields box to the Excel Field column.

Default Value:
None

Process Value

Function:
Specifies the process data variable to map to the Excel Field to change each field in Microsoft Excel document.
Accepted Values:
  • A number or a text string that can contain spaces.

    This must be in accordance with the data type of Microsoft Excel field.

  • A process data variable

Note:

  • Once Microsoft Excel field is in the Excel Field column, to map a Excel Field to a process value, select the row, and use Process Data screen.
Default Value:
None
Accepts Process Data Variables:
Yes

Formatted

Function:
Specifies if the formatted value of Microsoft Excel cell is read instead of the actual value.

The formatted values are useful to denote numbers or dates such as in e-mails.

To Open this Field:
Click the Read tab.
Accepted Values:
  • Deselected - The numerical value of the cell is read. For example, 1023
  • Selected - The formatted value of the cell is read. For example, €1,023.34
Default Value:
Deselected.

However, when you add a row, by default, the Formatted check box is selected.

Example:
When the Formatted check box is selected:
  • A Percentage type cell returns 15%, instead of 15, where 15 is the numerical value and % is the formatting code.
  • A Currency type cell returns €1,023.34 instead of 1023.34, where 1023.34 is the numerical value and € is the formatting code.

This indicates that the formatting code of Microsoft Excel cell is also read along with the cell's numerical value.

Delete Delete icon

Function:
Deletes the row.