When configuring a Range Finder to Find Specified Range
you need to write a piece of text that refers to a cell (or a range of cells).
Such references are also displayed (and can be entered) in the Cell Range View at the bottom of the Spreadsheet View.
The basic form is known from Excel formulas, but Kapow Katalyst provides some extensions.
The following examples show all the variants:
- This shows the core elements and how they are put together: The sheet name ("Sheet1"),
a separating exclamation mark, a column name ("B") and a row number ("3").
This example refers to a single cell of the named sheet.
- When the cell range reference is entered in the Cell Range View at the bottom of the Spreadsheet View,
the sheet name can be omitted if you want to refer to the currently displayed sheet. It will, however,
be added to the reference as soon as you press Enter. In a Range Finder you need to enter the
sheet name every time, as there is no notion of "currently displayed sheet" during execution
of the robot. For this reason the remaining examples will all include the sheet name.
- This shows how to refer to a range of cells from a single sheet: After the sheet name, you state
the upper left and lower right corners, separated by a colon.
(It is not possible to have a range that extends over several sheets.)
- This refers to all of column "C" of the stated sheet. Open ended ranges like this one are
not permitted in Excel, but are very useful in robots that must be able to adapt to
different sizes of Excel documents. When the robot works on a specific document, it will
automatically limit itself to the area of the document that actually contains data.
Open ended ranges will often be used in the Range Finder of a
Loop in Excel step.
- This refers to all of the columns C to H of the stated sheet and is an open ended range
as explained above.
- This shows that open ended ranges can also be rows. This example refers to all of row 14 of the stated sheet.
- This is a fixed range that refers to all of rows 14 up to and including 29 of the stated sheet.
- 'Total Sales'!B3
- If the sheet name contains white space or certain special characters, it must be enclosed in single
quotes. If the sheet name contains a single quote, it must be entered as two single quote characters.
The rules are just the same as when sheet names are included in cell references in Excel formulas.
- The Excel "document properties" (for example, the name of the author and the creation date of the document)
are made available in Kapow Katalyst in the form of a special sheet whose
name is empty. Thus this example refers to the value of one of the document properties (the name of
the property will be available in the neighboring cell "!A3"). This is an extension over Excel.