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

MS-Excel Scripting

Previous Top Next

MiniRobotLanguage (MRL)

OFC.Excel|Commands

Control MS-Excel worksheet via the SPR

Intention

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.

There is one exception that is the "EXIST" Command. It will always execute and return whether a "1" or a "0" (Excel is installed -> 1, Excel is not installe-> 0) on the TOS.

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

Hint:

1. Due to the nature of the underlying Excel-Scripting System it may not work to repeat tasks of opening and closing Excel hundreds of times. If such issues appear a reboot may be needed.

We have not tested this until now.

2. You may need to replace in german systems the "." with a "," in numbers that have been read. Because excel may read the numbers with ".".

For example "3.141"<>"3,131".

To replace it use:

RPL.$$RES|.|,

Syntax:

OFC.Excel|Subcommand and Data

Parameter Explanation:

Subcommands:

EXIST - Test if Excel is properly installed

OPEN - Open a existing Excel-File

CLOSE - Close an Excel-File that was opened using OPEN

SAVE - Save changes

SAVEAS - Save changes under another filename

SHEET_ACTIVATE - Activate another Sheep by Name/Number

SHEET_SETNAME - Set a new Name for a Sheep

SHEET_GETNAME - Get the name of a Sheet

FULLSCREEN - Make Excel Fullscreen

VISIBLE - Switch between visible/Invisible Mode

PAGE_UPDOWN - Scroll Page Up or down

SCROLL_UPDOWN - Scroll Up or down

SCROLL_LEFTRIGHT - Scroll Left or right

NUMBER_TO_LETTER - Convert a number to a letter

GET_CELL_???_BY_COORD - Get contents of a Cell by X and Y Coordinates

GET_CELL_???_BY_RANGE - Get contents of a Cell by Range "A1:A2".

FORMULA - Set a Cell Content

GET FORMULA - Get Formula from a Cell

LASTROW - Get the last used Row

LASTCOLUMN - Get the last used Column

EXCEL_GETTOPWIN - Get the Excel TopWindow-Handle

CALCULATE - Make Excel Calculate the sheet

SET_SIZE - Set Size of the Excel-Window

SET_POS - Set Position of the Excel-Window

' These SET Commands tell Excel how to load and save a Sheet

' for example using a Password or not.

SET UpdateLinks ON","SET UL ON"

SET UpdateLinks OFF","SET UL OFF"

SET FORMAT","SET FMT"

SET READONLY ON","SET RO ON"

SET READONLY OFF","SET RO OFF"

SET PASSWORD","SET PWD"

SET WRITE_PASSWORD","SET WPWD"

SET IGNORE_ROM ON","SET IROM ON"

SET IGNORE_ROM OFF","SET IROM OFF"

SET ORIGIN WINDOWS","SET ORI WIN"

SET ORIGIN MAC","SET ORI MAC"

SET ORIGIN DOS","SET ORI DOS"

SET DELIMITER","SET DELM"

SET EDITABLE ON","SET ED ON"

SET EDITABLE OFF","SET ED OFF"

SET NOTIFY ON","SET NF ON"

SET NOPTIFY OFF","SET NF OFF"

SET ADDTOMRU ON","SET MRU ON"

SET ADDTOMRU OFF","SET MRU OFF"

SET LOCAL ON","SET LOC ON"

SET LOCAL OFF","SET LOC OFF"

SET CORRUPTLOAD NORMAL","SET CL NORMAL"

SET CORRUPTLOAD REPAIR","SET CL REPAIR"

SET CORRUPTLOAD EXTRACT","SET CL EXTRACT"

Usage Examples:

OFC.Excel|SET CORRUPTLOAD REPAIR

OFC.Excel|SET WRITE_PASSWORD|4Z7ds74

OFC.Excel|SET PASSWORD|$$PWD

Example:

' EXCEL-Example

VAR.$$PFA=?path\Test3.xls

OFC.excel|OPEN|$$PFA|0|1

