3. Script Language > Browser and Office (Acc.-Engine) > OFC. - Office-Commands > MS-Excel Scripting

OFC. - Office-Commands/MS-Excel Scripting

Previous Top Next

MiniRobotLanguage (MRL)

OFC.Excel|FORMULA GET

Read a Formula or any value from a Cell in an MS-Excel worksheet via the SPR

Intention

Generally you will need to OPEN a Excel-Instance first, this is done using the "OPEN" Subcommand.Also you need to activate the sheet first using:

' Activate by Number

OFC.excel|SHEET_Activate|1

The Formula Command will read any sort of Text or Formula or numbers from the given Range of Cells.

For this you can use P3 to specify the way how the Cell-Content is interpreted.

The Command will drop an "1" on the TOS if it was successful, otherwise a "0".

Syntax:

OFC.Excel|FORMULA|P1|P2[|P3]

OFC.Excel|FORMULA GET|P1|P2[|P3]

Parameter Explanation:

OFC.excel|Formula|$$RAN|$$RESUL-VAR[|numeric Paste Method]

P1 - RANGE, A1:A1 oder A1:B400 specifies the coordinates of the cells to be written to.

P2 - (opt.) Variable for result. If missing, invalid or it's "-" then TOS is used.

P3 - (opt./numeric) 0-8 Read Method, technically the way how Excel should interpret the Cell value/text

0 - use "Cells.Formula" (default)

1 - use "Cells.Value"

2 - use "Cells.Value2"

3 - use "Cells.FormualArray"

4 - use "Cells.FormulaLabel"

5 - use "Cells.FormulaHidden" (Get Cell Property)

6 - use "Cells.FormulaLocal"

7 - use "Cells.FormulaR1C1"

8 - use "Cells.FormulaR1C1Local"

9 - use "Cells.Numberformat" (Get Format of the specified Range)

Example:

' Excel FORMULA Example

OFC.excel|OPEN||0|1

DBP.---------------

' Activate by name

OFC.excel|SHEET_Activate|2

OFC.excel|Formula Set|A1:B3|25|1

OFC.excel|Formula Set|A2:C2|=+A1*2

OFC.excel|Formula Set|A3:C3|=+A1*2|6

OFC.excel|Formula Set|A4:C4|=+A1*2|8

OFC.excel|Formula Get|A2:A2|0|$$RES

OFC.excel|Formula Get|A2:A2|1|$$VAL

DBP.Formula at A2: $$RES

DBP.Value at A2: $$VAL

MBX.Click to Close

OFC.excel|CLOSE|1

END.

Remarks:

Some of the P3 Parameters may not make sense (Hidden?) there may be changes in the future.

We replace in german systems the "." with a "," in numbers that have been read.

RPL.$$RES|.|,

Limitations:

These commands need an valid Installation of MS-Office Excel. It will check if MS Excel is properly installed, and if not just do nothing.

The FORMULA GET Subcommand will only work with Excel-Instances that have previously been opened with the OPEN Subcommand of the same Robot-Instance.

If the command succeeds, it will drop an "1" on the TOS.