EXCEL# returns the result of an EXCEL spreadsheet calculation in TeLiTab format

Syntax

EXCEL#(Mode%, SourceSheet$, TargetSheet$, TelitabInput$, InputScript$, OutputScript$, [InpVar])

Arguments

• Mode% controls the modus of EXCEL (0 if you don't want to see the sheet, 1 if you want the sheet to be visible).
• SourceSheet$ is the file name of the Excel sheet.
• If no path is included in the string for SourceSheet$, the file is expected to be in the applications directory of the knowledge base (see also Directory structure ).
• If "NullString" is provided as value for SourceSheet$, the sheet has to be embedded in the expression using the right mouse option "Include Binary in frame...".
• TargetSheet$ is the name under which the created Excel sheet will be saved.
• If no path is included in the string for TargetSheet$, the file is saved on the current Report/Project (..\My knowledge\Project\..) directory of this project (see also Directory structure ).
• If "NullString" is provided as value, the sheet will not be saved at all.
• TelitabInput$ is the dataset that can be used as input for the calculation (not required as long as all data required for InputScript$ is available).
• InputScript$ is the input script (in TeLiTab format) in which the positions in the spreadsheet are stated.
• OutputScript$ is the output script (in TeLiTab format) in which the positions are given of the data in the sheet to be presented as Telitab$ output of the parameter using the EXCEL#() function.
  Parameters in OutputScript$ should also be defined in InputScript$ with an empty spreadsheet position when you want to receive these values as input for Quaestor. This means that Quaestor will deal with these values provided as input in the solution, while these values where originally not provided but only given to in the spreadsheet. (see also examples).
• InpVar is an optional list of parameters or expessions. The list may contain one or more parameters required by the InputScript$ of the EXCEL#() function when these are not included in TelitabInput$. In such event, the current case values of the parameters presented in InputScript$ (and included in InpVar or computed earlier) are used as input for the EXCEL file.

Remarks

1. EXCEL#() can be used to get data from, sent data to and to process data with an Excel sheet.
2. Data can be send to Excel and retrieved from it in several ways. It will very much depend on the type of date, which type of data input and output is most useful. In all cases, the syntax of the expression does not change. However, the setup of the Excel sheets and syntax of the in- and output scripts WILL BE DIFFERENT. In the examples below this will be be explained.
3. If OutputScript$ = "NullString", EXCEL#() returns the name of the TargetSheet$ as result. If in this case TargetSheet$ = "NullString", EXCEL#() returns: 0
4. Please make sure that numbers taken from a worksheet are presented in decimal "." format (so 34.567 and not as 34,567). For Microsoft Windows, this can be adapted in Control Panel/Regional Settings. If you cannot change this due to other requirements, use the REPLINS$ function:

...

in which Telitab$ is the result of the EXCEL#() function.

1. The scripts (both input and output) for EXCEL#() follows the TeLiTab rules for defining objects/telitabs etc. So, when you want to write something to a sheet named "input" from an object (or TeLiTab) named "DATA" containing parameter "A" and "B" as part of a TeLiTab also containing parameter "C". you can write:

...

Let the text parameter TelitabOutput$ be defined by

TelitabOutput$ = EXCEL#(1, "Calc_sheet.xls", "Result_sheet.xls", TelitabInput#, TEXTITEM$(1),"NullString")

...

By using a comparable relation as example 1a, but now with an output script added a TeLiTab with Lpp, V_eco, Rt and PE is received as result:

TelitabOutput$ = EXCEL#(1, "Calc_sheet.xls", "Result_sheet.xls", TelitabInput$, TEXTITEM$(1), TEXTITEM$(2))

...

The syntax of the expression using the embedded workbook is almost the same as in the previous examples, except you don't have to address the source sheet anymore. The expression becomes, for instance:

TelitabOutput$ = EXCEL#(1, "", "", TelitabInput$, TEXTITEM$(1),TEXTITEM$(2))

...

Use as the syntax:

TelitabOutput$ = EXCEL#(1, "Calc_sheet.xls", "Result_sheet.xls", TelitabInput$, TEXTITEM$(1), "NullString")

...