Read entire sheet to find a coloured cell

[WS_Chai]

Hi there,

I would like to run a bot that reads the entire excel sheet and return the cell address if a specific colour is detected in any cell within the sheet.

I have 2 questions:

1. What should i put under “cell” for Get Cell Color activity?
2. How can I get the address of the cell if the bot finds a cell in the color I want? (i.e. return the details on which row and which column)

[Vikas_M]

Hey @WS_Chai ,

1. Add a “For Each Row” activity to loop through each row of the DataTable.
2. Inside the “For Each Row” loop, add a “For Each” activity to loop through each column in the current row.
3. Inside the nested “For Each” loop, use the “Get Cell Color” activity to get the color of the current cell. You can use the “UiPathTeam.Excel.Extensions.Activities” package to access this activity, which provides advanced Excel automation capabilities.
4. Compare the color of the current cell with the specific color you want to detect.
5. If the color matches, you can get the cell address using the “Get Cell Address” activity.
6. Store the cell address in a list or a variable.
7. After processing all cells, you will have a list of cell addresses where the specific color is detected.

Hope it helps you!

[lrtetala (Lakshman Reddy)]

Hi @WS_Chai

Check on this

How to find out Color cell in excel

You must be doing something wrong - try like this: [image] [image]

[ManFrancko (Franck MONGO)]

Sure, I’d be happy to help you with that!

To achieve your goal, you would need to use a combination of activities provided by UiPath. Here’s a step-by-step guide:

1. Excel Application Scope Activity: This activity opens an Excel workbook and provides a scope for Excel Activities. When the execution of this activity ends, the specified workbook and the Excel application are closed. To configure it:In the “WorkbookPath” field, enter the path to your Excel file. For example, you might enter something like “C:\Users\username\Documents\data.xlsx”.

2. Read Range Activity: This activity reads the value of an Excel range and stores it in a DataTable. To configure it:In the “SheetName” field, enter the name of the sheet you want to read from. For example, “Sheet1”. In the “Range” field, enter the range of cells you want to read. To read the entire sheet, you can leave this field empty. In the “Output” > “DataTable” field, create a variable (let’s call it “data”) to store the data from the Excel sheet.

3. For Each Row Activity: This activity enables you to process each row of the DataTable. To configure it:In the “For Each Row” field, input your variable “data” created in the previous step.Inside the “Body” of the For Each Row, add a For Each Activity to iterate through each item (cell) in the row. Set the “TypeArgument” to “Object” and the “Values” to row.ItemArray. This will go through each cell in the row.

4. Get Cell Color Activity (inside the nested For Each activity): This activity retrieves the background color of a specified cell. To configure it:In the “Cell” field, input the current index of the For Each loop (you can obtain this by using an Assign activity before the Get Cell Color activity to assign index = data.Rows.IndexOf(row)+2 and column = Array.IndexOf(row.ItemArray, item)+2, then in the cell field you can input column.ToString + index.ToString). In the “Output” > “Color” field, create a variable (let’s call it “color”) to store the color of the cell.

5. If Activity (inside the nested For Each activity): This activity enables you to make a decision based on a condition. To configure it:In the “Condition” field, compare the “color” variable with the color you’re looking for. For example, you might enter color = System.Drawing.Color.Red to check if the cell color is red. If the condition is true (the cell color is the color you’re looking for), in the “Then” section, you can use a Write Line Activity or Log Message Activity to output the address of the cell. You can get the address by concatenating the row index and column index (for example, column.ToString + index.ToString).

I hope this helps! If you have any other questions or if something is unclear, please let me know!

[lrtetala (Lakshman Reddy)]

Hi @WS_Chai

Try this

I hope it helps!!

[WS_Chai]

Hi @ManFrancko,

Really appreciate your step-by-step guidance, the details are very useful in helping to understand better.

I tried your approach but got an error. Would you mind advising if there’s anything that I did not follow correctly?

[supriya117 (Supriya Allada)]

Hi @WS_Chai

Try this:

