Excel Connector
The Excel Connector expands the functionality of the Microsoft Excel component in scenarios without interaction between users and the Microsoft Excel workbook. To access the Excel Connector, click Internal Data Sources > MS Office Applications.
Excel Connector features
The Excel Connector provides the following features:
- You can create, modify, and read from Excel workbooks using Pega Robot Studio without an Excel application installed in the environment. The connector uses the Open Office XML format to create Excel workbooks.
- The Excel Connector includes additional methods that enhance your ability to work with an Excel workbook and allows you to work with data ranges.
- The Excel Connector is faster for large spreadsheets than the Microsoft Excel component.
Note: Use the Microsoft Excel component when you need to automate using Microsoft Excel events, scenarios where the user needs to interact with the workbook in Excel, and scenarios where you need to run macros in an Excel document.
The Excel Connector in an automation
To use the Excel Connector in your automation, drag-and-drop the Excel Connector component from the Toolbox to the automation surface to add the component to the project's Global section of the application. Use the Excel Connector property grid to configure necessary information about the source and destination file.
| Property | Description |
|---|---|
| Name | Name of the Excel Connector component. Create unique names if using more than one Excel Connector in the same automation or the Global container |
| FileName | Excel workbook file name (along with the directory path) |
| Password | Excel workbook file password. Applicable if the Excel workbook is protected with a password. |
| SheetName | Workbook sheet name. The options populate based on the configured FileName property |
| SaveAsName | Destination Excel workbook file name |
To work with the component, drag-and-drop it to the design surface and choose a required property, method, or event.
Consider the following scenario. An accounting company prepares weekly Excel reports that contain all transactions of its customers. Every week, an automation adds a new Excel sheet which name is a number representing the current week of the year and copies all customers' names from the customer data input parameter. In the following image, the property grid of the Excel Connector specifies the source and target file name and the source data.
In the following image, click the + icons to see details about using an Exel File Connector to create a new sheet with the customer data.
Excel File range object
Create an Excel Connector range object to specify the data subset in a worksheet for an automation. To create the range, go to the Globals tab and on the Excel Connector, which sources the data. Use a property grid to define the start and end addresses of the range.
If you change the range and want to save that change to the Excel Connector, call the Commit method in your automation. If you have made the changes to an Excel Connector that overlap with a range, call the Load method to add the new values to the range.
In the following example, an automation imports data from a lookup table to the defined Excel range, commits changes to the range, and saves the Excel file.
Check your knowledge with the following interaction.
This Topic is available in the following Module:
If you are having problems with your training, please review the Pega Academy Support FAQs.
Want to help us improve this content?