Scripting with ARexx

CellBasics exposes an ARexx port, so scripts can drive it the same way you would by hand — open and save documents, move the selection around, read and write cell values, apply formatting and more. Commands are sent to the running application's ARexx port, CELLBASICS.1 (a second copy of the program running at the same time gets CELLBASICS.2).

Most commands act on the active document and its current selection: you typically SELECT a cell or range first, then run a command that reads or changes it. Query commands (the GET… family, VALUE, SELECTION, COLUMNS, and so on) return their answer in the ARexx RESULT variable.

A short example:

/* fill A1 and read it back */
ADDRESS 'CELLBASICS.1'
NEW
SELECT 'A1'
SETVALUE 'Hello'
BOLD ON
VALUE
SAY RESULT          /* => Hello */

In the reference below, arguments shown in [brackets] are optional.

Command reference

112 ARexx commands are available, grouped by area. Type in the box to filter by name or keyword.

Document (6)

NEW

NEW

clear the document

OPEN

OPEN path

load a document

APPEND

APPEND path

append the sheets of another document to the current one; sheets whose names are already taken are renamed and their references updated

SAVE

SAVE

save a document

SAVEAS

SAVEAS path

save a document to path

EXPORT

EXPORT path

write the document to path as .xlsx, .ods or .csv, chosen by the file's extension (.xlsx when it is none of these); the document keeps its own file. RESULT is empty on success, else the error

Clipboard & History (7)

UNDO

UNDO

undo the last operation

REDO

REDO

redo the last undone operation

COPY

COPY

copy the current selection to the clipboard

CUT

CUT

cut the current selection to the clipboard

COPYVALUES

COPYVALUES

copy the current selection to the clipboard as values only (formulas discarded)

PASTE

PASTE

paste the clipboard contents starting at the current selection

PASTEVALUES

PASTEVALUES

paste the clipboard contents as values only (formulas discarded) starting at the current selection

Selection & Values (6)

SELECT

SELECT cell or range

select a cell or range (A1, A1:A2, etc)

SELECTION

SELECTION

return current selection

VALUE

VALUE

return value(s) at current selection

NUMERICALVALUE

NUMERICALVALUE

return a number at current selection (discards formatting of dates, etc)

SETVALUE

SETVALUE value

enter value into selected cell(s)

RUNFORMULA

RUNFORMULA formula

run a given formula at currently selected cell, return the result

Sheets & Structure (54)

COLUMNS

COLUMNS

column count (active sheet)

ROWS

ROWS

row count (active sheet)

SHEET

SHEET

1-based index of active sheet

SHEETS

SHEETS

number of sheets in document

SHEETNAME

SHEETNAME

name of active sheet

SELECTSHEET

SELECTSHEET index

select sheet at index (1-based)

SETNAME

SETNAME name reference

define a workbook name, or change what one refers to. The reference is a range (Sheet1!$A$1:$B$2), a constant or a formula, without a leading =. Every formula that spells the name is read again and run. Undoable.

DELETENAME

DELETENAME name

remove a workbook name; formulas that spell it become #NAME?

GETNAME

GETNAME [name]

return what name refers to, or - when there is no such name. Without an argument it lists every name in the workbook, one per line, as "name = reference".

SETLINK target [tooltip]

