Excel File Connector

The Excel File Connector expands the functionality of the MicrosoftExcel connector in Robotic Process Automation (RPA) unattended scenarios when there is no interaction between the user and the Microsoft Excel workbook. The Excel File Connector is available in the Toolbox as the ExcelConnector component.

The ExcelConnector provides the following benefits:

• You can create, modify, and read from Excel workbooks by using Pega Robot Studio™ without an Excel application installed in the environment. The system uses the Open Office XML format to create Excel workbooks.
• The ExcelConnector includes additional methods that enhance your ability to work with an Excel workbook. With the MicrosoftExcel Connector, you can only get or set cell values and export or import data as data tables.
• The ExcelConnector lets you define ranges of data to work with, such as a table. You can define the range during design in Studio or by using an automation during runtime.

The ExcelConnector component is located in the Connectors section of the Toolbox. You can add the ExcelConnector component to the automation. By default, this component is located on the Global tab. You can also add the Excel File Connector to the Global Container (preferred).

If the component is not in the Toolbox, you can add the ExcelConnector component to the Toolbox by using the Choose Toolbox Items wizard. Right-click in Connectors section of the Toolbox to access the Choose Toolbox Items window. Enter Excel in the Filter field and select ExcelConnector from results.

When you add the ExcelConnector component to an automation, the component is added to the Global tab. You can also add the Excel File Connector to the Global Container. The Excel File Connector contains the following properties:

• Do not use the CopyCells, CopyRows, CopyColumns, MoveColumns, and MoveRows methods if the destination range contains merged cells. The destination range cannot overlap a merged range.
• When you use methods that read through a worksheet or range, the system starts with column 1 of row 1 and completes all columns of row 1 before proceeding with other rows in sequence.

• The Excel File Connector supports a wide range of formulas. For more information, see the Robotic Automation product page on Pega Community.

• You cannot use the SetCellFormula method on a table header. The table header cannot have the same text as another header in the same table, nor can it be empty. This also causes Excel to recover the workbook when opened.

Create an Excel File Connector range object to specify the subset of the data in a worksheet for an automation. To define the range, specify the sheet name and the start and end addresses of the range.

To add a range, follow these steps:

• In the Object Explorer, right-click the ExcelConnector component.
• Select Add Range. The system adds the excelRange object and denotes it with an R in the upper, left-hand corner of the Range icon.

The important properties of Excel Range are:

To determine which Excel File Connector the range object belongs to, look at the name in the component tray (excelRange on excelConnector) or look at the Excel File Connector property for the range. The system keeps this range in memory.

If you make a change to the range and you want to save that change to the Excel File Connector, call the Commit method in your automation. If you have made changes to an Excel File Connector that overlap with a range, call the Load method to add the new values to the range.