Skip to main content

MicrosoftExcel connector

Suggest edit Updated on November 10, 2021

Use the MicrosoftExcel connector to integrate Excel data, functions, and events into a Pega Robot Studio solution.

You can use the connector in automations to write data to cells, read data from cells, and import and export data from Excel. The MicrosoftExcel connector exposes the underlying COM components so that you can automate almost any task that you can manually do in Excel. The object model has four interfaces: Application, Workbook, Worksheet, and Range.

Note: For more information on the Excel object model, see Excel object model overview.

The MicrosoftExcel connector includes methods that you can use to work with these interfaces to perform common Excel tasks. However, by extracting a proxy for one of these interfaces you have access to all of the properties, methods, and events available in Excel. For more information, see Using the MicrosoftExcel connector.

When you add this component to an automation, the system places it on the Global tab of the component tray. This component must be global to be referenced by other automations in the project.

Properties

PropertyDescription
AskToUpdateLinks

If you set this property to True, Microsoft Excel asks the user to update links when it opens files with links.

If you set this property to False, the system automatically updates the links and displays no dialog box. This property is a read/write Boolean. The default is True.

Changes

Specifies the changes to occur when the workbook is closed. Choose from the following options:

  • Save
    • Changes are saved when the workbook is closed.
  • DoNotSave
    • Changes are not saved when the workbook is closed.
DisplayAlerts

If you set this property to True, Microsoft Excel displays alerts and messages while a macro is running.

If you set this property to False, the system suppresses the alerts and messages. This property is a read/write Boolean. The default is True.

ExcelApplicationA COM reference to the Microsoft Excel application.
ExcelApplicationActiveCellSpecifies the Microsoft.Office.Interop.Excel.Range object that refers to the Active Cell in the connector Excel application.
ExcelApplicationActiveSheetSpecifies the Microsoft.Office.Interop.Excel.Worksheet object that refers to the Active Sheet in the connector Excel application.
ExcelApplicationWorkbooksA reference to the workbook object of the Microsoft Excel connector.
ExcelApplicationWorksheetsA reference to the worksheet object of the Microsoft Excel connector.
ExcelWindowHeightReturns or sets a Double value that represents the height, in points, of the main Excel window.
ExcelWindowLeftReturns or sets a Double value that represents the distance, in points, from the left side of the screen to the left side of the main Excel window.
ExcelWindowTopReturns or sets a Double value that represents the distance, in points, from the top of the screen to the top side of the main Excel window.
ExcelWindowWidthReturns or sets a Double value that represents the distance, in points, from the left side of the main Excel window to its right side.
ExcelWorkbookA reference to the workbook object of the Microsoft Excel connector.
ExcelWorkbookActiveSheetA reference to the worksheet object of the Microsoft Excel connector.
ExcelWorkbookWorksheetsIndicates the Microsoft.Office.Interop.Excel.Worksheets collection for the connector Excel Workbook. This collection contains a Worksheet object for each worksheet in the connector Excel workbook.
IsRunningIndicates whether Microsoft Excel is running.
MissingGenerates a System.Reflection.Missing.Value that you use as input for method parameters that you do not want to specify. This is required for methods called from the Range, _Worksheet, and _Workbook proxy objects.
Mode

Specifies how the system opens the workbook file. The options are:

  • Create
  • Open

When you set this property to Create, Robot Studio creates a file if the file does not exist.

If you select Open, the system tries to open the file but does not create it if the file does not exist. If the file does not exist, the system displays an error message.

The default is Create.

OutputName

(19.1.7 and later)

Specifies one of the supported file types.

  • Microsoft Excel
    • .xlsx, .xlsm, .xltx, .xltm, .xls, .xlt
  • OpenDocument spreadsheet
    • .ods, .ots
  • Character Separated Values
    • .csv, .tsv

If you include a Save method, the system uses the value entered for this property unless it is set to null. If set to null, the system uses the value for the FileName property.

SupportedNameIndicates the Microsoft Office name for the Excel program that is in use.
SupportedVersionDisplays the version number of the Excel program.
VersionIndicates the version of Microsoft Excel that is in use.
Visible A Boolean property that indicates whether the main Excel window is visible when the solution is running
Workbook

Use this property to specify the name of the workbook file that you want to use in the solution. You must specify a workbook to use the component.

By default, Robot Studio looks at the exact file path selected. However, you can use the Folder property to determine the folder file path that is resolved at runtime by using predefined tokens. You select the tokens from a menu that has options such as Deployment, Temporary, and Personal. The system resolves the folder file path using the selected token.

WorksheetUse this property to specify the worksheet in the workbook that you want to use in the solution. If you do not enter a worksheet, the system uses the default worksheet.

Events

EventDescription
NewSheetThis event occurs when a new sheet is created in a workbook.
NewWorkbookThis event occurs when a new workbook is created.
QuitThis event occurs when you close Microsoft Excel manually.
SheetActivateThis event occurs when a sheet is activated in a workbook.
SheetCalculateThis event occurs when the current workbook is recalculated.
SheetChangeThis event occurs when the current sheet in a workbook changes.
SheetDeactivateThis event occurs when the current sheet in a workbook is deactivated.
WindowActivateThis event occurs when the main Excel window is activated.
WindowDeactivateThis event occurs when the main Excel window is deactivated.
WorkbookClosedThis event occurs when the current workbook is closed.
WorkbookOpenedThis event occurs when a new workbook is opened.
WorkbookPrintedThis event occurs when the current workbook is printed.
WorkbookSavedThis event occurs when the current workbook is saved.

In builds 19.1.7 and later, the Open and Save methods support CSVLoadOptions and CSVSaveOptions with which you specify the delimiters and other options. Use the OutputName property to specify one of the supported file types. If you include a Save method, the system uses the value that you enter for this property unless it is set to null. If the value is null, the system uses the value for the FileName property.

Methods

MethodDescriptionParametersResult Type
Close

Closes the workbook and the Microsoft Excel application instance that you associate with the MicrosoftExcel connector.

Depending on your entry in the Changes property, the Close method might also try to save any changes before it closes the workbook.

NoneVoid
ExportData

Exports the range of cells from the worksheet into a Microsoft .NET data table.

If you set the createHeader property to True, the method bases the names of the columns in the table on the first row in the range.

String cellStart, String cellEnd, Boolean createHeader DataTable
ExportData

Exports the range of cells from the worksheet into a .NET data table.

If you set the createHeader property to True, the method bases the names of the columns in the table on the first row in the range.

_Worksheet worksheet, String cellStart, String cellEnd, Boolean createHeader DataTable
ExportData

Exports the range of cells from the worksheet into the .NET data table that you specify.

This method retains only the data for the column names that are in the data table that you specify.

This method replaces DBNull.Values with String.Empty in the result table.

If you set the createHeader property to True, the method bases the names the columns in the table on the first row in the range.

_Worksheet worksheet, String cellStart, String cellEnd, Boolean createHeader, ref DataTable exportTableVoid
FindColumnsReturns all matching columns. The search parameter is case sensitive. String value, out Int32[] rows String
FindRowsReturns all matching rows. The search parameter is case sensitive. String value, out Int32[] rows String
GetCellValue

Returns the value in the cell that you specify by using the input parameter.

You can select a specific cell, such as d4, or input the cell number from another object. You can also use a named value as the input. For more information, see Named Value.

String cell String
ImportData (4 parameters)

Imports a .NET data table into the workbook. The row and column are zero (0) based for importing purposes.

The system uses a header for search parameters. If you set the createHeader parameter to True, the automation creates headers for the table.

Boolean CreateHeader, Int32 Rowstart, Int32 columstart, dataTable Void
ImportData (5 parameters)

Imports a .NET data table into the workbook. The row and column are zero (0) based for importing purposes.

The system uses a header for search parameters. If you set the createHeader parameter to True, the automation creates headers for the table.

Use the uiUpdates parameter to suspend user interface updates until the import operation finishes to execute the method more quickly.

DataTable dataTable, Boolean createHeader, Int rowStart, Int columnStart, Boolean uiUpdatesVoid
ImportData (5 parameters)

Imports a .NET data table into the worksheet that you specify. The row and column are zero (0) based for importing purposes.

The system uses a header for search parameters. If you set the createHeader parameter to True, the automation creates headers for the table.

_Worksheet worksheet, DataTable dataTable, Boolean CreateHeader, Int Rowstart, Int columnstart Void
ImportData (6 parameters)

Imports a .NET data table into the worksheet that you specify. The row and column are zero (0) based for importing purposes.

The system uses a header for search parameters. If you set the createHeader parameter to True, the automation creates headers for the table.

Use the uiUpdates parameter to suspend user interface updates until the import operation finishes to have the method execute more quickly.

_Worksheet worksheet, DataTable dataTable, Boolean CreateHeader, Int Rowstart, Int columnstart, Boolean uiUpdatesVoid
Open (1 parameter)Opens a workbook and uses the workbook name that you specify. String Workbook Void
Open (2 parameters)

Opens a workbook and uses the workbook name that you specify.

This method returns True if the open succeeds and False if it fails or there is an exception. The message contains the Exception message.

String WorkbookBoolean, out String message
Open (4 parameters)

Opens a workbook and uses the workbook name that you specify.

Include the readOnly parameter if you want the system to open the workbook in read only mode.

Include the Password parameter if the workbook is protected with a password.

This method returns an error message if the operation fails, but the system does not throw an exception.

String workbook, Boolean readOnly, String password Boolean, out String message
RunMacro (1 parameter)Runs a Visual Basic macro or calls a function. String MacroName Object
RunMacro (6 parameters)

Runs a Visual Basic macro or calls a function.

All of the macro or function parameters are optional.

String Macroname, Optional parameters Object
Save (1 parameter)This method saves the currently opened workbook and overwrites the existing file. NoneVoid
Save (no parameters)

Saves the open workbook and overwrites the file.

This method returns an error message if the operation fails, but the system does not throw an exception.

NoneBoolean, out String message
SaveAsSaves the open workbook and uses the file name that you specify. String filename Void
SaveAs

Saves the open workbook and uses the file name that you specify.

This method returns an error message if the operation fails, but the system does not throw an exception.

String filename Boolean, out String message
SaveAsXLSB

Use this method to save the open workbook that you specify in the fileName parameter, in an XLSB format.

If the operation fails, there is no message.

This method does not apply to Microsoft Office 2002 and 2003.

string fileNameBoolean
SaveAsXLSB

Use this method to save the open workbook that you specified in the fileName parameter in an XLSB format.

If the operation fails, the system returns False and the message that you specified with the out string message parameter.

This method does not apply to Microsoft Office 2002 and 2003.

string fileName, out string messageBoolean, out String message
SetCellValueThis method sets the cell value of the current workbook. In addition to referencing the cell directly, you can use a named value as the input. For more information, see Named Value. String cell, String cellValue Void

Cast methods

MethodDescriptionParametersResult type
TryCastToRange

Casts a generic object into a Microsoft.Office.Interop.Excel.Range type. Use this method when you are iterating through a Range collection with a ListLoop. The ListLoop outputs a generic object without the required properties, methods, and events.

This method creates a Range object with all of its properties, methods, and events. This method returns True if the attempt succeeds and False if there is an exception.

Object obj Boolean, out Range range
TryCastToWorkbook

Microsoft.Office.Interop.Excel.Workbook type. Use this method when you iterate through a Workbook collection with a ListLoop. The ListLoop outputs a generic object without the required properties, methods, and events.

This method creates a Workbook object with all of its properties, methods, and events. This method returns True if the attempt succeeds and False if there is an exception.

Object obj Boolean, out _Workbook workbook
TryCastToWorksheet

Casts a generic object into a Microsoft.Office.Interop.Excel.Worksheet type. Use this method when you are iterating through a worksheet collection with a ListLoop. The ListLoop outputs a generic object without the required properties, methods, and events.

This method creates a Worksheet object with all of the properties, methods, and events expected. This method returns True if the attempt succeeds and False if there is an exception.

Object objBoolean, out _Worksheet worksheet

Cell methods

MethodDescriptionParametersResult type
CellGetActive

Accepts a Worksheet object and returns the column (string) and row (int) of the active cell.

The result is the cell address (A1 style notation).

_Worksheet worksheet String, out String column, out Int row
CellGetValueAccepts a Worksheet object, a column (string), and a row (int) and returns the string value of the cell. _Worksheet worksheet, string column, Int row String
CellSetValueAccepts a Worksheet object, a column (string), a row (int), and sets the cell value equal to the value (string) that you provide. _Worksheet worksheet, String column, Int row, string value Void

Column methods

MethodDescriptionParametersResult type
ColumnDelete

Accepts a Worksheet object and deletes the column that you specified in the columnLetter parameter and shifts the other columns to the left.

_Worksheet worksheet, String columnLetterBoolean
ColumnGetLetter

Converts a columnNumber (int) to a string which represents the column portion of a cell address. For example:

1 = A, 2 = B, 27 = AA

You can use this method to construct the column portion of a cell address when you are iterating through columns using a ForLoop.

Int columnNumberString
ColumnGetNumber

Converts a columnLetter (string) to an int. For example:

A = 1, B = 2, AA = 27

You can use this method to set the limit value of a ForLoop to a column value when you are iterating through the columns in a row.

String columnLetterInt
ColumnInsert

Accepts a Worksheet object and inserts a column at the location that you specify by using the columnLetter parameter. The method then shifts the other columns to the right.

Use the insertFormatOrigin parameter to determine where to get the format for the inserted column. You can choose from e following options:

  • xlFormatFromLeftOrAbove
  • xlFormatFromRightOrBelow

This method returns True if the attempt succeeds and False if there is an exception. The system logs any exceptions.

_Worksheet worksheet, String columnLetter, XlInsertFormatOrigin insertFormatOrigin Boolean
ColumnShowHide

Hides or shows a column.

This method accepts a Worksheet object and sets the visible property for the column you specify.

_Worksheet worksheet, String columnLetter, Boolean visibleVoid

Range methods

MethodDescriptionParametersResult type
RangeClearAutoFilterClears the AutoFilter property for the Range object that you specify. Range rangeVoid
RangeClearFormatsClears the format information for the Range object that you specify. Range rangeVoid
RangeCopyCopies the origin range to the destination range. The origin and destination ranges must be in the same Excel application. Range originRange, Range destinationRangeVoid
RangeGetColumnWidthtrieves the column width for a range. Range rangeVoid, out Double width
RangeGetCoordinates

Returns the address boundaries for a range in A1 notation format.

The cellStart parameter contains the cell address at the per left of the range.

The cellEnd parameter contains the cell address for the bottom right of the range

Range range Void, out String cellStart, out String cellEnd
RangeGetRowHeightGets the row height for a range. Range range Void, out Double width
RangeSetAutoFilter

Sets the AutoFilter property for a single column and returns the filtered range.

Use the XlAutoFilterOperator method to specify the operator to use to associate the two criteria applied by a filter. For more information, see XIAutoFilterOperator Enum.

Range range, Int columnNumber, String filterText, XlAutoFilterOperator filterOperator, Boolean visibleDropdownRange
RangeSetBackgroundColorSets the background color for a range. Range range, Color backgroundColor Void
RangeSetColumnWidthSets the column width for a range. Range range, Double width Void
RangeSetFontSets the font for a range. Range range, Font font, Color color Void
RangeSetRowHeightSets the row height for a range. Range range, Double height Void
RangeSort

