Use Excel with Robots

The Kofax RPA Robot workflow includes a built-in Excel driver for performing operations on Excel workbooks. Microsoft Excel must be installed on the computer where the operation is executed.

The built-in Excel driver is available for automation devices as follows:

On remote Desktop Automation Service devices using Microsoft Excel installed on the remote device.
On local automation devices using Microsoft Excel on a Design Studio and RoboServer host.

If the local option is selected, both the Design Studio and RoboServer host must have Microsoft Excel installed to support this option.

Create and open a workbook

To create a new Excel workbook, insert the Excel step and select Create File in the Action list.
To open an Excel workbook:
1. If running in the standalone execution mode, click Prepare Execution.
2. Insert the Excel step.
3. Select Open File in the Action list.
4. Select the source (direct access, RFS, or variable).
5. For direct access or RFS source, type the full path to the workbook.
  
  For example: C:/documents/myworkbook.xlsx
6. For a variable source, select a variable with the binary type that contains the data.

Navigate in a workbook

When you open a workbook that contains worksheets, navigate between them by clicking a button for the respective worksheet or using the Component Action and Application Action menus.

Toolbar buttons

Use toolbar buttons in the built-in Excel driver to perform the following operations.

Button	Action	Description
	Save	For direct access and RFS sources, saves the changes in the workbook: If previously saved, saves to the location it was last saved. If read from a file, saves to the same location. If new, opens the Save As dialog box. To save workbooks, use either the Save or the Save As button.
	Save As	Saves the workbook to the file system or to an RFS location. After Save As, subsequent Save actions save to the same location.
	Mark to copy	Marks current selection for copy/paste action. For selection options see Select cells.
	Paste	Copies the previously marked selection to the selected cells. See Clipboard operations.
	Set text color	Applies an active color to the text in the selected cell.
	Set background color	Applies an active color to selected cells.
	Color Picker	Sets an active color to use for color changing actions.
	Custom color input	Specifies custom color for the Color Picker in hexadecimal (hex) format. For example, pure white is ffffff, and pure black is 000000.
		This text field shows the address of the currently selected cell and its value.

Select cells

Select one or more cells in the worksheet as follows.

To select one cell in the worksheet, click it just as you would do in a desktop version of Microsoft Excel.
To select a row, click the row heading.
To select a column, click the column heading.
To select the entire worksheet, click Select All .
To select a range of cells in a worksheet, use arrows with Shift in the Press Key.

Note that selection is made from left to right and from top to bottom. You cannot select cells by moving your selection up from the bottom or from right to left.

For example, to select a range that is five cells wide and three cells in height, perform the following steps.
1. Click a cell located at the top left corner of your range.
2. Insert the Press Key step. In the step, specify excel as the application name in the finder, select the Right Arrow in the Standard Keys, select Shift as the key modifier, and enter 4 in the Count field.
3. Execute the step.
4. Insert the Press Key step. In the step, specify excel as the application name in the finder, select the Down Arrow in the Standard Keys, select Shift as the key modifier, and enter 2 in the Count field.
5. Execute the step.
As a result, you should see the selected range of cells in your worksheet.

Some key presses configured for the Press Key option may not function due to technical limitations.

Change color

To use color changing actions, select a color in the Color Picker or specify a custom color in the Custom color input.
To specify a custom color, click the text area in the Custom color input, enter the required color in hex format, and click Set.
Once the active color is set, use the "Set text color" or "Set background color" actions.

Clipboard operations

The built-in Excel driver is designed for Robots running on RoboServer instances. Do not use the Windows Clipboard in the built-in Excel driver, because the clipboard is shared between all running robots.

You can only copy and paste between sheets within the same Excel workbook. If you have more than one Excel workbook open, you cannot copy from one and paste into another.

If you cut or copy information from an Excel workbook, the information is not copied to the clipboard and the "Extract clipboard" step does not provide the content you cut or copied from an Excel sheet.

To copy content within an Excel workbook, use the "Mark to copy" and "Paste" buttons on the toolbar or the Copy and Paste Application Actions and Component Actions.

Freeze tree operations

These operations perform cell loops faster and create more efficient robots.

Access workbooks using Excel driver within the Freeze Tree group step.
To use the Excel driver within the Freeze Tree step, insert the Freeze Tree step in your workflow, then insert the Open step that calls the Excel driver.

Action menus

To access the Application Action menu, right-click the excel tab in the Recorder View.
To access the Component Action menu, right-click a component in the Recorder View or Tree View.

The menu includes all items available on the built-in Excel toolbar plus the following menu actions.

"AA" denotes the Application Action menu.
"CA" denotes the Component Action menu.

Action	Description
Select AA and CA	Selects a cell in the current worksheet. Specify the cell address in the Select property of the Select step. For example, $b$4 selects the fourth cell in column b. To select a range of cells, use the Expand From Cursor option to expand the active selection into a rectangular, which includes both the currently selected cells and a new range or use A1:B2 notation.
Select Sheet AA	Opens the specified worksheet. Enter the worksheet name in the Select Sheet property of the Select Sheet step.
Scroll To AA and CA	Scrolls to and makes the selected cell visible on the screen. For AA, scrolls to the location of the cell you enter. For CA, scrolls to where the location is the result of the component finder.
Offset AA	Moves the pointer or selection by the indicated number of rows and columns.
Get Value AA and CA	Extracts the content of one or more cells into a text variable. Set Extract This to extract either formatted cells, raw content, or formula definitions from the cell. If a Range with multiple cells is specified, data from the cells is separated by tabs and rows in the result. Use Target to specify the scope of the search: Cursor, Selection, or Custom. The variable in Results contains the value of the selected cells.
Get Number Value AA and CA	Extracts the content of a cell into a Number variable. This action extracts binary data and is not affected by locale settings. Use Target to specify the scope of cells: Custom or Cursor. If the content of the cell cannot be converted into a number, or it contains an Excel #error value, select and specify to return Error Value. Otherwise, 0.0 is returned. The Results field contains the Number value of the extracted cells.
Get Hyperlink AA and CA	Extracts the URL of the hyperlink in the specified cell or an empty value if the cell does not contain a hyperlink. The Results field contains a variable of type Text containing the extracted URL. Hyperlinks can be associated with a range that contains multiple cells; this action works on any cell in the range.
Get Sheet Name AA	Extracts the name of the active worksheet. The Results field contains a variable of type Text with the extracted name of the Excel sheet.
Copy AA and CA	Copies the current selection.
Paste AA and CA	Pastes the previously copied selection to the selected cell.
Clear AA and CA	Clears a range of cells. Use this action to clear formatting, content, or both.
Find AA	Starts a new search in the specified scope. Find What: Enter the text to search for. Match Case: Select to perform a case-sensitive search. Match Entire Cell: Select to match the searched text for the entire content of cell. Search By: Select to search by rows or columns. Look In: Select to search through formulas, results, or comments. Start After: Specifies a cell after which the search should start. Target and Range: Specify the scope of the search. Select Result: Selects the found cell. If this option is enabled and the cell is present in the device tree, the pointer is also set to this cell. Use this option to let other actions operate on the found cell using the selection scope. The variable Results contains the location of the found cell. If the action fails, the variable contains an empty string.
Find Next AA	Continues the search action started previously. If you select Select Result, the found cell is selected. If this option is enabled and the cell is present in the device tree, the pointer is also set to this cell. Use this option to let other actions operate on the found cell using the selection scope. The variable Results contains the location of the found cell. If the action fails, the variable contains an empty string.
Set Value AA and CA	Sets a value in a range of cells and optionally applies formatting to the cells. Use this action to set data or formulas. If you select Custom in Format, the formatting is set to the value of the Custom Format field.
Set Number Value AA and CA	Sets a Number value in a range of cells and optionally applies formatting to the cells. The copied value is binary and not affected by locale settings. If you select Custom in Format, the formatting is set to the value of the Custom Format field.
Set Formatting AA and CA	Sets the formatting for a range of cells without changing the content of the cells. If you select Custom in Format, the formatting is set to the value of the Custom Format field. For Custom Format localization requirements, see Errors using Excel with Robots.
Set Hyperlink AA and CA	Assigns a hyperlink to a range of cells. The link is visually presented in the first cell of the range, but applied to all cells in the range.
Set Sheet Name AA	Changes the name of the active worksheet.
Set Row Height AA and CA	Sets the height of all rows containing cells in the range to a specific height or select to autofit the cells based on content. Note that in Excel the actual height of columns is an approximation of the provided value.
Set Column Width AA and CA	Sets the width of all columns containing cells in the range to a specific width or select to autofit the cells based on content. Note that in Excel the actual width of columns is an approximation of the provided value.
Format As Table AA	Converts the specified range to a table. This action uses the requested table style and applies selected table style options. Using the Guess option for the Table has Headers option instructs Excel to use its heuristics to determine if the table has headers. Selected table style options might not be visible if the table style does not provide visual clues. The options in this step are the same as in the Table Tools tab on the ribbon in Excel and use the same default settings. For more information, refer to the Microsoft Excel documentation.
Insert Sheet AA	Inserts a new worksheet in the workbook with the specified name. This action does not change the active workbook.
Insert Rows AA and CA	Inserts one or more rows relative to the first cell in the range.
Insert Columns AA and CA	Inserts one or more columns relative to the first cell in the range.
Delete Sheet AA	Deletes the active worksheet. It is not possible to delete the last worksheet in the workbook.
Delete Rows AA and CA	Deletes all rows containing cells in the range.
Delete Columns AA and CA	Deletes all columns containing cells in the range.
Add Comment AA and CA	Assigns a comment to the first cell of the range. This comment replaces an existing comment.
Delete Comments AA and CA	Removes all comments in the range.
Test Cell Type AA and CA	Performs a test on all cells in the range. The Robot can test a cell or multiple cells to verify whether they are blank, contain text, number, logical functions, errors, or formulas. The Results variable returns true if the cells meet the condition; returns false otherwise. The variable should be Boolean. The tests use the equivalent of Excel functions.
Set Named Range AA and CA	Adds a named range to the workbook or worksheet that references the range. Clear the Visible option to hide the range in Excel’s user interface after the workbook is saved.
Select Color AA	Selects one of the available colors to use for color changing actions.
Select Custom Color AA	Selects a color specified in the RGB hex format.
Set Text Color AA and CA	Applies an active color to the text in the selected cell.
Set Background Color AA and CA	Applies an active color to the selected cell.
Save AA	Saves a new workbook and changes to a workbook: If previously saved, saves to the location it was last saved. If read from a file, saves to the same location. If new or read from a variable, the action displays an error because the system does not know where to save the workbook. A new workbook does not have a file name yet, so it must be saved using the Save As dialog box. Any source read from a variable must be saved using the Save To action. To save workbooks to a variable, use the Save To Application Action. To save workbooks to a different location, use the Save As Application Action.
Save As AA	Saves the workbook to the file system or an RFS location.
Save To AA	Saves the workbook to a variable, which must be a binary type. A subsequent Save of the workbook fails. Use Save As or Save To.
Close AA	Closes the Excel window discarding any unsaved changes.

