Built-in Excel Driver

The Robot workflow includes built-in Excel driver that helps you perform some operations on Excel spreadsheets.

To work with the built-in Excel driver in Robot, Microsoft Excel must be installed on the computer where the step is executed.

The built-in Excel driver is available for both the local automation device (using Microsoft Excel on the DesignStudio/RoboServer host) and remote automation devices (using Microsoft Excel installed on the remote device).

To create a new Excel spreadsheet, insert the Excel step and select Create File in the Action list.

To open an existing Excel spreadsheet, insert the Excel step and select Open File in the Action list and type the full path to the spreadsheet. For example, c:/documents/myspreadsheet.xlsx.

When you open a spreadsheet with several worksheets, you can navigate between them by clicking a button for the respective worksheet or use the Component and Application actions. Chart sheets do not contain elements that can be manipulated by a robot and are therefore not shown in the tree of the editor. The Chart sheets are included when the spreadsheet is saved.


Excel in desktop automation

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

Button

Description


Save in Excel

Save: Saves the changes in the spreadsheet.


Save As in Excel

Save As: Opens the Save As dialog box to save the current spreadsheet under a different name.


Copy in Excel

Mark to copy: Marks current selection for copy/paste action. For selection options see "Select cells" subsection below this table.


Paste from clipboard

Paste: Copies the previously marked selection to the selected cells. See "Clipboard operations" below for details.


Set text color

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


Sets background color

Set background color: Applies an active color to selected cells.


Color picker

Color Picker: Sets an active color to use for color changing actions.


Custom color in Hex format

Custom color input: Specifies custom color for the Color Picker in hexadecimal (hex) format. For example, pure white is ffffff, pure black is 000000.


Current cell value

This text field shows the address of the currently selected cell and its value.

Select cells
You can 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 Select entire worksheet.
  • 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 and from right to left.

    For example, to select a range that is five cells wide and three cells in height, do the following.

    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, first either 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 "Set text color" or "Set background color" actions.
Clipboard operations

The built-in Excel driver is designed to be used in robots running on RoboServer instances. Do not use Windows Clipboard in the built-in Excel driver, because the clipboard is shared between all running robots. To copy content within an Excel document, use the Mark to copy and Paste buttons on the toolbar or Copy/Paste application and component actions.

You can only copy and paste between sheets within the same Excel document. If you have more than one Excel documents opened, you cannot copy from one and paste into the other.

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

Freeze Tree operations
You can work with spreadsheets using Excel driver within the Freeze Tree group step. This helps you perform cell loops faster and create a more efficient robot. To use the Excel driver within the Freeze Tree step, insert the Freeze Tree step in your workflow and then insert the Open step that calls the Excel driver as described in this topic.

Application and component actions

The Application Action menu is available when you right-click the excel tab in the Recorder View. The Component Action menu is available when you right-click a component in the Recorder View or Tree View. The menu includes all items available on the built-in Excel toolbar with the following additional items. "AA" denotes application action menu and "CA" denotes 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 and CA

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

Scroll To

AA

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

Offset

AA

Moves the cursor 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. You can use this action to either extract formatted cells, raw content, or formula definitions from the cell. If a range with multiple cells is specified, data from cells is separated by tabs and rows in the result.

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.

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

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.

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

Get Sheet Name

AA

Extracts the name of the active worksheet.

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 to 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 cursor is also set to this cell. You can use this option to let other actions operate on the found cell using the selection scope.

The variable Result 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 cursor is also set to this cell. You can use this option to let other actions operate on the found cell using the selection scope.

The variable Result 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. You can 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.

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 spreadsheet in the document with the specified name. This action does not change the active spreadsheet.

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. Returns true if all cells meet the condition; returns false otherwise.

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 the changes in the spreadsheet.

Save As

AA

Opens the Save As dialog box to save the current spreadsheet under a different name.

Close

AA

Closes the Excel window discarding any unsaved changes.