Extract background colour of each cell of every row

[dutta.marina (rpa_06)]

Hi All,

I am facing a problem in extracting background colour of each cell of every row. I am using excel application scope and for each row , however every time i am getting the first cell value only. I want to iterate through all the cells of each row and extract the cell colour value. I want to extract all the cell colour values from A2: O2. If its not feasible using UiPath can anyone help in macro to get all the cell colour values in excel sheet.
SalesForce_OutputFields.xlsx (9.7 KB)

GetCellColour.zip (3.4 KB)

[Anil_G (Anil Gorthi)]

@dutta.marina

Inside the for loop for excel rows…you need to use loop for column as well to iterate through each column of eqch row and use get cell color activity to get the color information

The second loop can be from A to O which will be Enumerable.Range(65,15).ToArray

Now inside second loop we can use chr(currentitem) which give A,B etc till O which iterated theough column and each excel row interates through rows …(Currentindex+2).ToString will give current row index in excel…

Hope this helps

Cheers

[dutta.marina (rpa_06)]

Hi Anil,

I am getting some error in the second loop. do you have the workflow for this.It will be helpful to understand.

Thanks a ton

[Anil_G (Anil Gorthi)]

@dutta.marina

Please check this

I have done few modifications as well to make it simple

cheers

[dutta.marina (rpa_06)]

Hi Anil

Thank you .It worked. Adding to it if I want to filter and get the column names whose cell colour is yellow how should I proceed.

Thanks a ton.

[Anil_G (Anil Gorthi)]

@dutta.marina

If color.Name.equals(“Yellow”) use this in if condition and then on the then side save the excel column name UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(currentItem)

cheers

[dutta.marina (rpa_06)]

Hi Also,

Also I have two questions.

1)Enumerable.Range(0,14).ToArray. What is Enumerable .range ?

[Anil_G (Anil Gorthi)]

@dutta.marina

It will create an array of numbers starting from 0 and adds 1 for 15 iterations so basically cretaes array of numbers from 0 to 14

Cheers

[dutta.marina (rpa_06)]

@Anil_G

UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(currentItem)

Hi Anil,

In the then condition shall i need to assign a variable and save the UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(currentItem)

[dutta.marina (rpa_06)]

@Anil_G

UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(currentItem)

Hi Anil,

Basically i need to extract those columns and paste in sheet2 of excel.

Thanks

[Anil_G (Anil Gorthi)]

@dutta.marina

Can you please open a new question as this is compeltely different and mention the full requirement details…if you need those coloumns full then store the column names and then use read range and write range only on the column value and each column can be pasted in loop

Cheers

[dutta.marina (rpa_06)]

Hi Anil,

Actually I did the following but not getting any column names using UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(currentItem). Any mistake I am doing.

[Anil_G (Anil Gorthi)]

@dutta.marina

Can you try using currentitem+1 i did try and i can see the column name…are you getting any error?

Cheers

[dutta.marina (rpa_06)]

@Anil_G

I did something like this .

UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(currentItem+1). I need only yellow colour column names. for example Column C Accounts-Payable.

Is it wrong ?Getting error

[Anil_G (Anil Gorthi)]

@dutta.marina

Column_Name should be of type string

Cheers

[dutta.marina (rpa_06)]

@Anil_G

I changed to string but I am getting column names as C, D, E etc. Do I need to add any thing else

[Anil_G (Anil Gorthi)]

@dutta.marina

Dont you need the excel column names?

If you need the column name …then add 1 to the retuned letter and use read cell with range as Column_name+ "1"

I assume your column names are in first row always

Cheers

[dutta.marina (rpa_06)]

@Anil_G

I need the Column names . Currently the column names are just for one row. But in future It may increased to 40 to 50 rows under same column . Do I need to proceed in the same way. I am sharing my workflow. Kindly check and let me know if I have to add more rows in excel is it the same procedure I need to follow. Sharing my Xaml file. Kindly let me know if its correct to get the Column Names.
GetCellColour (2).zip (3.5 KB)

[Anil_G (Anil Gorthi)]

@dutta.marina

I am not sure what you are talking…becaue hwo would columns increase to 40 to 50 roes/ do you measn 40 to 50 columns? if yes then the same code would work

cheers

[dutta.marina (rpa_06)]

@Anil_G

Ok got it . Now currently I got the out as C, D, E . Do I need to add again Column_name+ “1” to get the column names. This is where I am stuck now.