highlight matched records in the excel with color

[SpoorthyRajendr]

Hello All,

Kindly suggest me to achieve the below action .

​How to highlight matched records in the excel with color after comparison between two excel and updated the record based on some action.

------------------------------
Spoorthy
------------------------------

[nobu]

Hi Spoorthy,

It seems that Excel macro can handle those actions. So, one of the options would be that you create a macro, then you can trigger the macro from Blue Prism.

However, if you like to achieve this operation only in Blue Prism, it can be possible. An example is below described.

1. Get the data from the first excel to a Collection stage (Collection1)
2. Get the data from the second excel to an Collection stage (Collection2)
3. Set a Loop with Collection1
4. Set a nested Loop with Collection2
5. Compare the value of [Collecion1.Field] and [Collection2.Field]
6. If those data were matched, write the record to another Collection Stage (Collection3)
7. Once the above Loop was ended, Write Collection3 into Excel file ([MS Excel VBO].[Write Collection])

I'm sure there are several ways to achieve this operation.

Regards,

Nobu





------------------------------
Nobuhiro Tokushige
Product Specialist
Blue Prism
Australia/Sydney
------------------------------

[NB]

Hi Spoorthy,

You can create a custom action to highlight an active cell, uthat can be added the the ms excel VBO.

Here is the code from a custom action that I have implemented in the past:

***********************************************************

Dim lngColor As Long

lngColor = RGB(255, 0, 0)

GetInstance(handle).ActiveCell.Interior.Color = lngColor
Dim activeCell = GetInstance(handle).ActiveCell

************************************************************

The handle needs to be passed as input. To make the action more reusable three additional numbers can be passed as input variables for the RGB values.  When calling the action from the process, call an action to activate the cell before calling this action, as this action highlights the active cell.

Hope this helps!

Regards,
N

------------------------------
N
------------------------------

[SpoorthyRajendr]

​Hi Nikhat,

I have added one action in MS-Excel VBO to format the cell with Color and I could aciehve that action only for one row where it is not highlighted for other rows. I debugged my bot process where the looping is going fine I am not crack the issue why it is not highlighting cell with color for rest for the rows.

If u got some idea to resolve my issue then kindly revert me.
 
Regards,
Spoorthy

------------------------------
Spoorthy Rajendra
Application Team Lead
Accenture
------------------------------