SPREADSHEET specifies the name of the Excel workbook (*.xls, *.xlsx, *.xlsm) file.
Avoid the following characters in an Excel filename.
. or <PERIOD> is allowed in the normal position between the filename prefix and the extension suffix (myfile.xls, myfile.xlsm, etc….)
'Two consecutive spaces' or a prohibited <PERIOD> character in an Excel spreadsheet name removes the ability to re-open an EASAP run from the Results folder, so should be avoided.
$ or <DOLLAR SIGN> should not be used in the name of an Excel sheet, Excel dependency calculations will fail.
A sheet name in an Excel spreadsheet must not end with a space.
Before an EASAP can be connected to a spreadsheet file, the file must first be uploaded into the EASAP. The file can then be selected using the File: parameter within the SPREADSHEET. In the EASAP Builder, a Save will generate a SHEET object for each sheet (not for EDC or Batch Only: TRUE).
The File: parameter must be unique across multiple SPREADSHEET's.
No named range in an Excel Client (or 'EDC') spreadsheet may have the prefix 'prop_' nor any combination of this sequence using captial letters, please rename any of these named ranges.
For an EDC spreadsheet a subset of all the spreadsheet's named ranges may be specified in Named Ranges List: or Named Ranges Prefixes: (but not both) to limit the number of values stored in a 'case record' (for an example see Use a subset of named ranges for a case record)
|File:||select spreadsheet *.xls file.|
|Global Dependencies:|| Enable a global dependency tree, setting this to TRUE will significantly lengthen save times for an EASAP in return for shorter run times (default: FALSE, TRUE)
If set to TRUE with a 'global' CELL REFRESH (ie. Objects: left blank), performance will be slower than either FALSE here or where specific CELL REFRESH > Objects: are specified.
|Desktop:||Enable an Excel Client interface to initialize, modify and store named ranges to a database (default: FALSE, TRUE)|
|Password:||Password to open password-protected spreadsheet files|
|Excel Server Type:||Restrict to an Administrator-defined EXCEL SERVER TYPE, if unset any EXCEL SERVER may be used.
For preloading create a uniquely named EXCEL SERVER TYPE and enter it in LOCAL EXCEL SERVER > Excel Server Types:
|Exclude Formulas:||Cells containing formulas will be ignored in dependency calculations.|
|Simplify Formulas:||Select types of Excel formulas to ignore to simplify dependency calculations, use when no referenced cells are found or referenced within the look up data.
We recommend to simplify OFFSET, VLOOKUP, HLOOKUP
|Exclude Sheets:||Select SHEET's to exclude from dependency calculations, only select worksheets that do not contain cells referenced in any Range Reference: or Named Range: parameters|
|On Parse Error:||When an Excel reference cannot be parsed set it will have ALL_CELL dependencies, in some cases an Author may wish to set NO_CELL here and enter dependent cells manually.|
|Batch Only:||Set the spreadsheet to be opened only on submission (default: FALSE, TRUE)|
|Disable Named Ranges:||Disable caching of named ranges from this spreadsheet when the EASAP is saved (default: FALSE, TRUE)|
|Calculate Dependencies:||Specify when to calculate dependencies (default: Never, If Unset, Force)|
|File Choice:||Select a FILE CHOICE to specify a spreadsheet to upload and run after the EASAP has already been started
available when Batch Only: TRUE
|when Desktop: TRUE|
|Excluded Sheets:||Exclude sheets based on one or more prefix strings listed here (ie. create a 'blacklist')|
|Included Sheets:||Include sheets based on one or more prefix strings listed here (ie. create a 'whitelist')|
|Named Ranges Prefixes:||Named ranges that match these prefixes will be stored and retrieved via an Excel Client
alternately explicitly specify named ranges in Named Ranges List: below
|Named Ranges List:||A LIST (instead of prefixes, above) of named ranges to be stored and retrieved via an Excel Client|
|Save Type:||Allow the workbook be saved as well as named range data, (default: Data Only, Data and Workbook)|
Dependencies between object in an EASAP may be calculated in advance to optimize EASAP interactions, choose from:
There are four ways to utilize an Excel spreadsheet from an EASAP.
For a SPREADSHEET: