MS Excel actions
Excel automation in Zoho RPA lets you optimize and automate a wide range of data-related tasks, such as data extraction, data transformation, data reporting, data-driven decision-making, and data filtering. This category of RPA actions will help support your automation efforts, regardless of your goal. Whether you need to extract data from multiple sources, transform data into a usable format, generate reports, make data-driven decisions, or simply filter data, Excel automation can help.
Excel is a widely used and familiar tool, making it ideal for RPA automation. This set of actions will help you to reduce manual effort and improve the efficiency of your data-related tasks.
Supported RPA Agent Platforms : WindowsExamples :
- Extract data from multiple sources, such as websites, databases, and CRM systems, and consolidate it into a single Excel spreadsheet.
- Transform data into a usable format, such as cleaning data, removing duplicates, and formatting data for analysis.
To automate using Excel actions, Microsoft Excel must be installed on the machine.
We support the following versions:
- MS Excel 2013
- MS Excel 2016
- MS Excel 2019
Available Actions
Open Excel
Launch a specified Excel workbook. This is usually the starting point for any Excel-related automation task.
Example: Open a specific Excel spreadsheet, such as a daily sales report or a customer database.
Configuration
Excel instance: The variable name assigned for the Excel Instance that's currently open.
Execute in the background?: You can specify whether the Excel instance should be open while the workflow is being executed or whether it should run in the background.
Open Excel with: Choose whether you want to open an existing sheet or a blank sheet.
File path: If you choose to open an existing sheet, you can specify the file path here.
Create a new file if it isn't available?: Allows you to create a new file if the file does not exist in the specified path.
Password to open: Provide the password of the Excel workbook, if password-protected.
File protection: Choose whether the file is read-only or writeable.
Password to edit: Provide the password to edit the Excel workbook, if password-protected.
Read from Excel
Extract data from designated cells or ranges within an Excel sheet. Useful for data analysis, validation, or feeding data into other applications.
Example: Extract data from an Excel spreadsheet and populate it into another system, such as a CRM or ERP system.
Configuration
Excel instance: The Excel instance from which you want to read data.
Use the output data in subsequent cloud app actions?: Choose Yes if you want to use the data read from the sheet in your cloud app actions or custom functions in the same flow.
Note: If you plan to further process the extracted data in other cloud app actions, such as integrating with Slack or Zoho CRM or Custom Functions, select Yes for the Use the output data in subsequent cloud app actions? option. This will transfer the data from the Excel file to the RPA server, where it can be manipulated and used by other cloud app actions or custom functions.
However, if your processing needs are limited to local operations, like copying data within Excel files or transferring data between Excel and an ERP system, you can select No. This will keep the data processing within your local environment, avoiding unnecessary data transfer to the RPA server.
Sheet name: The name of your Excel worksheet.
Note: If you use a variable for the file name in the Open Excel action, you will not be able to use the Refresh option. Choose "Use custom value" and enter the sheet name
Read data from: Choose the criteria with which you want to read data from the worksheet. You can choose from cell, row, column, a specific range, or from the entire sheet.
Cell address: The reference of the cell you want to read data from, if you've selected Cell as the data source.
Row option: Choose from first row, last row, or a specific row if you've selected Row as the data source. If you've selected Specific Row, you'll have to specify the row number in the following field.
Row number: The row number from which you want to read data from, if you've selected Row as the data source.
Header row: You can choose Zoho RPA to detect the header row automatically, specify a row as the header row, or choose the default header row in your Excel worksheet.
Read mode: The data in the sheet can be filtered, hiding certain data cells from normal view. This feature allows you to select the source from which to read the data.
Include data format: You can enable this option to read data with formatting, such as currency, or when transferring data between sheets. For example, $600 or 65%.
Range: To specify the range, indicate the starting and ending columns and rows. Columns are denoted by the letters A, B, and so on, while rows are numbered 1, 2, 3, and so on.
Click the "Refresh fields" icon next to the "Read from Excel" action in the Variables pane. This fetches the column headers from the Excel file for use in subsequent actions.
Get the value of a specified data key from a read from excel task:
Read from Excel [ Entire Sheet]
${readFromExcel_2.excel_read_data[<index>]."<column header name>"}
Example:
# Get the Order ID for the first record when reading the entire sheet
${readFromExcel_2.excel_read_data[0]."Order ID"}
Read from Excel [ Specific row/Column]
${readFromExcel_2.excel_read_data."<column header name>"}
Example:
# Get the Order ID for the specified row when reading a specified row:
${readFromExcel_2.excel_read_data."Order ID"}
Note:
- The index of the first record is 0. You only need to use an index when reading the entire sheet or specific range on a read from excel task.
- The column header name must be enclosed in double quotes.
- You can use the . (dot) operator to access the value of a specified key in a data table.
Write to Excel
Efficiently input, update, or create data within Excel spreadsheets.
Example: Streamline account the payable process by inputting supplier invoices into spreadsheets for processing.
Configuration
Excel instance: The Excel instance from which you want to write data to.
Sheet name: The name of your Excel worksheet.
Write data to: Choose the option for where you would like to write data to: a cell, a row, a column, or a specific range.
Value to write: Enter the value that you want to write in the selected cell, row, column or specified range.
Row option: Choose the Row option to decide whether you prefer writing to the first available row or a specific row.
Column option: Choose the Column option to decide whether you prefer writing to the first available column or a specific column.
If data already exists: Decide what should happen if there is already data in a cell, row, column, or range, and choose whether to replace the existing data or move it below or to the right.
AutoFill
Automate the filling of cells in your spreadsheet using a predictable sequence, pattern, or formula.
Example: Generate email addresses based on first and last names or generate sequential product codes for your inventory.
Configuration:
Excel instance: The Excel instance from which you want to autofill data.
Sheet name: The name of your Excel worksheet.
Source: Select the cell or range containing the starting formula, pattern, or value.
Cell Address: The exact cell with the starting formula, pattern, or value.
Range: Source range that has the formula, pattern, or value.
Target range: Select the destination range where you want the pattern or formula applied.
Delete from Excel
Efficiently remove specified data from Excel spreadsheets, aiding in data cleanup and maintenance. It can be useful for maintaining data accuracy and compliance by eliminating redundant or outdated entries.
Example: Automate the removal of expired employee records in Excel to ensure HR data compliance.
Configuration
Excel instance: The Excel instance you want to delete data from.
Sheet name: The name of your Excel worksheet.
Delete: Choose the criteria with which you want to delete data from the worksheet. You can choose from cell, row, column, a specific range, or sheet.
Cell address: The reference of the cell from which you want to read data from, if you've selected Cell as the data source to delete.
Row number: The row number from which you want to delete data from, if you've selected Row as the data source to delete.
Column: The column letter from which you want to delete data from, if you've selected Column as the data source to delete.
Range: The From and To row numbers and From and To column letters for the range you want to delete, if you've selected Range as the source to delete.
Delete mode: The data in the sheet can be filtered, hiding certain data cells from normal view. This feature allows you to select the source from which to delete the data.
Save Excel
Save Excel workbooks with specific configurations and file formats. It is essential for recording changes made to Excel data, ensuring data preservation, and facilitating further analysis or reporting.
Example: Save Excel workbook in different formats, such as .csv or .xlsx, for compatibility with other software systems, enabling seamless data integration
Configuration
Excel instance: The Excel instance in which you want to save.
Save mode: Select how to save the changes. That is, whether you want to save to an existing document or create a new one.
Folder path: If you choose to save as a new document, specify the folder path here.
File name: The name of the new file that you want to save as.
File format: The file format for the new file that you are about to save. You can choose from various supported formats, such as Excel workbook (.xlsx), Excel workbook (.xls), CSV (.csv), or Macro-enabled workbook (.xlsm).
Filter data from Excel
Display selectively and manipulate data within Excel spreadsheets by applying filters to specific columns or criteria. This action is useful for segmenting data for further processing or analysis and reporting.
Example: Filter Excel inventory records to identify items with low stock levels, and then automate the reordering process.
Configuration
Excel instance: The Excel instance in which you want to filter data from.
Use the output data in subsequent cloud app actions?: Choose Yes if you want to use the data from the sheet in your cloud app actions in the same flow.
Sheet name: Choose the name of your Excel worksheet.
Filter data from: The specified parameters are necessary to indicate the source of data filtering — whether you want to filter the rows of data from an excel sheet or a specific table within that sheet.
Indicate header row: Select the header row in order to identify the column names.
Table name: If you are filtering data from a specific table, specify the name of the table here.
Clear existing filters: Select whether to clear any existing filters before applying this new filter.
Filter configuration: The conditions with which you want to filter the data. You can use a variety of operators, such as equals, greater than, less than, contains, and so on. You can also combine multiple conditions using the AND and OR operators.
Find and Replace
Automatically locate specific data within an Excel spreadsheet and replace it with desired values, streamlining data correction and standardization processes. Useful for ensuring data accuracy and consistency in various applications.
Example: Automate the process of finding and replacing outdated product codes with the current ones in an Excel inventory sheet, ensuring data accuracy.
Configuration
Excel instance: The Excel instance in which you want to look for and replace data.
Sheet name: The name of your Excel worksheet.
Search mode: Choose the operation you'd like to perform: find, find all, replace the first one, or replace all that's found.
Find value: The value to look for.
Replace: The value to replace the found value with.
Match entire cell contents: Choose whether to look for content that matches the cell content partly or fully to consider as a valid match.
Match case: Whether to consider the case of the content or not.
Search order: The order by which the search is performed. For example, say you were looking for "Zoho", and it was present in cells A4 and C3. If you search by Rows, then the first one to be found will be C3. If you search by Columns, then the first one to be found will be A4.
Search specific range on sheet: Check this box if you want to search within a specific range in the sheet.
Format data in Excel
Automatically apply formatting changes to data within Excel spreadsheets. Useful for enhancing the visual presentation of data, making it more readable, organized, and suitable for reporting and analysis.
Example: Format currency data in Excel financial reports, making it easier for stakeholders to understand and compare figures or color-coding products in inventory reports to indicate stock levels or expiration dates.
Configuration
Excel instance: The Excel instance in which you want to apply format to data.
Sheet name: The name of your Excel worksheet.
Format: Choose what you would like to format. You can choose from row, cell, column, or specific range.
Data Type: Choose the data type you would like to apply, such as text, number, currency, percentage, etc.
Alignment: Alignment options for your content, including horizontal or vertical alignment, as well as text wrap.
Font: Choose the font, style, color, and size.
Fill: You can also choose to fill the cell with a specific color.
Find first free row & column
Identify the next available row and column in an Excel spreadsheet. This is essential for dynamically adding new data without overwriting existing information and ensuring data continuity.
Example: Find the first free row and column in an Excel timesheet, making it possible to log employee work hours without overwriting prior entries.
Configuration
Excel instance: The Excel instance in which you want to find the first free row and column for.
Sheet option: Choose the sheet you want to find the first free row or column on. You can choose from the currently active sheet or a specific sheet.
Add new Sheet
Dynamically create new sheets within an Excel workbook, facilitating organized data storage and management.
Example: Add new sheets for different project phases, allowing for the organized tracking of project progress and milestones.
Configuration
Excel instance: The Excel instance in which you want to create a new sheet.
New sheet name: Name of the new sheet that you want to create.
Get all Sheets
Retrieve the list of all the sheet names within an Excel workbook. This can be useful for automating processes that involve multiple sheets, where it is required to interact with, analyze, or process data from various sheets within the workbook.
Example: Extract sheet names for different phases of a project, facilitating the seamless monitoring of progress and milestones while keeping each aspect separate for analysis and management.
Configuration
Excel instance: The Excel instance in which you want to retrieve the list of all the sheet names.
Switch to sheet
Navigate between different sheets within an Excel workbook. Useful for automating complex data management tasks that involve multiple sheets, making it possible to locate, process, and manipulate data efficiently.
Example: Navigate between product-specific sheets to adjust stock levels, reorder products, and manage inventory efficiently.
Configuration
Excel instance: The Excel instance in which you want to switch sheets.
Activate sheet by: Choose how to switch to the required sheet — by name or by index.
Index: The index of the required sheet (if you've selected index in the Activate sheet by field).
Sheet name: Name of the worksheet (If you've selected name in the Activate sheet by field).
Run macro
Excel macros are a set of predefined actions and commands that are recorded and executed to automate complex data processing, analysis and reporting tasks. The run macro action allows you to run these pre-defined macros within an active Excel workbook.
Example: Execute a macro to automate data cleansing tasks, such as removing duplicates and correcting formatting errors in Excel datasets
File extension requirements
By default, the RPA action is designed to execute macros that are contained directly within the active workbook, which usually has the .xlsm file extension (macro-enabled workbook)
Configuration
Excel instance: The open Excel instance in which you want to execute the macro. (e.g., ExcelInstance1)
Macro name: The exact name of the macro you wish to run (e.g., CleanData or FormatReport).
Macro arguments: Input parameters required by the macro. Enter multiple inputs as comma-separated values, ensuring the order matches the arguments defined in the VBA code.
Run macros on non xlsm file types
When you want to run a macro on a standard file (e.g., a .xlsx report), Zoho RPA requires the macro source file (e.g., PERSONAL.XLSB) to be located in the folder that Excel recognizes as the default local file location.
Perform the following steps:
1. Copy your personal macro workbook, PERSONAL.XLSB, in its default system path (the XLSTART folder).
This is usually in the following folder.
C:\Users\<your username>\AppData\Roaming\Microsoft\Excel\XLStart\PERSONAL.XLSB
2. Open your MS Excel App, go to File > Options > Save > Default local file location.
3. Paste a copy of the PERSONAL.XLSB file into the Default local file location folder you found in Step 2.
Note: Since this is a copy of your original PERSONAL.XLSB file, you must replace the file in this location every time you add or update a macro in your primary personal macro workbook.
Configuration
Once you are done, use the following configuration for your RPA action:
Excel Instance: The open Excel instance where you want to execute the macro (e.g., ExcelInstance1).
Macro name: PERSONAL.XLSB!<macroname>
The name of the macro you wish to run (e.g., PERSONAL.XLSB!CleanData).
Macro arguments: Input parameters required by the macro. Enter multiple inputs as comma-separated values, ensuring the order matches the arguments defined in the VBA code.
Close Excel
Close Excel workbooks or the Excel application after completing data processing tasks. This can be useful for saving system resources and ensuring data security.
Example: After generating monthly financial reports, RPA closes the Excel application, saving system resources and ensuring data security.
Configuration
Excel instance: The Excel instance that you want to close.
Save before close: Allows you to save changes to the Excel file before closing it.