|
<< Click to Display Table of Contents >> Navigation: 3. Script Language > Browser and Office (Acc.-Engine) > OFC. - Office-Commands > MS-Excel Scripting > OFC. - Office-Commands |
MiniRobotLanguage (MRL)
OFC.Excel|GET_CELL_???_BY_COORD
Read 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
There are 3 versions og the Subcommand:
1. To read Simple numbers:
OFC.Excel|GET_CELL_NUM_BY_COORD|P1|P2[|P3]
2. To read Text
OFC.Excel|GET_CELL_TEXT_BY_COORD|P1|P2[|P3]
3. This will read the Cell as both Numbers and Text. And return the (longer) result that looks more valid.
OFC.Excel|GET_CELL_ANY_BY_COORD|P1|P2[|P3]
The GET_CELL ... BY_COORD Commands will read Text and Numbers from the Sheet. It may not read Formulas there.
Use the GET FORMULA-Command for that.
To specify which Cell to read, this Subcommand uses X- and Y-Coordinates, starting in the upper right Corner of the Sheet with (1,1).
If you prefer to use a RANGE like: "A1:A1" then use the GET_CELL_???_BY_RANGE Group of Commands.
The Command will drop an "1" on the TOS if it was successful, otherwise a "0".
Syntax:
OFC.Excel|GET_CELL_TEXT_BY_COORD|P1|P2[|P3]
OFC.Excel|GET_CELL_NUM_BY_COORD|P1|P2[|P3]
OFC.Excel|GET_CELL_ANY_BY_COORD|P1|P2[|P3]
Parameter Explanation:
P1 - X-Coordinate, starting with "1" in the Upper,left corner of the sheet, specifies the X-coordinates of the cells to be written to.
P2 - Y-Coordinate, starting with "1" in the Upper,left corner of the sheet, specifies the Y-coordinates of the cells to be written to.
P3 - (optional, Variable or "-") contains the variable that will receive the Cell-Content.
If a Cell has String Content, "GET_NUM" will return "0". If P3 is missing then the result will be placed on TOS.
If a Cell has numeric Content then "GET_TEXT" will not return a result. Use "GET_ANY" to have an internal check which one to use.
Example:
' Excel GET_CELL Example
OFC.excel|OPEN||0|1
DBP.---------------
' Activate by name
OFC.excel|SHEET_Activate|2
OFC.excel|write|A1:A1|54
OFC.excel|write|A2:A2|Hallo
DBP. Text
OFC.excel|GET_CELL_TEXT_BY_RANGE|A1:A1|$$REA
OFC.excel|GET_CELL_TEXT_BY_RANGE|A2:A2|$$REB
DBP.$$REA-$$REB
DBP. Num
OFC.excel|GET_CELL_NUM_BY_RANGE|A1:A1|$$REA
OFC.excel|GET_CELL_NUM_BY_RANGE|A2:A2|$$REB
DBP.$$REA-$$REB
DBP. ANY
OFC.excel|GET_CELL_ANY_BY_RANGE|A1:A1|$$REA
OFC.excel|GET_CELL_ANY_BY_RANGE|A2:A2|$$REB
DBP.$$REA-$$REB
DBP. Text
OFC.excel|GET_CELL_TEXT_BY_COORD|1|1|$$REA
OFC.excel|GET_CELL_TEXT_BY_COORD|1|2|$$REB
DBP.$$REA-$$REB
DBP.Num
OFC.excel|GET_CELL_NUM_BY_COORD|1|1|$$REA
OFC.excel|GET_CELL_NUM_BY_COORD|1|2|$$REB
DBP.$$REA-$$REB
DBP. ANY
OFC.excel|GET_CELL_ANY_BY_COORD|1|1|$$REA
OFC.excel|GET_CELL_ANY_BY_COORD|1|2|$$REB
DBP.$$REA-$$REB
MBX.$$REA-$$REB
DMP.6
MBX.Click to Close
OFC.excel|CLOSE|1
END.
Remarks:
To Close your Excel-Instance, use the CLOSE Subcommand.
However, its not necessary to close an Excel File, if you want to leave it open, even after the SPR exits.
Limitations:
The GET_CELL ... BY_COORD Commands will read Text and Numbers from the Sheet. It may not read Formulas there.
Use the GET FORMULA-Command for that.
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 GET_CELL ... BY_COORD 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.
See also: