Skip to main content

Excel File Connector

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.
Note: Use the MicrosoftExcel Connector in Robotic Desktop Automation (RDA) automations. For example, use the MicrosoftExcel Connector when you need to automate using events from Microsoft Excel, 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 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).

excel connector 3

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.

excel connector 1

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:

Property Description
Design (Name) Name of the Excel connector component. Create unique names if using more than one ExcelConnector in the same automation or the global container
FileName

Excel workbook file name (along with the directory path)

SheetName Workbook sheet name. The options populate based on the configured FileName property
Password Excel workbook file password. Applicable, if the Excel workbook is protected with a password

Guidelines for using Excel File Connector and Excel range

  • 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.

Excel File Range Object

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.
Note: You can create a range object for an Excel File Connector regardless of its location (Automation or Global container).
excel connector 2

The important properties of Excel Range are:

Property Description
SheetName Workbook sheet name. This setting populates with worksheet names of the parent ExcelComponent contains a configured FileName)
StartAddress Data range starting cell address
EndAddress Data range ending cell address
SkipEmptyRows True/False. Defines whether to retrieve empty rows on data range load.

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.

If you are having problems with your training, please review the Pega Academy Support FAQs.

Did you find this content helpful?

Want to help us improve this content?

We'd prefer it if you saw us at our best.

Pega Academy has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice