Excel command

Use the Excel command to transfer data to and from Microsoft Excel spreadsheets.

Overview

The Excel command enables you to automate many of the repetitive tasks that involve spreadsheets. Some common scenarios include:

  • Copy data from one Excel spreadsheet to another.
  • Extract data from an application to an Excel spreadsheet.
  • Extract data from a website to an Excel spreadsheet.
  • Move data from an Excel spreadsheet to another application.
  • Transfer data from Microsoft Access to Excel.
  • Transfer Excel data to a website form.
  • Delete rows or columns in Excel.
  • Compare two columns or two cells in an Excel spreadsheet.
  • Sort data in Excel spreadsheets and delete duplicate rows.
Note: Automation for Excel is supported from Microsoft Office 2000 to Microsoft Office 2016. Excel automation can be used with or without macros and supports the following file format, .xls and .xlsx.
Important: When you use Microsoft Excel Version 2208 and a bot tries to open an unprotected macro, a security warning is displayed indicating that the macros are blocked as they are not from a trusted source.

See Macros from the internet will be blocked by default in Office for more details.

To learn more, search for the Automating Excel Using the Excel Command course in Automation Anywhere University: RPA Training and Certification (A-People login required).

Activate Sheet
Activates a specific spreadsheet in an open workbook. Specify whether to activate the Sheet by Index (numerical value) or Sheet by Name.
Close Spreadsheet
Closes a spreadsheet that was opened using the Open Spreadsheet operation. The Excel command identifies the spreadsheet using the same session name as the one used in the Open Spreadsheet operation.
Do not save changes: Select this check box to discard the spreadsheet.
Delete Cells
Deletes the values in an Active Cell or in a Specific Cell. The options include:
  • Shift cells left
  • Shift cells right
  • Entire column
  • Entire row
Find/Replace
Searches spreadsheet for cells that contain a matching value, and optionally replaces cell contents with the new value.
Customize this command:
  • Specify search parameters with the From and Till drop-down menus. The choices are Beginning of file, End of file, and Active cell.
    Note: Either the From field or the Till field can be the Active cell, but not both.
  • Specify whether to search By rows or By columns.
  • Select the check boxes to Match by case or to only Match entire cell contents.
  • Select the list variable of string data type that you want to use to store the output from the Assign cell addresses variable list.
Get Cells

Retrieves the value from a cell and stores it in a variable.

Customize this command:
  • Specify whether to retrieve a single cell, multiple cells, or all cells.
  • Select the Get Single Cell option to retrieve value from a single cell.
    • Specify whether to retrieve data from the Active Cell or Specific Cell. If Specific Cell is selected, specify the cell name to retrieve value from the Specific Cell.
    • Select the Get Cell Value check box to retrieve all the characters from the cell. If this check box is not selected, it retrieves up to 8221 characters. Also, the format of the data entered in the Specific Cell is not retained if the check box is selected.
      Note: This feature is available in the following versions: Version 11.3.3.3 and 11.3.3.3.x, and Version 11.3.5.1 and later. It is not available in Version 11.3.4.x and Version 11.3.5.
    • Select the variable from the drop-down menu to assign the output of the cell value.
  • Select the Get Multiple Cells option to retrieve value from multiple cells. Specify the cell range in the From Specific Cell and To Specific Cell fields.
  • Select the All Cells option to retrieve value from all the cells.
Go To Cell
Moves to a specific cell within a spreadsheet.
The movement options include:
  • Move one cell to the left or to the right of the active cell.
  • Move one cell above or below the active cell.
  • Move to the beginning or end of a row or column.
Note: End of the row/column refers to the row or column where the data ends.
Open Spreadsheet
Opens an Excel spreadsheet based on selected Spreadsheet Path.
Note: Do not use the same session name in the Open spreadsheet command more than one time within the same bot. This is applicable even if the open command is commented out.
Specific Sheet Name: Select this check box if working with multiple spreadsheets to specify the sheet name.
Contains Header: Select this check box to retrieve data from a spreadsheet that contains headings in the first row. You can enable this option to apply it to later operations in the session.
Load Add-ins: Select this check box if working with a spreadsheet in which add-ins are required.
Advanced View:
  • Spreadsheet Password: Select this check box to open a password protected spreadsheet. Specify the passwords to open and modify the spreadsheet.

    Press the F2 key to insert a Credential Variable for enhanced security. See Assigning credential variables from credential lockers.

  • Open Read-Only Recommended Spreadsheet: Select this check box to open a spreadsheet in Read Only Mode or Editable Mode.
Run Excel Macro
Runs a macro that is stored within an Excel spreadsheet. Enter the macro name and specify the parameters of the macro arguments, if any.
Save Spreadsheet
Saves a spreadsheet in an open workbook.
Set Cell
Sets the value of the active cell or a specific cell in an Excel spreadsheet. You can assign a variable to the cell value.

Contains Header

The Contains Header option enables you to retrieve data from the active spreadsheet that has its first row defined as a header.

In cases where the columns change position due to updates (for example, inserting new columns or deleting existing columns), the Task Bot or MetaBot Logic has to be updated to accommodate the change. Use the Contains Header option to enable the Task Bot or MetaBot Logic to automatically map repositioned columns of the target Excel spreadsheet.

Define the Contains Header option in the Open Spreadsheet operation for a particular session. Use the Excel Cell System Variable when configuring Contains Header.

During run time, the variable will indicate the column name and position of the cell from the header. For example, for a cell defined as A5, the variable will refer to Row A, Column 6.

Note: The string defined in the variable has to be an exact match. For instance, when retrieving data from each cell or specified cells under the heading Id, use the System Variable $Excel Cell(Id,2)$; variables such as $Excel Cell(id,2)$ or $Excel Cell(<space>Id<space>),2$ are considered invalid.

See Excel System Variables.

The Contain Header option is applicable to the following operations:
  • Delete Cell

    You cannot delete cells that are defined as header cells.

  • Find/Replace
    • You can Find a cell value using the Row/Column parameters.
    • You cannot Replace content in cells that are defined as header cells.
  • Get Cells

    For the Get Single Cell or Get Multiple Cells parameters, include the Excel Cell variable in the Specific Cell or From Specific Cell and To Specific Cell text boxes .

  • Go to Cell

    You can apply the Contains Header option to the Specific Cellparameter.

  • Set Cell
    • You can apply the Contains Header option to the Active Cell and Specific Cell parameters.
    • You cannot set the value of a cell that is defined as a header cell.