Code stage for removing unique values in spreadsheet/collection

[TyAllred]

Hi there,

I am trying to remove duplicates from a column in Excel, but have found the only way to do this is to insert a code stage. Unfortunately, I do not have the any experience working with code stages and do not know how to properly code it.

Does anyone have experience using code stages to remove duplicates, or know of the proper way to do it?

If it is easier, I am fine to import the column as a collection into BP and remove duplicates in the collection.

Would be very much appreciated!

------------------------------
Ty Allred
------------------------------

[PaulHumphreys]

​Hi there.  It would be possible to do a code stage I am sure, however, depending on the level of expertise with VB/C# etc. within your area, it might not be the best way to approach it.  If something were to go wrong and you needed to look into an error, would you have the knowledge to be able to identify where it was going wrong?  The simpler and more future-proofed option might be to do the collection manipulation in Blue Prism.  That way anyone who is trained in Blue Prism will be able to understand what is happening in the code and make fault investigation easier.

If the code stage is a viable option for you, you will need to do some advanced filtering and the best way to work out what the code stage needs to do is to record a macro in Excel of the actions you want to take (which wll be in VBA in the Editor of Excel) and then adapt it to work as a VB code stage in Blue Prism.  I can have a look at how this would work for you if you want to go down this route.

------------------------------
Humphreys
Senior Technical Specialist
Europe/London
------------------------------

[PaulHumphreys]

You piqued my interest so I had a look at how simple this might be to achieve.  It is actually really simple in coding terms so might not be much of a risk to have it as a code stage after all.  Your code stage will need to have the following inputs:

handle of the excel instance
workbook name
the range you want to filter

In my example I have called these handle, workbookname and filterrange (note the lack of spaces in the parameter names - this is deliberate as the code stage will not be able to handle any names with spaces).

In the code stage itself, you then need to assign an object to the workbook and perform the advanced filter to unique values:

Dim wb as Object = GetWorkbook(handle,workbookname)

wb.Activesheet.Range(filterrange).AdvancedFilter(Action:=1, Unique:=True)

NB:  The 'Action =1' replaces 'Action:=xlFilterInPlace' due to the fact that Blue Prism doesn't hold all of the Excel Object Naming Conventions and we need to use the numerical identifier instead.

I hope this makes sense.  unfortunately, I won't be able to share the actual Blue Prism code stage I have built as I only have Blue Prism on my work machine.

If you have any questions, let me know and I will try to help further.

​

------------------------------
Humphreys
Senior Technical Specialist
Europe/London
------------------------------

[TyAllred]

Hey @PaulHumphreys,

Thank you so much for the help! I tried to follow your instructions as closely as I could but am still returning an error when I check the code. I will attach a screenshot to show you what I have written and hopefully you can see where I went wrong.

​

------------------------------
Ty Allred
------------------------------

[PaulHumphreys]

​Hi Ty

You need to add these functions into your Global Code in the Initialise action of your object:

Protected Function GetWorkbook(Handle As Integer, Name as String) As Object

 Dim wb as Object = Nothing
 If String.IsNullOrEmpty(Name) Then
  wb = GetInstance(Handle).ActiveWorkbook
  If wb Is Nothing
   wb = NewWorkbook(Handle)
  End If
  Return wb
 Else
  Return GetInstance(Handle).Workbooks(Name)
 End If

End Function

Protected Function GetInstance(Handle As Integer) As Object

 Dim Instance As Object = Nothing
 
 If Handle = 0 Then
  If CurrentInstance Is Nothing Then  
    Create_Instance(Handle)   
  End If
  Return CurrentInstance
 End If

 Instance = HandleMap(Handle)
 If Not Instance Is Nothing Then
  CurrentInstance = Instance
 End If
 Return Instance

End Function

------------------------------
Humphreys
Senior Technical Specialist
Europe/London
------------------------------