User Tools

Site Tools


READ EXCEL ACTION

Guide Section: Processing Events and Performing Actions | EASAP Tree: EVENT PROCESSINGACTION / PROCESSES


READ EXCEL ACTION reads data from cell or named ranges as specified in the Ranges: parameter in an Excel spreadsheet by referencing existing SHEET's or specifying a ‘Named Range’ for a specific spreadsheet in the Sheets: parameter. Data read from a range in the specified spreadsheet are set as values of explicit LIST's as specified in the Lists: parameter. The Ranges: parameter is used in conjunction with the Types: parameter to determine the data read from the specified spreadsheets. There are two methods for specifying the range to read:

READ EXCEL ACTION may fail on spreadsheets with macros that move or delete cells, these spreadsheets need to be modified (either change any macros that move or delete cells or create a custom sheet with non-moved/deleted cells that refer to the moved/deleted cells.)

READ EXCEL ACTION
Essential Parameters:
Sheets:Select from list of available SHEET's as data sources
Ranges:Comma-separated list of named or cell ranges to be read
(eg. start_cell:end_cellB6:G6 or single cell single_cellA4)
Types:Select type of read for each range specified (COLUMN, NORMAL RANGE, ROW)
Lists:Comma-separated list of new LIST names to store data read from spreadsheets
Optional Parameters:
Delimiter:Character to act as delimiter in DORs in Ranges: parameter. default→%.
Do if:Logical expression: if 'FALSE' then action NOT performed, otherwise 'TRUE'.
Replace Nulls:Value to replace any null values returned by EXCEL READ ACTION.
Checking: Turn off the Builder's rules checking (default→STRICT or LENIENT)
READ EXCEL ACTION (PROCESSES Branch only)
Queue:Set to TRUE to avoid running out of of free Excel processes

Ranges:

The format of a cell reference in Ranges: takes one of the following forms:

  • start_cell:end_cellA5:A25
  • start_cellA5

The first format should be used together with a selection of NORMAL RANGE in Types: while the second format should be used with the ROW or COLUMN selections in Types:

If ROW or COLUMN is selected in Types: data will be read along the row or column from the specified start cell in the Ranges: parameter until an empty cell is encountered.

NAMED RANGES

When specifying a NAMED RANGE in the Ranges: parameter, type in the name of the NAMED RANGE. EASAP Builder will auto-complete the parameter, allowing the Author a choice from a drop down list as shown below:

Multiple Ranges

More than one Cell Reference or NAMED RANGE can be specified in the Ranges: parameter by comma separating the individual cell reference or named range values. Therefore, a single READ EXCEL ACTION can populate the values of multiple explicit LIST's.

For every cell reference or named range specified in the Ranges: parameter, a corresponding value must be specified in each:

  • Sheets:
  • Types:
  • Lists:

All four parameters must contain the same number of entries.


Dates

Excel dates are handled as numeric data. These can be converted to a more suitable form using DATE CONVERSION on the DATA PROCESSING branch.


Page Tools