MS Excel - Pivot Tables for UiPath Studio

Description

Provides useful functions for creating and working with pivot tables in MS Excel workbooks. Please note these activities are not mandatory to be used within Excel application scope. The Excel sheet gets attached to the UiPath with the workbook name provided as input.

Prerequisites

NA

Execution

Kindly import the NuGet package to use the activities

Inputs 

CreatePivotTable

WorksheetName (string) – Optional. Default to be the active Worksheet

WorkbookName (string) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.

SourceRange (string) – Required. Like: A1:R100

DestinationWorksheet (string) – Optional. If this argument is omitted, it will be default to be the active worksheet. If the destination worksheet doesnot exist, it will create one

DestinationRange (string) – Optional. If this argument is omitted, it will put the pivot table in A1 of a new created worksheet and return the name of that worksheet. If it's in the current worksheet, just give the address of the start position like: A100. If you want to create a new worksheet to put the pivot table, give an address like this: "NewWorksheetName!A100"

PivotTableName (string) – Required. The name of the newly created pivot table

MultipleRange (bool) – True if multiple ranges are involved

PivotTableFilterbyMultiValues

WorksheetName (string) – Optional. Default to be the active Worksheet

WorkbookName (string) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.

PivotField (string) – Required. Filter by which field

Values (string) – Required. The value could be seperated by comma

MoveToFilter (bool) – Optional. Default to be false which means not to move the field to filter

Show (bool) – Optional. Show or hide these values. Default to be shown.

PivotTableUnfilter

WorksheetName (string) – Optional. Default to be the active Worksheet

WorkbookName (string) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.

PivotTableName (string) – Optional. Default to be the first pivot table in the worksheet

PivotFields (string) – Optional. Unfilter certain fields, like {field1}{field5}{field19}. Default to be clear all filters.

PivotTableChangeDataSource

WorksheetName (string) – Optional. Default to be the active Worksheet

WorkbookName (string) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.

SourceWorksheetName (string) – Optional. where the Pivot table's data source belongs to. Default to be the original source's worksheet

SourceNewRange (string) – Required.

PivotTableAddorResetRows

WorksheetName (string) – Optional. Default to be the active Worksheet

WorkbookName (string) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.

PivotTableName (string) – Optional. The name of the newly created pivot table. Default to be the first Pivot Table in the worksheet

FieldsName (string) – Required. Like: {Field1}{Field4}{Field100}

AddOrReset (bool) – Optional. Default to be "Reset”, Which is false

PivotTableAddorResetColumns

WorksheetName (string) – Optional. Default to be the active Worksheet

WorkbookName (string) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.

PivotTableName (string) – Optional. The name of the newly created pivot table. Default to be the first Pivot Table in the worksheet

FieldsName (string) – Required. Like: {Field1}{Field4}{Field100}

AddOrReset (bool) – Optional. Default to be "Reset",  Which is false

PivotTableAddorResetValues

WorksheetName (string) – Optional. Default to be the active Worksheet

WorkbookName (string) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.

PivotTableName (string) – Optional. The name of the newly created pivot table. Default to be the first Pivot Table in the worksheet

Caption (string) – Optional. Create the name of the value, like "Sum of terminal". Default caption

FunctionIndex (int) – Optional. The function performed in the added data field. Default to function

Position (int) – Optional. The Position of the Field. Default to append

FieldsName (string) – Required. Like: {Field1}{Field4}{Field100}

AddOrReset (bool) – Optional. Default to be "Reset",  Which is false

OpenWorkbookByPassword

WorkbookFullPath (string) - Required.Full Path of Workbook

Password (string) - Optional. If no password, just leave it empty.

MaxTimeForWaiting (int) - Optional. Default to be 120s.

CloseAllInstances

Nil

Outputs

CreatePivotTable

Nil

PivotTableFilterbyMultiValues

Nil

PivotTableUnfilter

Nil

PivotTableChangeDataSource

Nil

PivotTableAddorResetRows

Nil

PivotTableAddorResetColumns

Nil

PivotTableAddorResetValues

Nil

OpenWorkbookByPassword

WorkbookFullPath (string) - Required.Full Path of Workbook

Password (string) - Optional. If no password, just leave it empty.

MaxTimeForWaiting (int) - Optional. Default to be 120s.

CloseAllInstances

Nil

Components Included

Process

Main

NuGet Package

MSExcelPictureOperations

List of Functionality

CreatePivotTable

For creating a pivot table. SourceRange should include the header.

"DestinationWorksheet" default to be the "WorksheetName". "DesinationRange" default to be the appended row. If the worksheet doesnot exist, it will create a new one.

PivotTableFilterbyMultiValues

Pivot table, Set it filtered by multiple values in a Field. The Values can be something like:   "{200025}{200035}{200045}". Wildcard cannot be used here. If the value doesn't exist, it will be skipped.

Notice this method will not move PivotField to Filter unless you set MoveToFilter to be True.

PivotTableUnfilter

If PivotFields is empty, clear all filters;

If PivotFields is like: {Field1}{Field5}. Just remove "Fields1" and "Fields5" from filter.

PivotTableChangeDataSource

Change the pivot table data source. The source must come from the same workbook, but could be from different worksheet.

PivotTableAddorResetRows

This function can :

Add fields to Rows -> Set flgAddOrReset to True

Reset fields in Rows -> Set flgAddOrReset to False

Remove fields in Rows -> Set FieldsName to Empty and Set flgAddOrReset to False

PivotTableAddorResetColumns

This function can :

Add fields to Rows -> Set flgAddOrReset to True

Reset fields in Rows -> Set flgAddOrReset to False

Remove fields in Rows -> Set FieldsName to Empty and Set flgAddOrReset to False

PivotTableAddorResetValues

Each field can only have one caption and function. The new function or caption will make a change on the existing one.

This function can :

Add fields to Values-> Set flgAddOrReset to True

Reset fields in Values-> Set flgAddOrReset to False

Remove all fields in Values-> Set FieldsName to Empty and Set flgAddOrReset to False

xlAverage        -4106   Average.

xlCount           -4112   Count.

xlCountNums  -4113   Count numerical values only.

xlDistinctCount           111      Count using Distinct Count analysis.

xlMax  -4136   Maximum.

xlMin   -4139   Minimum.

xlProduct        -4149   Multiply.

xlStDev            -4155   Standard deviation, based on a sample.

xlStDevP          -4156   Standard deviation, based on the whole population.

xlSum  -4157   Sum.

xlUnknown     1000    No subtotal function specified.

xlVar    -4164   Variation, based on a sample.

xlVarP  -4165   Variation, based on the whole population.

OpenWorkbookByPassword

The handler is from the default instance which is the first instance. This function could open the Excel file directly from Remove Server by using the http or https path or UNC path, while the standard Excel VBO could only use UNC path.

CloseAllInstances - Closes all instances of excel which have been opened by this business object, either through "Create Instance" or "Open Instance" and which have not been discarded through "Close Instance" or "Release Instance"