Sorts a range.

For complete documentation on this method, see Range.Sort.

Range range, Range key1, XlSortOrder order1, Range key2, XlSortOrder order2, Range key3, XlSortOrder order3, XlYesNoGuess header, Boolean caseSensitive, XlSortOrientation sortOrientation, XlSortMethod sortMethod, XlSortDataOption option1, XlSortDataOption option2, XlSortDataOption option3Void

Row methods

MethodDescriptionParametersResult type
RowDeleteDeletes a row from the worksheet that you specify. _Worksheet worksheet, Int rowNumberBoolean
RowInsert

Inserts a row into the worksheet that you specify after the row number that you specify.

Use the XlInsertFormatOrigin method to specify where to get the format for inserted rows.

_Worksheet worksheet, Int rowNumber, XlInsertFormatOrigin insertFormatOrigin Boolean

Workbook methods

MethodDescriptionParametersResult type
WorkbookActivateActivates the workbook that you specify. _Workbook workbook Void
WorkbookGetActiveSheet

Returns the Worksheet that represents the active sheet (the visible sheet) in the workbook that you specify.

This method returns Nothing if no sheet is active.

_Workbook workbook_Worksheet
WorkbookGetSheets

Returns a Sheets collection that represents all of the sheets in the workbook that you specify.

For more information, see Sheets Interface.

_Workbook workbook Sheets

Worksheet methods

MethodDescriptionParametersResult type
WorksheetClearFormatsClears the format information for the worksheet that is in the workbook that you specify. _Workbook workbook, String sheetNameBoolean
WorksheetDeleteDeletes the worksheet that you specify from the workbook that you specify. _Workbook workbook, String sheetName Boolean
WorksheetDeleteDeletes a worksheet by index from the workbook that you specify. _Workbook workbook, Int32 index Boolean
WorksheetGetByNameGets a worksheet object from the workbook that you specify. _Workbook workbook, String sheetName Boolean, out _Worksheet worksheet
WorksheetGetRange

Gets a range object from the worksheet that you specify with cell boundaries.

The system throws an exception if it cannot find the object.

_Worksheet workSheet, String cellStart, String cellEndBoolean, out Range range
WorksheetGetRange

Gets a range object from the worksheet that you specify using cell boundaries.

This method returns an error message if the operation fails. The system does not throw an exception.

_Worksheet worksheet, string cellStart, string cellEnd Boolean, out Excel.Range range, out string message
WorksheetGetUsedRangeGets the used range object from the worksheet that you specify. The system returns the number of rows and columns. _Workbook workbook, String sheetName Boolean, out Int rowCount, out Int columnCount, out Range range
WorksheetInsert

Inserts one or more worksheets into the workbook that you specify.

Use the insertBefore and count parameters to specify where to insert the worksheet. For example, if you enter True and 3, the system inserts the three new worksheets before the worksheet that you specify with the sheetName parameter.

If you enter False and 3, then the system inserts the three new worksheets after the worksheet you specify with the sheetName parameter.

_Workbook workbook, String sheetName, Boolean insertBefore, Int32 countBoolean
WorksheetInsert

Inserts a worksheet into the workbook that you specify.

Use the insertBefore parameter to specify where to insert the worksheet. For example, if you enter True, the system inserts the new worksheet before the worksheet you specify with the sheetName parameter.

If you enter False, then the system inserts the new worksheet after the worksheet you specify with the sheetName parameter.

_Workbook workbook, String sheetName, Boolean insertBefore, String newSheetName Boolean, out _Worksheet newSheet
WorksheetIsInEditModeDetermines whether the worksheet that you specify is in EditMode. _Worksheet worksheetBoolean
Did you find this content helpful? YesNo

Have a question? Get answers now.

Visit the Support Center to ask questions, engage in discussions, share ideas, and help others.

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

Pega.com is not optimized for Internet Explorer. For the optimal experience, please use:

Close Deprecation Notice
Contact us