How Excel VBO in 5.0.33 destroyed my weekend (again locale issue)

Some weeks ago I’ve discovered that even if BP was updated on customer environment, business objects were not.
So I decided do update the Excel VBO at the last version: 5.0.33 and I started to see something strange.
When I tried to set a formula in an excel cell the formula did not work anymore, indeed Excel was not able anymore to compute the formulas anymore, moreover i had had similar problems with float values.

1. Static code comparison

I decided do drill down the problem and I started from the static comparison of the code between old code and new code.
At first I noticed the approach is more rational than the previous versions, so well done BP Team, but I also discovered that something changed in the actions below :

Get Worksheet As Collection
Old code

 Dim ws as Object = GetWorksheet(handle, workbookname, worksheetname, False)

 ' Do we have a sheet?
 sheetexists = ws IsNot Nothing
 ' No sheet? No entry.
 If Not sheetexists Then Return

 ws.Activate()
 ws.UsedRange.Select()
 ws.UsedRange.Copy()

 Dim data As String = Clipboard.GetDataObject().GetData(DataFormats.Text, True)
 
 ' The data split into rows
 Dim asRows() As String = Split(data, vbCrLf)
 
 Dim table As New DataTable()
 ' Set a flag indicating the header row
 Dim isHeaderRow As Boolean = True
 
 For Each strRow As String In asRows
  If Not String.IsNullOrEmpty(strRow) Then
  
   Dim fields() As String = Split(strRow, vbTab)
   If isHeaderRow Then
   
    isHeaderRow = False
    For Each field As String in fields
     table.Columns.Add(field)
    Next
    
   Else

    Dim row as DataRow = table.NewRow()
    For i As Integer = 0 To fields.Length - 1
     If i < fields.Length Then
      row(i) = fields(i)
     Else
      row(i) = ""
     End If
    Next I
    table.Rows.Add(row)
    
   End If
    
  End If
  
 Next
 worksheetcollection = table

OOB 5.0.33

  Dim ws as Object = GetWorksheet(handle, workbookname, worksheetname, False)

 ' Do we have a sheet?
 sheetexists = ws IsNot Nothing
 ' No sheet? No entry.
 If Not sheetexists Then Return

 ws.Activate()
 ws.UsedRange.Select()
 ws.UsedRange.Copy()

 Dim data As String = GetClipboardText()
 
 worksheetCollection = ParseDelimSeparatedVariables( _
  data, vbTab, Nothing, True)

Set Cell Value
Old code

GetInstance(handle).ActiveCell.Value = value

OOB 5.0.33

GetInstance(handle).ActiveCell.Value = value
Dim activeCell = GetInstance(handle).ActiveCell
SetProperty(activeCell, "Value", value)

WriteColl
Old code

 ' Get to the cell
 Dim ws as Object = GetWorksheet(handle,workbookname,worksheetname)
 Dim origin as Object = ws.Range(cellref,cellref)
 Dim cell as Object = origin

 Dim colInd as Integer = 0, rowInd as Integer = 0 ' Offsets from the origin cell
 
 ' Deal with the column names first
 If includecolnames Then
  For Each col as DataColumn in collection.Columns
   Try
    cell = origin.Offset(rowInd, colInd)
   Catch ex as Exception ' Hit the edge.
    Exit For
   End Try
   cell.Value = col.ColumnName
   colInd += 1
  Next
  rowInd += 1
 End If
 
 ' Now for the data itself
 For Each row as DataRow in collection.Rows
  colInd = 0
  For Each col as DataColumn in collection.Columns
   Try
    cell = origin.Offset(rowInd, colInd)
   Catch ex as Exception ' Hit the edge.
    Exit For
   End Try
   'MessageBox.Show("RowOffset:" & rowInd & "; ColOffset:" & colInd & "; cell: " & cell.Address(False,False))
   cell.Value = row(col)
   colInd += 1
  Next
  rowInd+=1
 Next

OOB 5.0.33

 Get to the cell
Dim ws As Object = GetWorksheet(handle, workbookname, worksheetname)
Dim origin As Object = ws.Range(cellref, cellref)
Dim cell As Object = origin

