READ EXCEL ACTION reads data from cells or named ranges as specified in Ranges: for an Excel spreadsheet by referencing existing SHEETs or specifying an Excel 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 LISTs as specified in Lists:
Ranges: is used in conjunction with Types: 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|
|Sheets:||Select one or more SHEETs to use as data source(s)|
|Ranges:||Enter a comma-separated list of named or cell ranges to be read
(eg. start_cell:end_cell or single cell, B6:G6 or A4, respectively)
|Types:||Select type of read for each range specified (COLUMN, NORMAL RANGE, ROW)|
|Lists:||Enter a comma-separated list of new LIST names to store data read from spreadsheets|
|Delimiter:||Set a character to delimit object references in Ranges: (Default: % )|
|Do if:||Logical expression if true then action is performed and not otherwise (Default: true)|
|Replace Nulls:||Set a value to replace any null values returned by EXCEL READ ACTION|
|Checking:||Turn off the EASAP 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 (Default: TRUE)|
|On Error:||Set to STOP to halt any subsequent PROCESS or ACTION if there is an error (Default: CONTINUE)|
The format of a cell reference in Ranges: takes one of the following forms:
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.
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:
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 may populate the values of multiple LISTs.
For every cell reference or named range specified in Ranges: a corresponding value must be specified in:
All four parameters must contain the same number of entries.
Excel dates are handled as numeric data, convert one via: DATA PROCESSING > SCALAR > DATE CONVERSION