' Activate by name

OFC.excel|SHEET_Activate|2

'OFC.excel|SHEET_GetName|$$TXT|2

' Write a Block of cells

OFC.excel|FORMULA|A1:B10|2

' Write more Cells

FOR.$$LOP|1|12|6

CAL.$$NUM=$$LOP+4

OFC.excel|WRITE|B$$LOP:C$$NUM|=+A$$LOP*2,+A3*2,+A$$LOP*2,+A2*2|1

OFC.excel|FORMULA|E$$LOP:F$$NUM|=+A$$LOP*2|8

OFC.excel|Get Formula|E$$LOP|0|$$TRE

DBP.$$TRE

NEX.

' Max used Row/Column

OFC.EXCEL|LR|$$RES

OFC.EXCEL|LC|$$REC

MBX.Max used: $$RES-$$REC

' Max used Row/Column in Column 2

OFC.EXCEL|LR|$$RES|2

OFC.EXCEL|LC|$$REC|2

MBX.Max used: $$RES-$$REC

MBX.Click to Close

OFC.excel|CLOSE|1

END.

'-------------------------------------------------------

'SPR Script-file: Excel Benchmark

'Purpose:

'Author: TEOT\Theo

'Creation date: 05-11-2018 at 20:14:50

'===========================================================

'#EXE:?path\

'#SPI:ForceWrite

VAR.$$STA=#dtime#

VAR.$$SIZ=40

'VAR.$$PFA=?path\Test.xls

OFC.excel|OPEN||0|1

' Activate by name

OFC.Excel|EXCEL_GETTOPWIN|$$HWN

OFC.Excel|SET_POS|0|0

OFC.Excel|SET_SIZE|1024|740

OFC.excel|SHEET_Activate|1

OFC.excel|SET_SPEED|0

OFC.excel|SET ITERATION|1

VAR.$$STB=#dsince#

' Part 1 - Insert some numbers in A

FOR.$$LOP|1|$$SIZ

RND.1|100000|$$RND

CAL.$$RND=$$RND/100

RPL.$$RND|.|,

OFC.excel|WRITE|A$$LOP:A$$LOP|$$RND

NEX.

' Part 2

FOR.$$OUL|1|$$SIZ

OFC.Excel|NUMBER_TO_LETTER|$$OUL|$$XLE

CAL.$$XLA=$$OUL+1

OFC.Excel|NUMBER_TO_LETTER|$$XLA|$$XLD

FOR.$$LOP|1|$$SIZ

VAR.$$FOR==1/SUMME(A1:$$XLE$$SIZ)

IVV.$$LOP>1

CAL.$$LOA=$$LOP+$$OUL-1

VAR.$$FOR=$$FOR*$$XLA$$LOA+SUMME($$XLD1:$$XLD$$LOP)

EIF.

OFC.excel|FORMULA SET|$$XLD$$LOP|$$FOR|0

NEX.

VAR.$$STC=#dsince#

' Part III

FOR.$$LOP|1|($$SIZ/2)

FOR.$$LOC|1|$$SIZ

OFC.Excel|SCROLL_UPDOWN|$$LOP

PAU.0.01

OFC.Excel|SCROLL_LEFTRIGHT|$$LOC

PAU.0.01

OFC.Excel|SCROLL_UPDOWN|-$$LOP

PAU.0.01

OFC.Excel|SCROLL_LEFTRIGHT|-$$LOC

PAU.0.01

NEX.

VAR.$$STD=#dsince#

VAV.$$ERG=Settings:$$STB$crlf$Recalculation:$$STC$crlf$Scrolling: $$STD

MBX.$$ERG

OFC.Excel|CLOSE|1

ENR.

Remarks:

Microsoft, Excel and other used Trademarks are copyrighted by their respective owners. None of these Programs is part of the SPR-Distribution.

Limitations:

This command can only be used if MS Excel is properly installed on the System. We have tested with MS Excel 2016.