Dim colInd As Integer = 0, rowInd As Integer = 0 ' Offsets from the origin cell

' Deal with the column names first
If includecolnames Then
 For Each col As DataColumn In Collection.Columns
  Try
   cell = origin.Offset(rowInd, colInd)
  Catch ex As Exception ' Hit the edge.
   Exit For
  End Try
  SetProperty(cell, "Value", col.ColumnName)
  colInd += 1
 Next
 rowInd += 1
End If

' Now for the data itself
For Each row As DataRow In Collection.Rows
 colInd = 0
 For Each col As DataColumn In Collection.Columns
  Try
   cell = origin.Offset(rowInd, colInd)
  Catch ex As Exception ' Hit the edge.
   Exit For
  End Try
  'MessageBox.Show("RowOffset:" & rowInd & "; ColOffset:" & colInd & "; cell: " & cell.Address(False,False))
  SetProperty(cell, "Value", row(col))
  colInd += 1
 Next
 rowInd += 1
Next

I highlighted what I think is the most interesting change: BP decided to use late binding instead of early binding when dealing with read/write operations.
It’s not hard to understand that this seems to be the problem so I tried to discover what are the main differences between early binding in late binding, so I found this article on MSDN which clarifies everything (more or less):
Late binding is still useful in situations where the exact interface of an object is not known at design-time. If your application seeks to talk with multiple unknown servers or needs to invoke functions by name (using the Visual Basic 6.0 CallByName function for example) then you need to use late binding. Late binding is also useful to work around compatibility problems between multiple versions of a component that has improperly modified or adapted its interface between versions 
It’s also true that Microsoft does not encourage this kind of approach, indeed in the same article I read this:
Microsoft Office applications provide a good example of such COM servers. Office applications will typically expand their interfaces to add new functionality or correct previous shortcomings between versions. If you need to automate an Office application, it is recommended that you early bind to the earliest version of the product that you expect could be installed on your client's system. For example, if you need to be able to automate Excel 95, Excel 97, Excel 2000, and Excel 2002, you should use the type library for Excel 95 (XL5en32.olb) to maintain compatibility with all three versions. 

Even if I don’t like this approach because it does not help performances I also don’t understand why it causes the problem I told you before, did you try to wrap reviews issue using visual studio using the involvement code lines from global code of the business objects.

2. Dynamic code comparison

I created a fake process which did read/write on excel and i noticed no regressions in GetWorksheetAsCollection, instead of the other two methods, so i focused on the method below, which is called by WriteColl and SetCellValue and is ultimately responsible of data write in Excel.

Private Sub SetProperty(Instance As Object, Name As String, ParamArray args As Object())
    Dim culture = Thread.CurrentThread.CurrentCulture
    'culture = Globalization.CultureInfo.GetCultureInfo(1033)

    Instance.GetType().InvokeMember(Name, Reflection.BindingFlags.SetProperty, Nothing, Instance, args, culture)
End Sub

I discovered that the problem was regional settings and not the late binding itself, indeedn using these references below i figured out that, when you have to write data on Excel, it's safe to use en-Us locale settings:

1. How to: Make String Literals Region-safe in Excel Using Reflection
2. Globalization and Localization of Excel Solutions

So I decided to retest everything with Visual Studio, consider that my culture is IT

Wrong result using current culture

Right result just forcing none culture

3. Conclusion

After this analysis i discovered that, if you are currently on machines with locale different from en-US, you could have problems with data type different from string or text, so you can apply my solution or rollback just the SetCellValue action to ensure you a better weekend.

Read More

Playing with RDP and Surface Automation

This is a small blog post about a little trick very useful when you are in supervised-run phase.
Typically in this phase, you run the process in debug mode at full speed on a particular machine in order to discover weak points of the automation and performance issues.
If the automation is design to use surface automation techniques, desktop resolution will become an important factor.
Could sound strange, but sometimes you have to do deal with higher resolutions so I was wondering what could be a method good method to monitor more machines together having a higher resolution and I found an answer in smart sizing.