1. Use the “Read Range” activity to read the entire Excel sheet into a DataTable.
2. Use a “For Each Row” loop to go through each row in the DataTable.
3. Use another “For Each” activity to go through each column in the current row.
4. Use the “Get Cell Color” activity to check the color of each cell. Set the “SheetName” and to get the cell address dynamically, you can use :
   Convert.ToChar(columnIndex + 64) + (rowIndex + 1).ToString(). This converts the column index to the corresponding Excel column letter.
5. Check the color returned by the “Get Cell Color” activity against the color you want to detect. Store the cell address in a variable.

[ManFrancko (Franck MONGO)]

I’m sorry to hear that you’re encountering an error. This error usually occurs when the cell you’re trying to read is not within the range of your Excel sheet.

1. Check Your Excel File: Open your Excel file and check if cell 22 is within the range of your data. Make sure there are no merged cells in your data, as this can sometimes cause issues.

2. Check Your For Each Activities: Inside your For Each Row and For Each activities, make sure you’re correctly iterating through each row and each item in the row.Confirm that you’re correctly calculating the index and column in your Assign activity before the Get Cell Color activity. Remember, the index = data.Rows.IndexOf(row)+2 and column = Array.IndexOf(row.ItemArray, item)+2.

3. Check Your Get Cell Color Activity: In the “Cell” field, ensure that you’re correctly inputting the cell address. The cell address should be in the format “column.ToString + index.ToString”.Example: If the column is 1 and the index is 22, the cell address should be “A22”.

If you’ve checked all of these and you’re still encountering the error, it would be helpful if you could provide more information about your workflow and your Excel data. This will allow me to provide a more accurate solution.

[WS_Chai]

Hi @ManFrancko

I’ve checked cell 22 and there is no merged cell.
I am guessing that it is relating to to the 2 assign acitivities for “index” and “column”?

When I changed the assign values to
index = data.Rows.IndexOf(row)+1 and column = Array.IndexOf(row.ItemArray, item)+1， the error becomes cell 11

When I changed the assign values to
index = data.Rows.IndexOf(row)+3 and column = Array.IndexOf(row.ItemArray, item)+3， the error becomes cell 33

I would like to read the entire sheet and all cells with values.
Do you mind to advise how can I rectify this issue?

[WS_Chai]

Hi @supriya117

May I ask where should i include this to get the cell address? Do I use assign activity?

Convert.ToChar(columnIndex + 64) + (rowIndex + 1).ToString() .

[supriya117 (Supriya Allada)]

@WS_Chai

You can directly use in index property of get cell color activity or in assign activity and pass that string variable in index property.

[ManFrancko (Franck MONGO)]

Sure, I’d be happy to assist you in resolving this issue!

The error message “Get Cell Color: could not read cell color” typically indicates that UiPath is trying to access a cell that doesn’t exist in your Excel sheet.

Let’s try a different approach to ensure we’re only trying to access cells that exist:

Firstly, verify the total number of rows and columns in your Excel sheet.

• You can determine the total number of rows in your DataTable by using the expression data.Rows.Count.
• Similarly, you can determine the total number of columns by using the expression data.Columns.Count.

Secondly, adjust your For Each loops to ensure you’re only iterating through existing cells.

• For the outer For Each Row loop, make sure your “Values” is set to data, which is your DataTable variable.
• For the inner For Each loop (that iterates over each cell in a row), make sure your “Values” is set to row.ItemArray.

Thirdly, in the Get Cell Color activity, make sure you’re correctly specifying the cell to get the color from.

• The “Cell” field should be set to a string that represents the cell’s address in the Excel sheet.
• Excel uses a “column-row” format for cell addresses (like “A1”, “B2”, etc.). So, you need to convert the column index (which is a number) to the corresponding column letter.
• You can use an Assign activity to create a new string variable, let’s call it cellAddress. Use the following expression to convert the column number to a letter and combine it with the row number to get the cell address: Convert.ToChar(65 + columnIndex).ToString + (rowIndex + 1).ToString.
• Replace columnIndex and rowIndex with the variables you’re using to track the current index in your For Each loops.

Fourthly, re-run your workflow and observe if the error still persists.

If you’re still encountering issues after following these steps, could you please provide more details about your Excel file (like the total number of rows and columns) and your workflow (like screenshots of your activities and their properties)? This would help me provide a more accurate solution.