Use the Excel command to transfer data to and from Microsoft Excel spreadsheets.
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.
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
- 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.
- Specify search parameters with the From and Till drop-down menus. The choices are Beginning of file, End of file, and Active cell.
- 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 126.96.36.199 and 188.8.131.52.x, and Version 184.108.40.206 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:
Note: End of the row/column refers to the row or column where the data ends.
- 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.
- 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
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.
- Spreadsheet Password: Select this check box to open a password protected spreadsheet. Specify the passwords to open and modify the spreadsheet.
- 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.
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 TaskBot or MetaBot Logic has to be updated to accommodate the change. Use the Contains Header option to enable the TaskBot 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.
- Delete Cell
You cannot delete cells that are defined as header cells.
- 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.