NEW
NEW
clear the document
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.
112 ARexx commands are available, grouped by area. Type in the box to filter by name or keyword.
NEW
clear the document
OPEN path
load a document
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 a document
SAVEAS path
save a document to path
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
UNDO
undo the last operation
REDO
redo the last undone operation
COPY
copy the current selection to the clipboard
CUT
cut the current selection to the clipboard
COPYVALUES
copy the current selection to the clipboard as values only (formulas discarded)
PASTE
paste the clipboard contents starting at the current selection
PASTEVALUES
paste the clipboard contents as values only (formulas discarded) starting at the current selection
SELECT cell or range
select a cell or range (A1, A1:A2, etc)
SELECTION
return current selection
VALUE
return value(s) at current selection
NUMERICALVALUE
return a number at current selection (discards formatting of dates, etc)
SETVALUE value
enter value into selected cell(s)
RUNFORMULA formula
run a given formula at currently selected cell, return the result
COLUMNS
column count (active sheet)
ROWS
row count (active sheet)
SHEET
1-based index of active sheet
SHEETS
number of sheets in document
SHEETNAME
name of active sheet
SELECTSHEET index
select sheet at index (1-based)
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 name
remove a workbook name; formulas that spell it become #NAME?
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 text [author]
put a comment on the current selection, keeping whatever link the cells have. Quote the text if it has spaces.
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 [AUTHOR]
return the text of the active cell's comment, or with AUTHOR the name of its author, or - when it has none
HIDEROWS
hide the rows of the current selection; each one keeps the height it had, so UNDO and UNHIDE restore it
HIDECOLUMNS
hide the columns of the current selection
UNHIDE
unhide every hidden row and column in the current selection, or in the whole sheet when nothing is selected
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
return the active sheet's tab colour as a hexadecimal RRGGBB value, or - when it has none
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
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
return the active cell's data validation as "type=... formula1=... sqref=...", or - when it has none
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 index
delete the indexth conditional formatting rule of the current selection, counting the highest priority as 1
MOVERULE index delta
move the indexth rule of the current selection delta places down the priority order; a negative delta moves it up
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
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 [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
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
return the active cell's formula as it is stored, or - when the cell holds no formula
FORMATASTABLE
make the selection an Excel table, its first row the header, styled TableStyleMedium2, as Table > Format as Table does
CONVERTTABLE
turn the Excel table the active cell is in back into a plain range, as Table > Convert Table to Range does
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 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 [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
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
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 [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 [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 [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
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
return the height of the active cell's row in pixels; a hidden row is 0
GETCOLUMNWIDTH
return the width of the active cell's column in pixels; a hidden column is 0
SHOWGRID ON|OFF
show or hide the sheet's grid, as Table > Show Grid does. Undoable.
GETGRID
return ON when the sheet's grid is drawn, OFF when it is not
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 position
1-based position to insert a new row at
INSERTCOLUMN position
1-based position to insert a new column at
DELETEROW position
delete row at 1-based position index
DELETECOLUMN position
delete column at 1-based position index
INSERTCELLSDOWN
insert cells over the current selection, shifting existing cells down within the selected columns
INSERTCELLSRIGHT
insert cells over the current selection, shifting existing cells right within the selected rows
DELETECELLSUP
delete cells in the current selection, pulling cells below up within the selected columns
DELETECELLSLEFT
delete cells in the current selection, pulling cells to the right left within the selected rows
SETFONT name
set the font of the current selection
SETFONTSIZE size
set the font size (in points) of the current selection
BOLD state
set bold on the current selection (ON/OFF, also accepts 1/0/TRUE/YES)
ITALIC state
set italic on the current selection (ON/OFF, also accepts 1/0/TRUE/YES)
UNDERLINE state
set underline on the current selection (NONE, SINGLE or DOUBLE; defaults to SINGLE)
STRIKETHROUGH state
set strikethrough on the current selection (NONE, SINGLE or DOUBLE; defaults to SINGLE)
TEXTCOLOR color
set the text color of the current selection, as a hexadecimal RRGGBB value (e.g. FF0000, optional leading #)
BGCOLOR color
set the background (fill) color of the current selection, as a hexadecimal RRGGBB value, or TRANSPARENT/NONE to clear it
ALIGN alignment
set the horizontal alignment of the current selection (LEFT, CENTER, RIGHT or JUSTIFY)
VALIGN alignment
set the vertical alignment of the current selection (TOP, CENTER or BOTTOM)
LINEWRAP state
set line wrapping on the current selection (ON/OFF, also accepts 1/0/TRUE/YES)
MERGE
merge the current selection into a single cell
UNMERGE
split any merged cells in the current selection
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 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 level
set the indent of the current selection to level
GETFONT
return the font name of the current selection (empty if it has multiple values)
GETFONTSIZE
return the font size of the current selection (empty if it has multiple values)
GETBOLD
return ON or OFF for the current selection (empty if it has multiple values)
GETITALIC
return ON or OFF for the current selection (empty if it has multiple values)
GETUNDERLINE
return NONE, SINGLE or DOUBLE for the current selection (empty if it has multiple values)
GETSTRIKETHROUGH
return NONE, SINGLE or DOUBLE for the current selection (empty if it has multiple values)
GETINDENT
return the indent level of the active cell
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
return the text color of the active cell as a hexadecimal RRGGBB value
GETBGCOLOR
return the background (fill) color of the active cell as a hexadecimal RRGGBB value, or TRANSPARENT
GETALIGN
return the horizontal alignment of the current selection (LEFT, CENTER, RIGHT, JUSTIFY or AUTOMATIC; empty if it has multiple values)
GETVALIGN
return the vertical alignment of the current selection (TOP, CENTER, BOTTOM or AUTOMATIC; empty if it has multiple values)
GETLINEWRAP
return ON or OFF for the current selection (empty if it has multiple values)
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.
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
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 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 format
set a date/time format string on the current selection (e.g. YYYY-MM-DD)
CHANGEDECIMALS delta
change the number of decimal places of the current selection by delta (e.g. 1 or -1)
SETDECIMALS precision
set the number of decimal places of the current selection to precision
SETSEPARATORS state
toggle thousands separators on the current selection (ON/OFF, also accepts 1/0/TRUE/YES)
SETCURRENCY symbol
set the currency symbol of the current selection
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.