screen mode id:i:1
use multimon:i:0
desktopwidth:i:2560
desktopheight:i:1280
session bpp:i:32
winposstr:s:0,1,374,70,1206,588
compression:i:1
keyboardhook:i:2
audiocapturemode:i:0
videoplaybackmode:i:1
connection type:i:6
displayconnectionbar:i:1
disable wallpaper:i:0
allow font smoothing:i:1
allow desktop composition:i:1
disable full window drag:i:0
disable menu anims:i:0
disable themes:i:0
disable cursor setting:i:0
bitmapcachepersistenable:i:1
full address:s:vcolmcn13044
audiomode:i:0
redirectprinters:i:1
redirectcomports:i:0
redirectsmartcards:i:1
redirectclipboard:i:1
redirectposdevices:i:0
redirectdirectx:i:1
autoreconnection enabled:i:1
authentication level:i:2
prompt for credentials:i:1
negotiate security layer:i:1
remoteapplicationmode:i:0
alternate shell:s:
shell working directory:s:
gatewayhostname:s:
gatewayusagemethod:i:4
gatewaycredentialssource:i:4
gatewayprofileusagemethod:i:0
promptcredentialonce:i:1
use redirection server name:i:0
drivestoredirect:s:
smart sizing:i:1

The key are those three parameters

desktopwidth:i:2560
desktopheight:i:1280
...
smart sizing:i:1

Don’t ask me why the remote machine has this resolution (2560x1280 vs 1920x1080 of the physical machine) but this way I can’t resize the RDP window with no interference on surface automation.

Read More

Namespace collisions in BluePrism

This will be one of the shortest both on my blog and is about a short and sad story about code refactoring.
I was trying to reorder business objects introducing namespaces, as you already know BP encourage to create more than just one business object per application: the problem is you will commonly use at least one business object which holds the most common actions.
Let’s imagine an application called Generic Bank Web System (yep it’s not an original name) and a VBO called GBW with this actions:

• Login 
• Log out 
• Navigate to function 
• Check payments 
• Do payments 
• Get customer data 
• Set customer data 

Now we can imagine this place to split this object into three objects, this will help us to keep objects small and maintainable, will also help us to prevent performance issues.

• GBW 
• Login 
• Log out 
• Navigate to function 
• GBW.Payments 
• Check payments 
• Do payments 
• GBW.Customer 
• Get customer data 
• Set customer data 

You can also choose other criteria when sleeping your dress you can choose to split by functional area, by process etc.
What you cannot do is to name the group exactly as one of the existing business objects because BP is not able to recognize the difference between group and business objects when compiling the process: even if during debug sessions everything seems to work like a charm, when you switch to production and you launch the process from the control room or maybe from the scheduler the result will be an error

Failed to create session on machine... - failed to get effective run mode – Unable to calculate run mode – object GBW does not exist 

KABOOM

Good to go

In this case, the solution is simply to rename the object GBW in GBW.Common, by the way, be careful to include in the common object just the error-proof actions in order to avoid regressions.
Thanks to Antonio Durante for the useful brainstorming sessions … yes! again 😊

Read More

An approach to exception handling in BluePrism

One of the things I dislike the most is the fact that when someone approaches RPA is lead to think that he/she doesn’t need any, programming language knowledge: sadly it’s not true.
The proof of that is the poor exception handling I often see: we know from traditional programming that is better to handle exceptions at the higher level and in BP is exactly the same.
Before we have to distinguish between technical and business exception:

• Technical exceptions are the most common ones and related to temporary application problems (or bad programming). Could be handled locally using the exception block because of these exceptions can be solved simply re-trying a specific part of the process raising the exception only if max number of tentatives is reached;

• Business exceptions are related to the process logic and most of the times it means that you cannot work the item in the queue, or maybe you just have to skip that particular process step 

Because of when we talk about RPA, we are mainly talking about workflows, if something falls apart the logic we designed most of the time the result will be that the item could not be worked, so there is no reason not to manage them on the main page.
Consider we are always dealing with the producer-consumer model (if you don’t know what I’m talking about read this blog post first ) so in the image below I just added a piece in the producer-consumer model by defining exception types and recognizing it in the main page in order to what is needed according to the exception type.

Exception handling to the higher level

After that, the robot will pick another item from the queue and start working again. Thanks to Antonio Durante for the useful brainstorming sessions which lead us to define new standards

Read More