Group Items In Datatable, Count Occurances

Assume that you have an excel spreadsheet that has groupable items located in one row, for example in the following array:

{x,y,z,w,x,y,z}

And assume you need to build a new matrix, where the output would be the unique items, and their occurrence count. In a sense, the following output:

{x-2,y-2,z-2,w-1}

in your assign value use the following:

<InArgument x:TypeArguments="s:String[]" xml:space="preserve">[dt1.AsEnumerable.
GroupBy(Function(x) x("Name").ToString).Select(Function(g) String.Format("{0} - {1} times", g.Key, g.Count())).ToArray]</InArgument>

Dealing With RPA Transaction Errors

You may encounter a situation where you might have a transaction error in UiPath. This can be something like a missing element or. To deal with this, most people in RPA bots will follow with decision paths that say if successful, go here, else go here, and so on. The problem you may encounter is if an error gets handled and proceeds to the next transaction, however at the end of that transaction it will fail due to the previous error, even though everything appears to have completed successfully. All transactions after that error fail for the same reason. It won’t even let you set the exception to another variable.

To get around this issue in the TypeInto (or other element) set the ContinueOnError property true, then the activity will not bubble an exception should something go wrong. Alternatively, you can also follow more modern programming paradigms and use a try/catch/finally.

Remove From DataTable Using Date Ranges

In UiPath you may encounter an issue where you have a spreadsheet, and each of the rows within the spread sheet have a correlating date value. This information can often times need to be archived or moved based on this value. The easiest way to accomplish this is to use the Filter Datatable activity, and then you can specify a custom condition. They results datatable can then be output via the Write Range activity.

The condition for specifying a range of everything 5 years or older would look like:

ColumnName > DateAdd(DateInterval.Year, -5, Today)

Replace Vars From A Text File With User Variables

In order to replace variables within a text file with your own user variables, it is necessary to use replacement variables. This is a carry over from other languages, but instead it is known as string composite formatting (takes a list of objects and a composite format string as input. A composite format string consists of fixed text intermixed with indexed placeholders, called format items, that correspond to the objects in the list.)

So let’s say you have some standard alter commands:

alter database 'O'.'O' set variable 'Hello' '"How Are You"';

If you have a source array of values that you want to change, the best option is to use a Read Text File activity and within the template file change the above to:

alter database 'O'.'O' set variable 'Hello' '"{0}"';

Preserving Excel DateTime Formatting

It can problematic when working with Excel files in UiPath to preserve the DateTime formatting when that formatting differs between the documents. If you are using ReadCell or ReadRange then the value is the raw DateTime string, this will also occur when you try to access the cell value directly ala workbook.ReadCell(Of String)(Range as string). In order to overcome this issue, it is necessary to use InvokeCode, and run the following (With an out argument called result and an in argument called inputCell)


Dim xlApp As Microsoft.Office.Interop.Excel._Application = Nothing
Dim xlWorkBooks As Microsoft.Office.Interop.Excel.Workbooks=Nothing
Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook=Nothing
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet=Nothing
Dim xlWorkSheets As Microsoft.Office.Interop.Excel.Sheets=Nothing

xlApp=CType(Marshal.GetActiveObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application)
xlApp.DisplayAlerts=False
xlWorkBooks=xlApp.Workbooks
xlWorkBook=xlWorkBooks(1)
xlApp.Visible = True
xlWorkSheets=xlWorkBook.Sheets

xlWorkSheet= CType(xlWorkSheets(1),Microsoft.Office.Interop.Excel.Worksheet)
result = xlWorkSheet.Range(inputCell).Text.ToString

Outlook Mail Messages – NullReferenceException

The full stack trace is below:

Message: Object reference not set to an instance of an object. This error usually occurs when using a variable with no set value (not initialized).

Exception Type: NullReferenceException

System.NullReferenceException: Object reference not set to an instance of an object

This is a standard object null reference error, meaning some object that is being used has not be instantiated, or is set declaratively as null. Within the context of Outlook and UiPath, this is most likely because your mail lists are empty, this happens within the Get outlook mail messages activity. If this looks good and you are still having issues, you should emit mails.count to standard output and see if there is a problem there while trying to contact the mail list.

Clear Box Content With TypeInto

This method pertains to when in UiPath you need to remove all the content from a box before using the next number variable.

  1. Verify that the typeinto activity will not work. This has an empty field property.
  2. If 1. does not work, Locate the Relative Click container.
  3. Add an Anchor base activity (A container that searches for a UI element by using other UI elements as anchors.)
  4. Input the position of the AnchorPosition in the Anchor Base activity

Select Item From Dropdown In Excel Without Write Cell

It is possible to select a value from a drop down within an Excel file within UiPath by using Write Cell to define a local variable, however if the result is going to be used by a secondary robot this approach will fail. In order to get around this issue, perform the following steps:

  1. Open with Excel sheet via Excel Application Scope.
  2. Point to whatever cell has the available options via Select Range.
  3. Go to Data Validations (“[k(alt)]avv” Shortcut)
  4. Get Text from the source field to get all options.
  5. Write the option using Write Cell.

Excel Activities Bot – Copy Between Files

In order to copy between two Excel files within UiPath it is relatively simple. Firstly, you must define the Excel Application Scope, which opens an Excel workbook and provides a scope for Excel Activities. Following, read the values as batch data in a temporary Datatable. Once that is done it providers a source you can iterate against, so then you can search or do whatever else you want to do and then you can Write Range the CellValue however you want.

Note: If you need to iterate against the column headers it is a little different because if you use Datatable.Columns and would like to create a new variable with each Column Header that is read, you will need use a dictionary variable and store your dynamic variables. This is defined within the Run time variable screen.