Query Excel Services

* This topic applies to Nintex Workflow Enterprise Edition only

* This action requires Microsoft SharePoint Server

This workflow action will retrieve data from a Microsoft Excel workbook via Excel Services.

To use the workflow action:

Locate the action in Workflow Actions Toolbox (located on the left hand-side);

Click on the Category listings to reveal the actions; OR
Search for the action using a keyword.

Select the action, drag it onto the design canvas and drop it onto a design pearl.

Alternatively

Left-clicking the pearl, mouse-over Insert Action and then the Categories to reveal the actions, click the required action from the list.

To change the settings used by the action:

On the action’s title click the down arrow to activate a drop-down
Select Configure; OR
Double-click the action's icon.

For more information on the other options in the drop-down, please refer to the Getting started with the Nintex Workflow designer.

Options within this action

URL

URL to the excel services web service.

Providing this URL is optional. If the URL is left blank, this action will use the default excel services URL based on the URL of the site workflow is running on.

Credentials

Username and password

Valid credentials to access the Excel Services web service. The credentials must have access to the workbook.

Workbook path

The location of the Excel workbook to query. The workbook must be in a configured Excel Service trusted location.

Sheet name

The name of the Excel sheet in the workbook that will be queried.

Update cell values

Specifies cells that should be set in the workbook before data is retrieved. Changes to these cell values are not committed to the workbook, they are only used to determine the values of the cells to retrieve information.

For example, if the cell to retrieve is the sum of E2 and E4, E2 and E4 can be set in this section and the result will be reflected in the cell to retrieve that data. Multiple cells can be updated by clicking the Add cell to update link.

Cell position the location of the cell to update. The cell location must be specified in the 'A1' format or be the defined name of a cell. Note that only single cells can be updated, you cannot specify a range with multiple cells.
Cell value the value to update the cell with.

Cells to retrieve

The range of cells to retrieve values from. A single cell must be specified in the 'A1' format. Cell ranges must be specified in the 'A1:B2' format. If a cell or cell range is named, the defined name can also be specified.

Store result in

The workflow variable to store the resulting value in.

If the selected variable can only contain a single value and a range is returned, only the first cell in the range will be used.

In a collection, values are stored left to right, top to bottom. For example, if the range to return is A1:B3, the values will be stored in this order: A1, A2, A3, B1, B2, B3.

Retrieve as formatted text

Specifies whether or not to keep the cell formatting, or only return the plain value. For example, if a cell is formatted to display numbers as currency, selecting this option will return a string with the currency symbol matching the display in Excel. If this option is not selected, only the numeric value of the cell is stored.

date values when an unformatted date value is stored into a collection, it will be stored as a numeric representation of the date (this is how Excel Services provides the data). The action cannot determine the difference between a number and a date represented as a number, so the value will be treated as a number when added to the collection. When returning a date result into a Date Time variable, the action will assume the value being retrieved is a date and store it as such.