07-04-23 03:17 PM
Hi team,
I have an excel in which a column has red colored text in it/font color is red, I have to filter that column on that basis ,Can anyone tell me how to do that?
Answered! Go to Answer.
08-04-23 01:21 PM
@AS - Understood thanks for clarifying your query : Please follow below approach:
1) Add a custom action in one of your excel extended VBOs - Add Criteria to Filter by Font Colour - See below and Add inputs as : Handle, Worksheet Name, WorkBook Name, and Field.
2) Add a Code Stage : Set Inputs as below
3) Write below Code : Note for Criteria its hardcoded to (255, 0, 0) as per your requirement (in Red) - you can remove that colour code and set a user input for custom RGB value if your wish to choose different colour to filter.
4) Add Dll and Namespace: I will attach the dll with this response (Microsoft.Office.Interop.Excel.dll : Free .DLL download)- add this dll to C:\Program Files\Blue Prism Limited\Blue Prism Automate\ :
Dll: Microsoft.Office.Interop.Excel.dll
NameSpace : Microsoft.Office.Interop.Excel.XLAutoFilterOperator
Additionally you would have to specifiy the DataTable as this belongs to both of these Dll : 'DataTable' is an ambiguous reference between 'System.Data.DataTable' and 'Microsoft.Office.Interop.Excel to let the code know which data table you are using - add reference namespace as : System.Data.DataTable so that system does not get confused which reference to use as you might have other actions which are referenced to this DataTable reference from System.Data
See below:
5) Publish the Action and Call from Process :
7) Result/Ouput:
Excel before Code:
Excel After Code:
Let me know if you face any difficulties implementing this.
------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.
Regards,
Mukesh Kumar - Senior Automation Developer
NHS England, United Kingdom, GB
------------------------------
07-04-23 05:44 PM
Hi @AS : Is one of the columns name in red font colour or the rows data in the column have red font ? Do you have a sample snip of how it looks like, it will be easy to understand it then?
07-04-23 06:14 PM
It is a column in which the numbers are entered like 254.78 , these numbers are written in red color, I don't have the snip as much.
07-04-23 06:30 PM
Hi aanchal mathur - (Your Query : column has red coloured text in it/font colour is red, I have to filter that column on that basis)- is it the rows in that specific column which are red in colour and you want to filter those rows which has red font- See below is it like this snip?
Snip
To try creating a code for above such cases : just hit record the macro and do the excel operations when done stop the macro and try understanding the code:
for your scenario -
Macro VBA Code:
You can replicate this code in one of your excel extended objects, additionally you might have to add the Dll : Microsoft.Office.Interop.Excel.dll and Namespace : Microsoft.Office.Interop.Excel.XLAutoFilterOperator
------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.
Regards,
Mukesh Kumar - Senior Automation Developer
NHS, England, United Kingdom, GB
------------------------------
07-04-23 11:04 PM
Hi Aanchal,
You will need a new action in the Excel VBO for this..
The below code is in C# but you can easily convert into VB.net:
xs1.Range["A1", xs1.Cells[lastrow.Row, lastcol.Column]].
AutoFilter(14, xlBook.Colors[33], Excel.XlAutoFilterOperator.xlFilterCellColor);
Bear in mind that in this example the colour id is hardcoded as 33.
You will need to find the appropriate color id for your filter:
https://www.automateexcel.com/excel-formatting/color-reference-for-color-index/
------------------------------
Andrzej Silarow
Principal Consultant
RPA Guru
Europe/London
------------------------------
08-04-23 04:50 AM
Hi ,
It is like snip 2.
08-04-23 01:21 PM
@AS - Understood thanks for clarifying your query : Please follow below approach:
1) Add a custom action in one of your excel extended VBOs - Add Criteria to Filter by Font Colour - See below and Add inputs as : Handle, Worksheet Name, WorkBook Name, and Field.
2) Add a Code Stage : Set Inputs as below
3) Write below Code : Note for Criteria its hardcoded to (255, 0, 0) as per your requirement (in Red) - you can remove that colour code and set a user input for custom RGB value if your wish to choose different colour to filter.
4) Add Dll and Namespace: I will attach the dll with this response (Microsoft.Office.Interop.Excel.dll : Free .DLL download)- add this dll to C:\Program Files\Blue Prism Limited\Blue Prism Automate\ :
Dll: Microsoft.Office.Interop.Excel.dll
NameSpace : Microsoft.Office.Interop.Excel.XLAutoFilterOperator
Additionally you would have to specifiy the DataTable as this belongs to both of these Dll : 'DataTable' is an ambiguous reference between 'System.Data.DataTable' and 'Microsoft.Office.Interop.Excel to let the code know which data table you are using - add reference namespace as : System.Data.DataTable so that system does not get confused which reference to use as you might have other actions which are referenced to this DataTable reference from System.Data
See below:
5) Publish the Action and Call from Process :
7) Result/Ouput:
Excel before Code:
Excel After Code:
Let me know if you face any difficulties implementing this.
------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.
Regards,
Mukesh Kumar - Senior Automation Developer
NHS England, United Kingdom, GB
------------------------------
08-04-23 01:33 PM
Attached is the Dll Copy, Additionally you can download this dll from : https://www.dll-files.com/microsoft.office.interop.excel.dll.html
08-04-23 03:32 PM
Thank you so much for your help.