How to remove rows in excel based on different excel file in Blue Prism

[bazygon]

Hi,
I would like to make project which it will be removing rows from excel file:

 based on another excel file:

Any ideas how to compare this tabels and remove orders from 1st. IMG based on second IMG?

[zdenek.kabatek]

Hi, bazygon,

would it be acceptable following solution - loading first excel into BP collection, loading second excel into BP collection and getting output collection with rows where the values of certain columns are not the same. My action takes as input parameters only one column from each collection so it would be Order from one excel and Order from the other excel.

Regards

Zdenek

[bazygon]

Ok, but how can I compare this two collection and remove duplicate orders?

[zdenek.kabatek]

Hi, bazygon,

happy to share VBO, however, you may need to perform a few changes especially library references. I suggest to jump on a MS Teams call and I will guide you through it. My email address is zdenek.kabatek@neoops.com.

Cheers,

Zdenek

[michaeloneil · Accepted solution]

Hi @bazygon 

To remove duplicates from excel you will need a new action in your excel vbo on blueprism. I built one previously and it should be easy enough for you to replicate. In your excel vbo create a new page and add a code stage then add the code (shown below), add your inputs as Handle, Workbook, Worksheet, Reference (this is the range you want to remove duplicates from), Column number (this is the column you want to remove duplicates from). Add these same inputs to the start icon and then publish the action. This should then be able to remove duplicates in the excel worksheet for you, code and screens below.

Dim wb, ws As Object
Dim excel, sheet, range As Object

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet
range = sheet.Range(Reference)
range.Select()

ws.Range(Reference).RemoveDuplicates (Columns:=Column_number, Header:= 2)