put a link on the current selection. A target with a scheme (http://, mailto:, ...) is an external link; one without is a place in this workbook, e.g. C10 or 'Other Sheet'!B5. Keeps whatever comment the cells have.

SETCOMMENT

SETCOMMENT text [author]

put a comment on the current selection, keeping whatever link the cells have. Quote the text if it has spaces.

CLEARNOTE

CLEARNOTE

remove the link and the comment from every cell of the current selection

GETLINK

return the active cell's link target, or the place it points at in this workbook, or - when it has none

GETCOMMENT

GETCOMMENT [AUTHOR]

return the text of the active cell's comment, or with AUTHOR the name of its author, or - when it has none

HIDEROWS

HIDEROWS

hide the rows of the current selection; each one keeps the height it had, so UNDO and UNHIDE restore it

HIDECOLUMNS

HIDECOLUMNS

hide the columns of the current selection

UNHIDE

UNHIDE

unhide every hidden row and column in the current selection, or in the whole sheet when nothing is selected

SHEETCOLOR

SHEETCOLOR color

set the colour of the active sheet's tab, as a hexadecimal RRGGBB value (with or without a leading #) or NONE to clear it

GETSHEETCOLOR

GETSHEETCOLOR

return the active sheet's tab colour as a hexadecimal RRGGBB value, or - when it has none

SETVALIDATION

SETVALIDATION type [operator] [formula1] [formula2] [prompttitle] [prompt] [errortitle] [error] [errorstyle] [ALLOWBLANK] [NODROPDOWN]

give the current selection a data validation. type is Excel's own name: any, whole, decimal, list, date, time, textLength or custom; operator one of between, notBetween, equal, notEqual, greaterThan, lessThan, greaterThanOrEqual, lessThanOrEqual; errorstyle stop, warning or information. For a list, formula1 is either the items themselves (High,Medium,Low) or a range with a leading = (=$B$3:$B$5). A type of any with neither message clears the validation instead. Undoable.

CLEARVALIDATION

CLEARVALIDATION

take the data validation off the cells of the current selection; a validation covering cells outside it keeps them, so its range comes back split

GETVALIDATION

GETVALIDATION

return the active cell's data validation as "type=... formula1=... sqref=...", or - when it has none

SETRULE

SETRULE condition [value1] [value2] [FILL rrggbb] [TEXTCOLOR rrggbb] [BOLD] [ITALIC] [UNDERLINE] [STRIKE] [STOPIFTRUE] [INDEX n]

give the current selection a conditional formatting rule. condition is one of between, notBetween, equal, notEqual, greaterThan, lessThan, greaterThanOrEqual, lessThanOrEqual, contains, notContains, beginsWith, endsWith, blank, notBlank, error, noError or formula; value1 and value2 are the bounds a cell value is compared against, the text a text rule looks for, or the formula itself. A new rule goes in at the top of the priority order; with INDEX it replaces the nth rule of the selection's list instead. Undoable.

DELETERULE

DELETERULE index

delete the indexth conditional formatting rule of the current selection, counting the highest priority as 1

MOVERULE

MOVERULE index delta

move the indexth rule of the current selection delta places down the priority order; a negative delta moves it up

CLEARRULES

CLEARRULES

take every conditional formatting rule off the cells of the current selection; a format covering cells outside it keeps them, so its range comes back split

GETRULES

GETRULES

return the conditional formatting rules over the current selection, the highest priority first, one per | as "1 applies=A1:A5 fill=ff0000 stop rule=Cell value > 10", or - when there are none

PAGESETUP

PAGESETUP [PAPER name] [ORIENTATION portrait|landscape] [SCALE percent] [MODE manual|fitcolumns|fitwhole] [MARGINS l,t,r,b] [GRID on|off] [CENTERH on|off] [CENTERV on|off]

change the active sheet's print settings. Every keyword is optional and only what is named changes, so the repeated rows and columns of Print_Titles - which no keyword here edits - are left alone. Margins are in inches. Undoable, and it marks the document unsaved, which writing the print dialog's changes straight into the sheet never did.

GETPAGESETUP

GETPAGESETUP

return the active sheet's print settings as "paper=A4 orientation=portrait scale=100 mode=manual margins=... grid=off centerh=on centerv=off rows=1-1", or - when the sheet has none

FORMULA

FORMULA

return the active cell's formula as it is stored, or - when the cell holds no formula

FORMATASTABLE

FORMATASTABLE

make the selection an Excel table, its first row the header, styled TableStyleMedium2, as Table > Format as Table does

CONVERTTABLE

CONVERTTABLE

turn the Excel table the active cell is in back into a plain range, as Table > Convert Table to Range does

SETTABLE

SETTABLE [NAME name] [STYLE name] [HEADER on|off] [TOTALS on|off] [BANDEDROWS on|off] [BANDEDCOLUMNS on|off] [FIRSTCOLUMN on|off] [LASTCOLUMN on|off]

change the Excel table the active cell is in, as Table > Table Properties does. Every keyword is optional and only what is named changes, so a switch left out is not the same as one turned off. STYLE is any of Excel's 60 built-in names or one the file brought with it; a built-in is loaded into the workbook the first time it is used. Turning TOTALS on adds a row to the table with a Total label under its first column and a SUBTOTAL(109) under every other column whose data reads as numbers; turning it off takes the row out of the table and clears those cells. Either way the model and the cells are one undo step. Undoable.

INSERTPICTURE

INSERTPICTURE file [INCELL]

put a picture on the active sheet, anchored at the active cell and at the image's own size, moving and sizing with the cells, its proportions locked. Returns its index in the sheet's drawing order and leaves it selected, so the commands below need no INDEX. With INCELL it goes into the active cell instead, as Excel's Place in Cell, and nothing is returned. Undoable.

PLACEINCELL

PLACEINCELL [INDEX n]

Excel's Place in Cell: the picture becomes the content of the cell its top left corner is on, drawn fitted inside it with its proportions kept, and keeps its alternative text. Typing into the cell replaces it. Undoable as one step.

PLACEOVERCELLS

PLACEOVERCELLS

the other way: the active cell's picture floats again over that cell at the image's own size. Returns its index in the drawing order. Undoable as one step.

GETCELLPICTURE

GETCELLPICTURE

return the active cell's picture as "120x60 alt=Logo" - the image's own size, then its alternative text if it has any - or - when the cell holds none.

DELETEPICTURE

DELETEPICTURE [INDEX n]

take a picture off the active sheet: the nth in its drawing order, or the selected one without INDEX. Undo puts it back where it was in that order. Undoable.

MOVEPICTURE

MOVEPICTURE [CELL address] [OFFSET dx,dy] [SIZE w,h] [INDEX n]

move or resize a picture, as dragging it and its handles does. CELL puts its top left corner on that cell's, OFFSET then shifts it by pixels and SIZE gives it a new size in pixels. One undo unit for the whole call. Undoable.

SETPICTUREANCHOR

SETPICTUREANCHOR [BEHAVIOUR moveandsize|move|absolute] [ALTTEXT text] [INDEX n] [LOCKASPECT on|off]

how a picture follows the cells under it - Excel's twoCell, oneCell and absolute - the alternative text a reader who cannot see it gets, and whether a handle drag keeps its proportions (Shift does the opposite while dragging). Only what is named changes, and the picture stays where it is on screen whichever behaviour it is given. Undoable.

GETPICTURES

GETPICTURES

return the active sheet's pictures in drawing order, one per | as "0 cell=A1 pos=0,0 size=120x40 anchor=moveandsize lockaspect selected alt=Logo", or - when the sheet has none. cell and pos are where the picture actually sits - the cell its top left corner falls on, and that corner in pixels from A1 - rather than the anchor it stores, which an absolute picture keeps while the cells move under it.

GETROWHEIGHT

GETROWHEIGHT

return the height of the active cell's row in pixels; a hidden row is 0

GETCOLUMNWIDTH

GETCOLUMNWIDTH

return the width of the active cell's column in pixels; a hidden column is 0

SHOWGRID

SHOWGRID ON|OFF

show or hide the sheet's grid, as Table > Show Grid does. Undoable.

GETGRID

GETGRID

return ON when the sheet's grid is drawn, OFF when it is not

GETTABLE

GETTABLE

return the Excel table the active cell is in as "name=Parts range=A1:C4 style=TableStyleLight9 header totals bandedrows", the switches that are on listed after the style, or - when the cell is in none

INSERTROW

INSERTROW position

1-based position to insert a new row at

INSERTCOLUMN

INSERTCOLUMN position

1-based position to insert a new column at

DELETEROW

DELETEROW position

delete row at 1-based position index

DELETECOLUMN

DELETECOLUMN position

delete column at 1-based position index

INSERTCELLSDOWN

INSERTCELLSDOWN

insert cells over the current selection, shifting existing cells down within the selected columns

INSERTCELLSRIGHT

INSERTCELLSRIGHT

insert cells over the current selection, shifting existing cells right within the selected rows

DELETECELLSUP

DELETECELLSUP

delete cells in the current selection, pulling cells below up within the selected columns

DELETECELLSLEFT

DELETECELLSLEFT

delete cells in the current selection, pulling cells to the right left within the selected rows

Formatting (16)

SETFONT

SETFONT name

set the font of the current selection

SETFONTSIZE

SETFONTSIZE size

set the font size (in points) of the current selection

BOLD

BOLD state

set bold on the current selection (ON/OFF, also accepts 1/0/TRUE/YES)

ITALIC

ITALIC state

set italic on the current selection (ON/OFF, also accepts 1/0/TRUE/YES)

UNDERLINE

UNDERLINE state

set underline on the current selection (NONE, SINGLE or DOUBLE; defaults to SINGLE)

STRIKETHROUGH

STRIKETHROUGH state

set strikethrough on the current selection (NONE, SINGLE or DOUBLE; defaults to SINGLE)

TEXTCOLOR

TEXTCOLOR color

set the text color of the current selection, as a hexadecimal RRGGBB value (e.g. FF0000, optional leading #)

BGCOLOR

BGCOLOR color

set the background (fill) color of the current selection, as a hexadecimal RRGGBB value, or TRANSPARENT/NONE to clear it

ALIGN

ALIGN alignment

set the horizontal alignment of the current selection (LEFT, CENTER, RIGHT or JUSTIFY)

VALIGN

VALIGN alignment

set the vertical alignment of the current selection (TOP, CENTER or BOTTOM)

LINEWRAP

LINEWRAP state

set line wrapping on the current selection (ON/OFF, also accepts 1/0/TRUE/YES)

MERGE

MERGE

merge the current selection into a single cell

UNMERGE

UNMERGE

split any merged cells in the current selection

FRAME

FRAME edges [style] [color]

set the border (frame) style of the current selection. edges selects which borders to set: ALL, OUTER, INSIDE, INSIDEH, INSIDEV, TOP, BOTTOM, LEFT, RIGHT, or NONE to clear all borders. Only the named edges are changed, the rest are left untouched. style (optional) is the line style: THIN (default), THICK, MEDIUM, HAIR, DASHED, DOTTED, DOUBLE, DASHDOT, DASHDOTDOT, MEDIUMDASHED, MEDIUMDASHDOT, MEDIUMDASHDOTDOT or SLANTDASHDOT. color (optional) is a hexadecimal RRGGBB value (defaults to black)

INDENT

INDENT delta

step the indent of the current selection by delta levels (INDENT 1 to increase, INDENT -1 to decrease), clamped to Excel's 0 to 15

SETINDENT

SETINDENT level

set the indent of the current selection to level

Reading Formatting (14)

GETFONT

GETFONT

return the font name of the current selection (empty if it has multiple values)

GETFONTSIZE

GETFONTSIZE

return the font size of the current selection (empty if it has multiple values)

GETBOLD

GETBOLD

return ON or OFF for the current selection (empty if it has multiple values)

GETITALIC

GETITALIC

return ON or OFF for the current selection (empty if it has multiple values)

GETUNDERLINE

GETUNDERLINE

return NONE, SINGLE or DOUBLE for the current selection (empty if it has multiple values)

GETSTRIKETHROUGH

GETSTRIKETHROUGH

return NONE, SINGLE or DOUBLE for the current selection (empty if it has multiple values)

GETINDENT

GETINDENT

return the indent level of the active cell

GETCONDITIONAL

GETCONDITIONAL

return what the conditional formats paint over the active cell: fill=#RRGGBB, text=#RRGGBB, bar=fraction axis=fraction for a data bar and icon=shape/value for an icon set, or - when none of them match

GETTEXTCOLOR

GETTEXTCOLOR

return the text color of the active cell as a hexadecimal RRGGBB value

GETBGCOLOR

GETBGCOLOR

return the background (fill) color of the active cell as a hexadecimal RRGGBB value, or TRANSPARENT

GETALIGN

GETALIGN

return the horizontal alignment of the current selection (LEFT, CENTER, RIGHT, JUSTIFY or AUTOMATIC; empty if it has multiple values)

GETVALIGN

GETVALIGN

return the vertical alignment of the current selection (TOP, CENTER, BOTTOM or AUTOMATIC; empty if it has multiple values)

GETLINEWRAP

GETLINEWRAP

return ON or OFF for the current selection (empty if it has multiple values)

GETFRAME

GETFRAME [edge]

return the border (frame) style of the active cell. With an edge argument (TOP, BOTTOM, LEFT or RIGHT) it returns that edge's style; without one it returns all four edges as "TOP:style BOTTOM:style LEFT:style RIGHT:style". See FRAME for the list of style names.

Data Type & Number Format (8)

NUMBERFORMAT

NUMBERFORMAT code

set an Excel number format code on the current selection, e.g. 0.00%, #,##0.00;[Red]-#,##0.00 or yyyy-mm-dd. A code with spaces has to be quoted. The cell's type follows the code, so a date code makes a date of a number; General clears both. Undoable as one step.

GETNUMBERFORMAT

GETNUMBERFORMAT

return the number format code of the active cell, or General. Codes that CellBasics renders with a native formatter (plain 0.00, #,##0) read back as General.

SETTYPE

SETTYPE type

set the data type of the current selection: AUTOMATIC, WHOLENUMBER, NUMBER (or DECIMAL), TEXT, DATE, DATETIME, TIME, TIMEDELTA or CURRENCY. The appropriate default format is applied.

SETDATEFORMAT

SETDATEFORMAT format

set a date/time format string on the current selection (e.g. YYYY-MM-DD)

CHANGEDECIMALS

CHANGEDECIMALS delta

change the number of decimal places of the current selection by delta (e.g. 1 or -1)

SETDECIMALS

SETDECIMALS precision

set the number of decimal places of the current selection to precision

SETSEPARATORS

SETSEPARATORS state

toggle thousands separators on the current selection (ON/OFF, also accepts 1/0/TRUE/YES)

SETCURRENCY

SETCURRENCY symbol

set the currency symbol of the current selection

Data (1)

SORT

SORT column [column ...]

sort the rows of the current selection by one or more columns, primary key first. Each column is a column letter optionally followed by a sort direction, separated by a colon: ASC/ASCENDING/A (the default) or DESC/DESCENDING/D, e.g. A, B:DESC, C:ASC. For example, SORT A C:DESC B sorts ascending by column A, then descending by C, then ascending by B. Undoable as a single operation.

No commands match your search.