Excel Automation

Excel Automation helps you to input, read, manipulate, extract, and analyze data in Microsoft Excel.

The available activities enable you to create new excel workbook, a different type of charts, read information from cell, row, or column, open a specific excel workbook, run macros, write to a cell, extract data from a spreadsheet, and even delete cell or row. Automating Microsoft Excel task helps to reduce manual errors and eliminate repetitive and time-consuming task performed in an excel workbook.

You can use Excel Automation activities as an alternative to Excel macros, and the tasks managed by macros can be handled by an RPA bot.

Excel Automation Actions

You can use the following activities to design an RPA bot for Excel activities:

The Active Excel Sheet lets you activate or select a specific spreadsheet of an Excel workbook.

Properties

  • Name: Enter the display name of the action.
  • Instance Name: Enter the name of the Excel workbook instance which contains the spreadsheet you want to activate. It helps you to differentiate between multiple Excel workbook instances.
  • Enter Sheetname to Active: Enter the spreadsheet you want to activate or select.

Use this action to write a formula into a specific spreadsheet cell. Any value that exists in the specified cell location is also overwritten.

Properties

  • Name: Enter the display name of the action.
  • Instance Name: Enter the name of the workbook spreadsheet instance, which contains the cells in which you want to write the formula.
  • Enter Formula to Apply: Specify the formula that you want to be written to the cell.
  • Enter Cell Location: Excel cell that you want to write to.

You can use the Add button to add multiple cell locations and the formulas you want to add.

 

Use this action to close an opened Excel workbook instance. You can also choose the option to save the work performed in the Excel workbook before closing it.

Properties

  • Name: Enter the display name of the action.
  • Instance Name: Enter the name of the Excel workbook instance you want to create.
  • Workbook to save: Select the option to save the Excel workbook or not.

 

Create Chart lets you create a chart in an Excel spreadsheet based on chart type and range of the data available in the spreadsheet.

Properties

  • Name: Enter the display name of the action.
  • Instance Name: Specify the instance of excel which you want to use as a reference for the automation job.
  • Range: Specify the range you want to read to create the chart. Only strings are supported.
    • Range From: Minimum value of the range.
    • Range To: Maximum value of the range.
  • Chart Type: Select a chart type you want to create, such as Bar, Pie, Scatter, or Line.
If you are using Microsoft Office 2016 with this action, you might see that this action cannot create a few types of charts.

 

Create Dataset lets you convert an Excel spreadsheet into a dataset. You can use the dataset to loop over the cells of a spreadsheet.

When you are creating a dataset of an Excel spreadsheet containing numeric values, keep the first row of the spreadsheet as the column heading with string values.

Properties

  • Name: Enter the display name of the action.
  • Enter Dataset Name: Enter the name of the dataset you want to create.
  • Enter Workbook Filepath: Enter the name of the Excel workbook path which contains the sheet.
  • Enter Sheet Name: Enter the name of the sheet you want to convert into a dataset.
In the created excel dataset, check that there is no empty cell between columns or rows, that is all the columns and rows must be of the same size.

 

Create Sheet creates a specific spreadsheet in an Excel workbook. You add this action after you have created an Excel Workbook instance using the Create Workbook action.

Properties

  • Name: Enter the display name of the action.
  • Sheet Name: Enter the name of the spreadsheet you want to create in an Excel workbook instance.

 

Use this action to create an Excel workbook instance.

Properties

  • Name: Enter the display name of the action.
  • Instance Name: Enter the name of the Excel workbook instance you want to create.
  • File Path: Specify the name (including extension) and path of the location you want to save the excel workbook.

 

Use this action to remove a specified range in an Excel workbook.

Properties

  • Name: Enter the display name of the action.
  • Instance Name: Enter the name of the workbook instance where you want to make changes.
  • Range: Enter the range or cell you want to delete.
  • Shift Up: Specify if you want the surrounding rows and columns to the removed cells or range. If you select No, only the cell data is deleted, leaving the formatting unchanged.

 

Use this action to remove a specific row in an Excel workbook.

Properties

  • Name: Enter the display name of the action.
  • Instance Name: Enter the name of the workbook instance where you want to make changes.
  • Row Number: Specify the row you want to remove.
  • Shift Up: Select Yes, to delete the specified rows and shift remaining row one position up.
    • Select No, to only delete the specified rows without changing the formatting.

 

Use this action to read the value of an Excel cell and store it in a variable.

Properties

  • Name: Enter the display name of the action.
  • Instance Name: Enter the name of the Excel workbook spreadsheet in which the cell is located.
  • Cell Address: Specify the cell from which you want to extract the data.
  • Variable Name: Enter the name of the variable to store the information.

 

Find the last used row number of a specified column.

Properties

  • Name: Enter the display name of the action.
  • Instance Name: Specify the instance of excel which you want to use as reference for the automation job.
  • Column Letter: Enter the letter of the column for which you want to get the last row.
  • Variable Name: Enter the name of the variable to store the value.

 

Use this action to merge multiple excel workbooks.

Properties

  • Name: Enter the display name of the action.
  • Enter Filename of Merged Excel: Specify the filename of the new merged excel workbook.
  • Excel FilePaths: Specify the full path of the Excel workbooks you want to merge. You can use the +Add button to add multiple Excel workbooks full path.

 

Use this action to open an Excel workbook.

You can also use this action to open an Excel workbook stored in a specified directory.

Properties

  • Name: Enter the display name of the action.
  • Instance Name: Enter the name of the Excel workbook or spreadsheet you want to open.
  • Enter Workbook FilePath: Enter the full path of the Excel workbook.

 

Run a macro stored in an Excel workbook. The Excel workbook must be macro-enabled.

Properties

  • Name: Enter the display name of the action.
  • Instance Name: Specify the instance of excel which you want to use as reference for the automation job.
  • Enter Macro Name: Enter the name of the macro you want to run.

 

Saves an Excel you have created with the changes in your computer. You can also use this to save the changes in an opened workbook.

Properties

  • Name: Enter the display name of the action.
  • Instance Name: Specify the instance of excel which you want to use as reference for the automation job.
  • Choose Save/Save As: Based on your requirement, select one of the options:
    • Save: Save changes to the workbook. Uses the same properties used when you opened an existing workbook.
    • Save As: Save a workbook with changes to a new or different location on your computer.
      • Indicate Directory of the File: Enter the directory in your computer where you want to save the workbook.

 

Use this action to select range from an Excel sheet to perform further activities.

Properties

  • Name: Enter the display name of the action.
  • Instance Name: Specify the instance of excel which you want to use as reference for the automation job.
  • Range Selection: Select whether you want to select a range or not. Based on the selected option, do the following:
    • Range: Specify the range you want to select. Only strings are supported.
      • Range From: Minimum value of the range.
      • Range To: Maximum value of the range.
    • Cell Address: Enter the cell from which you want to select.

 

Use this action to write a value or text into a specific spreadsheet cell. If a value exists in the specified cell, it is overwritten.

Properties

  • Name: Enter the display name of the action.

  • Instance Name: Specify the instance of excel which you want to use as reference for the automation job.

  • Location: Specify the cell location you want to write to. For example, A1.

  • Text: Enter the value or text to set in the specified cell.

You can use the Add button to add multiple cell locations you want to write to.