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

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

bazygon — Fri, 16 Aug 2024 13:07:02 GMT

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?

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

zdenek.kabatek — Mon, 19 Aug 2024 08:14:24 GMT

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

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

bazygon — Mon, 19 Aug 2024 08:57:07 GMT

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

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

zdenek.kabatek — Mon, 19 Aug 2024 11:27:14 GMT

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

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

michaeloneil — Tue, 20 Aug 2024 16:01:02 GMT

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)