Using the Microsoft Excel connectors

Using the Microsoft Excel connectors

Description	Choosing a connector when automating Microsoft Excel or manipulating Excel files.
Version as of	19.1
Application	Pega Robotic Process Automation
Capability/Industry Area	Robotic Automation

Using the Microsoft Excel connectors
New URL	https://help.roboticautomation.pega.com/191/#t=components%252Fusing the excel connectors.htm
This article has been moved over to our Pega Community here in order to align with our larger content strategy. Please update any bookmarks or links that you may have to this location.

Note: If you are using version 22.1, see Which type of robot to use.

Why two Excel connectors

Pega Robot Studio includes two connectors for automating Microsoft Excel and files created by Microsoft Excel connectors:

• Microsoft Excel connector – Allows you to create automations that work while you are using Microsoft Excel.
• Excel File connector – Allows you to create, modify, and read from Excel workbooks without having the Excel application opened or installed in the environment.

You can use both connectors in a project. The following is a summary of the major differences in these connectors that will help you understand which connector to use in your automation.

Excel File connector

The Excel File connector works with OpenOffice XML files directly using a .NET component

• Pros
  • Does not require Microsoft Excel to be installed or a Microsoft Office license to work with Excel files.
  • There is no visual presentation of the Excel file, so use this connector when the user does not need to view or modify the Excel file.
  • Handles large files and performs operations much faster than Microsoft Excel connector.
• Cons
  • Does not support binary format Excel files (.xlsb).
  • Does not execute Excel macros.

Microsoft Excel connector

The Microsoft Excel connector uses the COM (Component Object Model) interface exposed by Excel to perform Excel operations.

• Pros
  • Works with a visible worksheet and allows a user to modify the worksheet at the same time.
  • Supports all file types supported by Excel, including binary format Excel files (.xlsb).
  • Supports macro Excel files (.xlsm) and can run macros.
• Cons
  • Requires that you have Microsoft Excel installed.
  • Editing large visible worksheets is slower than using the Excel File connector

Opening a workbook

Before you can automate a worksheet, you must first open a workbook and identify the worksheet that you want to automate.

With the Excel File connector

To open a workbook using the Excel File connector do the following tasks:

1. Set the FileName property to the path of the workbook.
2. Set the Password property if the workbook is password protected.
3. Set the SheetName property to specify the active worksheet once the workbook is open.
4. Run the Open method. The method returns a message that is null if successful and a boolean result value that you can branch on, as shown in the following example:

With the Microsoft Excel connector

To open a workbook using the Microsoft Excel Connector do the following tasks:

• For files that are not password protected, run the Open method with one parameter and supply the path to the workbook as the parameter. The method returns a message that is null if successful and a boolean Result value that you can branch on. Once the file opens, the active worksheet is the first worksheet in the workbook, as shown in the following example:

• For files that are password protected, run the Open method with 3 parameters and supply the path to the workbook, a Boolean value to specify whether the file should be opened Read-Only, and the password. The method returns message that is null if successful and a boolean Result value that you can branch on. Once the file is open the active worksheet is the first worksheet in the workbook, as shown in the following example:

Excel File connector Object Model

When working with the Excel File connector, you have two objects available: the ExcelConnector and the ExcelRange. These objects provide functionality that is distinct from the Microsoft Excel Object Model.

ExcelConnector object

The ExcelConnector object has the following methods that you can include:

• Workbooks - New, Open, Close, Save, SavetoPDF, and Reload
• Worksheets - Add, Delete, Exists, Hide, Insert, Rename,and Unhide
• Cells - Clear, Copy, Find, Get, and Set
• Columns - Add, Clear, Copy, Delete, Find, Get Width, Move, and Set Width and Color
• Rows - Add, Clear, Delete, Find, Get Height, Move, and Set Height and Color
• Convert - Cell address to row/column numbers and Row/column number to cell address
• Import/Export

ExcelRange object

You create an ExcelRange object at design time by right-clicking the Excel File connector and choosing Add Range. An ExcelRange object defines a part of a worksheet. You specify the SheetName and a cell StartAddress and EndAddress. Before working with an ExcelRange, you must call the Load method. The changes that you make to the range are not written to the worksheet until you call the Commit method.

The ExcelRange has the same methods available on the ExcelConnector for working with cells, columns, and rows.

Microsoft Excel connector and the Microsoft Excel Object Model

When working with Microsoft Excel connector, you should have a basic understanding of the Microsoft Excel Object Model or the basic building blocks of a worksheet. This object model is documented at Microsoft Docs (https://docs.microsoft.com/en-us/visualstudio/vsto/excel-object-model-overview?view=vs-2019). Here are the main objects:

• Application
• Workbook
• Worksheet
• Range
• Cell

Application object

The Application object allows you to control the Excel application. You can access this object by extracting a proxy from the ExcelApplication property of the Microsoft Excel connector. Notice that there are more than 90 methods available on this object.

Workbook object

The Workbook object is a collection of worksheets and it represents the workbook that was you opened by using the connector. You can access this object through the ExcelWorkbook property of the Microsoft Excel connector. Use this object to iterate through the worksheets, to activate a worksheet, and to access the properties of the workbook such as the author, comments and whether it has a password. Here is an example of iterating through the worksheets to find "Sheet1".

Worksheet object

The Worksheet object is commonly known as a worksheet. Reading or manipulating a worksheet requires working with the Worksheet object. A worksheet is composed of many contiguous and overlapping blocks of cells called ranges. Worksheet objects can be created and deleted and you can read and manipulate the contents of the worksheet.

Range object

The Range object refers to a contiguous block of cells. A range may be a single cell or may be multiple columns and rows of cells. For instance, a worksheet has a Cells property that is a range that contains all the cells in the sheet. Range objects have a background and foreground color, row height, column width, font, formats, formulas, and values all which you can manipulate. You can also search, sort, import, and export the content included in the range.

Cell object

The Cell object is a range with a single cell. This is the smallest object that you can manipulate in a worksheet. Since a Cell object is a range, all that is possible with a range is also possible with a single cell.