Action

Description

Select

AA and CA

Selects a cell in the current worksheet.

Specify the cell address in the Select property of the Select step.

For example, $b$4 selects the fourth cell in column b.

To select a range of cells, use the Expand From Cursor option to expand the active selection into a rectangular, which includes both the currently selected cells and a new range or use A1:B2 notation.

Select Sheet

Opens the specified worksheet.

Enter the worksheet name in the Select Sheet property of the Select Sheet step.

Scroll To

AA and CA

Scrolls to and makes the selected cell visible on the screen.

For AA, scrolls to the location of the cell you enter.
For CA, scrolls to where the location is the result of the component finder.

Offset

Moves the pointer or selection by the indicated number of rows and columns.

Get Value

AA and CA

Extracts the content of one or more cells into a text variable.

Set Extract This to extract either formatted cells, raw content, or formula definitions from the cell.

If a Range with multiple cells is specified, data from the cells is separated by tabs and rows in the result.

Use Target to specify the scope of the search: Cursor, Selection, or Custom.

The variable in Results contains the value of the selected cells.

Get Number Value

AA and CA

Extracts the content of a cell into a Number variable. This action extracts binary data and is not affected by locale settings.

Use Target to specify the scope of cells: Custom or Cursor.

If the content of the cell cannot be converted into a number, or it contains an Excel #error value, select and specify to return Error Value. Otherwise, 0.0 is returned.

The Results field contains the Number value of the extracted cells.

Get Hyperlink

AA and CA

Extracts the URL of the hyperlink in the specified cell or an empty value if the cell does not contain a hyperlink.

The Results field contains a variable of type Text containing the extracted URL.

Hyperlinks can be associated with a range that contains multiple cells; this action works on any cell in the range.

Get Sheet Name

Extracts the name of the active worksheet.

The Results field contains a variable of type Text with the extracted name of the Excel sheet.

Copy

AA and CA

Copies the current selection.

Paste

AA and CA

Pastes the previously copied selection to the selected cell.

Clear

AA and CA

Clears a range of cells. Use this action to clear formatting, content, or both.

Find

Starts a new search in the specified scope.

Find What: Enter the text to search for.
Match Case: Select to perform a case-sensitive search.
Match Entire Cell: Select to match the searched text for the entire content of cell.
Search By: Select to search by rows or columns.
Look In: Select to search through formulas, results, or comments.
Start After: Specifies a cell after which the search should start.
Target and Range: Specify the scope of the search.
Select Result: Selects the found cell. If this option is enabled and the cell is present in the device tree, the pointer is also set to this cell. Use this option to let other actions operate on the found cell using the selection scope.

The variable Results contains the location of the found cell. If the action fails, the variable contains an empty string.

Find Next

Continues the search action started previously.

If you select Select Result, the found cell is selected. If this option is enabled and the cell is present in the device tree, the pointer is also set to this cell. Use this option to let other actions operate on the found cell using the selection scope.

The variable Results contains the location of the found cell. If the action fails, the variable contains an empty string.

Set Value

AA and CA

Sets a value in a range of cells and optionally applies formatting to the cells. Use this action to set data or formulas.

If you select Custom in Format, the formatting is set to the value of the Custom Format field.

Set Number Value

AA and CA

Sets a Number value in a range of cells and optionally applies formatting to the cells. The copied value is binary and not affected by locale settings.

If you select Custom in Format, the formatting is set to the value of the Custom Format field.

Set Formatting

AA and CA

Sets the formatting for a range of cells without changing the content of the cells.

If you select Custom in Format, the formatting is set to the value of the Custom Format field.

For Custom Format localization requirements, see Errors using Excel with Robots.

Set Hyperlink

AA and CA

Assigns a hyperlink to a range of cells. The link is visually presented in the first cell of the range, but applied to all cells in the range.

Set Sheet Name

Changes the name of the active worksheet.

Set Row Height

AA and CA

Sets the height of all rows containing cells in the range to a specific height or select to autofit the cells based on content.

Note that in Excel the actual height of columns is an approximation of the provided value.

Set Column Width

AA and CA

Sets the width of all columns containing cells in the range to a specific width or select to autofit the cells based on content.

Note that in Excel the actual width of columns is an approximation of the provided value.

Format As Table

Converts the specified range to a table. This action uses the requested table style and applies selected table style options.

Using the Guess option for the Table has Headers option instructs Excel to use its heuristics to determine if the table has headers.

Selected table style options might not be visible if the table style does not provide visual clues.

The options in this step are the same as in the Table Tools tab on the ribbon in Excel and use the same default settings. For more information, refer to the Microsoft Excel documentation.

Insert Sheet

Inserts a new worksheet in the workbook with the specified name. This action does not change the active workbook.

Insert Rows

AA and CA

Inserts one or more rows relative to the first cell in the range.

Insert Columns

AA and CA

Inserts one or more columns relative to the first cell in the range.

Delete Sheet

Deletes the active worksheet. It is not possible to delete the last worksheet in the workbook.

Delete Rows

AA and CA

Deletes all rows containing cells in the range.

Delete Columns

AA and CA

Deletes all columns containing cells in the range.

Add Comment

AA and CA

Assigns a comment to the first cell of the range. This comment replaces an existing comment.

Delete Comments

AA and CA

Removes all comments in the range.

Test Cell Type

AA and CA

Performs a test on all cells in the range.

The Robot can test a cell or multiple cells to verify whether they are blank, contain text, number, logical functions, errors, or formulas.

The Results variable returns true if the cells meet the condition; returns false otherwise. The variable should be Boolean.

The tests use the equivalent of Excel functions.

Set Named Range

AA and CA

Adds a named range to the workbook or worksheet that references the range.

Clear the Visible option to hide the range in Excel’s user interface after the workbook is saved.

Select Color

Selects one of the available colors to use for color changing actions.

Select Custom Color

Selects a color specified in the RGB hex format.

Set Text Color

AA and CA

Applies an active color to the text in the selected cell.

Set Background Color

AA and CA

Applies an active color to the selected cell.

Save

Saves a new workbook and changes to a workbook:

If previously saved, saves to the location it was last saved.
If read from a file, saves to the same location.
If new or read from a variable, the action displays an error because the system does not know where to save the workbook.

A new workbook does not have a file name yet, so it must be saved using the Save As dialog box. Any source read from a variable must be saved using the Save To action.

To save workbooks to a variable, use the Save To Application Action.

To save workbooks to a different location, use the Save As Application Action.

Save As

Saves the workbook to the file system or an RFS location.

Save To

Saves the workbook to a variable, which must be a binary type.

A subsequent Save of the workbook fails. Use Save As or Save To.

Closes the Excel window discarding any unsaved changes.

Chart sheets

Because chart sheets do not contain elements that can be manipulated by a robot, they are not shown in the tree of the editor. The chart sheets are included after a workbook is saved.