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 SET

Write a Formula to 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 write any sort of Text or Formula or numbers into the given Range of Cells.

For this you can use P3 to specify the way how the Text is written into the Sheet.

The Command with the Parameter "9" can be used to set the Format (Numeric, General, Text etc.) of a Cell or a Range of Cells.

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 SET|P1|P2[|P3]

Parameter Explanation:

OFC.excel|Formula|$$RAN|$$FOR[| Paste Method]

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

P2 - Text, this text is written in the cells. Delimiter can be used (P3).

P3 - (opt./numeric) 0-8 Paste Method, technically the way how Excel should interpret the written 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" (Set Cell Property)

6 - use "Cells.FormulaLocal"

7 - use "Cells.FormulaR1C1"

8 - use "Cells.FormulaR1C1Local"

9 - use "Cells.Numberformat" -> This can be use to change the Cell-Format to Text or numeric etc.

Example:

' Excel FORMULA Example

OFC.excel|OPEN||0|1

OFC.excel|SHEET_Activate|2

FOR.$$LOP|0|8

OFC.excel|FORMULA|B$$LOP:B$$LOP|=+A1*2|$$LOP

NEX.

DMP.6

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..

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 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.