How to read Excel values ​​including color information

[sumire]

Hello,

I have a Excel table like this:

I would like to get the values ​​of this table as collection, including color information.
Like this:

I'm looking for a suitable method.
If you know of a better way, please let me know.

[Tejaskumar_Darji]

Hi, we did some different solutions that involved excel with font color. You will have to build a custom excel filter that can apply a filter based on color and pass input color hex code.

[sumire]

Hello @Tejaskumar_Darji ,

Thank you for your reply.
What I know is that I use "Get Background Color" action inside "MS Excel  Booster" VBO to get the color information.
https://digitalexchange.blueprism.com/dx/entry/78038/solution/spgmi--ms-excel-booster 
Is there any other way you are suggesting? If you know of a new method please let me know.

I need to process an Excel sheet that uses color to indicate data types, and I'm having a really hard time.
I don't want them to create such an Excel in the first place... 😞

[faheemsd]

Dear @sumire 

Could you please confirm if the color values are fixed in your excel or is it changing randomly?

[Mohamad_747]

Hi,

You can add a new action that can give you the RGB Codes

1. Go to the object studio SGPMI MS Excel  Booster

2 Add a new action and name it for example (Get font color)

3 Add this code 

Try

dim C as long
dim R as long
dim G as long
dim B as long

C = GetWorksheet(handle, Workbook, Worksheet ).Range(cellref,cellref).DisplayFormat.Font.Color

R = C Mod 256
G = C \ 256 Mod 256
B = C \ 65536 Mod 256
GetCellRGB = "R=" & R & ", G=" & G & ", B=" & B

Catch ex as exception

exceptionMessage = ex.message

End Try

for Input provide this 

For output this : 

And you will have an output like this for example if your cell font is in red, you will have  <R=255, G=0, B=0> 

Hope it will help you

[sumire]

Hello @faheemsd ,

Thank you for reply.

Color values are fixed.
Each value is color-coded by type. For example, the large size is blue, the medium size is yellow, and the small size is red.

[sumire]

Hello @Mohamad_747 ,

Thank you for your reply.

I also wrote a similar article. (Sorry, I wrote this article in Japanese, so please use machine translation to read it)
https://qiita.com/Sumire_Neko/items/e119bbbdaa9c57e9e930 
Since there are hundreds of target cells, reading the color information by moving each cell one by one is time-consuming.
Therefore, I'm looking for a faster way to do this.

[Mohamad_747]

Hello @sumire 

You need to create a CUSTOM Action Like this for example : 

For inputs you put : 

Notice : 

column_where_color_is_present is the index column. In your case it's column A, so you put 1

column_result is where you stock the result so you can put  2 or 3 depends on where you want to stock your values.

For Ouptut  : 

And inside the code you put this 

Try
Dim C As Long
Dim R As Long
Dim G As Long
Dim B As Long
Dim i As Long

For i = start_row To last_Row

If Not IsDBNull(GetWorksheet(handle, Workbook, Worksheet).Cells(i, column_where_color_is_present).Value) AndAlso Len(GetWorksheet(handle, Workbook, Worksheet).Cells(i, column_where_color_is_present).Value) > 0 Then
C = GetWorksheet(handle, Workbook, Worksheet).Cells(i, column_where_color_is_present).Font.Color
R = C Mod 256
G = C \ 256 Mod 256
B = C \ 65536 Mod 256
Dim GetCellRGB As String = "R=" & R & ", G=" & G & ", B=" & B
GetWorksheet(handle, Workbook, Worksheet).Cells(i, column_result).Value = GetCellRGB
Else
GetWorksheet(handle, Workbook, Worksheet).Cells(i, column_result).Value = ""
End If

Next i

Catch ex As Exception
exceptionMessage = ex.Message
End Try

You need to make a loop to find your wanted results, we make the loop inside the vbo 🙂

Your process should look like this 

Your CUSTOM VBO inputs in the process like this : 

Ensure that you dont have empty value inside your column A 

At the end you will have this results 

Please mark as best answer if my code helped you 🙂

regards

[faheemsd]

Dear @sumire 

We can achieve this with the help of macro code.

Create an Excel template with the macro enabled workbook and copy all your original excel data into the Macro Excel template and then Run the Macro with the help of Blue Prism. output will be created in the Sheet2 and you can read the Sheet2 data as a collection in Blue Prism.
My Excel Data

My Macro Name: GetFontColorsInColumnA

After running the above Macro output will be generated in Sheet2

Macro Code:

Function GetColorName(colorValue As Long) As String
' This function converts the font color value to a known color name
' You can add more color mappings if needed

Select Case colorValue
Case RGB(0, 0, 0)
GetColorName = "Black"
Case RGB(255, 255, 255)
GetColorName = "White"
Case RGB(255, 0, 0)
GetColorName = "Red"
Case RGB(0, 255, 0)
GetColorName = "Green"
Case RGB(0, 0, 255)
GetColorName = "Blue"
Case RGB(255, 255, 0)
GetColorName = "Yellow"
Case RGB(255, 165, 0)
GetColorName = "Orange"
Case RGB(128, 0, 128)
GetColorName = "Purple"
Case RGB(255, 192, 203)
GetColorName = "Pink"
Case RGB(128, 128, 128)
GetColorName = "Gray"
Case RGB(192, 192, 192)
GetColorName = "Silver"
Case RGB(0, 128, 0)
GetColorName = "Dark Green"
Case RGB(0, 128, 128)
GetColorName = "Teal"
Case RGB(128, 0, 0)
GetColorName = "Maroon"
Case RGB(128, 128, 0)
GetColorName = "Olive"
Case RGB(0, 0, 128)
GetColorName = "Navy"
Case RGB(75, 0, 130)
GetColorName = "Indigo"
Case RGB(255, 69, 0)
GetColorName = "Orange Red"
Case RGB(255, 20, 147)
GetColorName = "Deep Pink"
Case RGB(139, 69, 19)
GetColorName = "Saddle Brown"
Case RGB(210, 105, 30)
GetColorName = "Chocolate"
Case RGB(245, 245, 220)
GetColorName = "Beige"
Case RGB(220, 20, 60)
GetColorName = "Crimson"
Case Else
GetColorName = "Other"
End Select
End Function

Sub GetFontColorsInColumnA()
Dim wsInput As Worksheet
Dim wsOutput As Worksheet
Dim cell As Range
Dim fontColorName As String
Dim fontColorValue As Long
Dim lastRow As Long
Dim outputRow As Long

' Set the input and output worksheets
Set wsInput = ThisWorkbook.Sheets("Sheet1")
On Error Resume Next
Set wsOutput = ThisWorkbook.Sheets("Sheet2")
If wsOutput Is Nothing Then
Set wsOutput = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
wsOutput.Name = "Sheet2"
End If
On Error GoTo 0

' Clear any existing data in the output sheet
wsOutput.Cells.Clear

' Write headers to the output sheet
wsOutput.Cells(1, 1).Value = "Name"
wsOutput.Cells(1, 2).Value = "Font Color"

' Get the last row with data in the input sheet
lastRow = wsInput.Cells(wsInput.Rows.Count, "A").End(xlUp).Row

' Iterate through each cell in column A of the input sheet
outputRow = 2 ' Start from row 2 to skip header
For Each cell In wsInput.Range("A2:A" & lastRow)
fontColorValue = cell.Font.Color

' Determine the font color name
fontColorName = GetColorName(fontColorValue)

' Write the name and font color name to the output sheet
wsOutput.Cells(outputRow, 1).Value = cell.Value
wsOutput.Cells(outputRow, 2).Value = fontColorName

outputRow = outputRow + 1
Next cell
End Sub

Please use the above macro code in the excel workbook.

Open the Excel work book with Blue Prism and Run the Macro as shown in below

Please try this and it will give the the response with in seconds and let me know if you have any doubts on this

---

@sumire wrote:

Hello,

I have a Excel table like this:

I would like to get the values ​​of this table as collection, including color information.
Like this:

I'm looking for a suitable method.
If you know of a better way, please let me know.se add this macro code in the Excel workbook

[faheemsd]

Dear @sumire 

We can achieve this with the help of the macro.

Create a Excel template with Macro Enabled workbook, copy your original excel data into the Macro Enabled workbook and the run the macro which will give the results in Sheet2
Sample Excel Data

Macro name and Macro code: GetFontColorsInColumnA

Function GetColorName(colorValue As Long) As String
' This function converts the font color value to a known color name
' You can add more color mappings if needed

Select Case colorValue
Case RGB(0, 0, 0)
GetColorName = "Black"
Case RGB(255, 255, 255)
GetColorName = "White"
Case RGB(255, 0, 0)
GetColorName = "Red"
Case RGB(0, 255, 0)
GetColorName = "Green"
Case RGB(0, 0, 255)
GetColorName = "Blue"
Case RGB(255, 255, 0)
GetColorName = "Yellow"
Case RGB(255, 165, 0)
GetColorName = "Orange"
Case RGB(128, 0, 128)
GetColorName = "Purple"
Case RGB(255, 192, 203)
GetColorName = "Pink"
Case RGB(128, 128, 128)
GetColorName = "Gray"
Case RGB(192, 192, 192)
GetColorName = "Silver"
Case RGB(0, 128, 0)
GetColorName = "Dark Green"
Case RGB(0, 128, 128)
GetColorName = "Teal"
Case RGB(128, 0, 0)
GetColorName = "Maroon"
Case RGB(128, 128, 0)
GetColorName = "Olive"
Case RGB(0, 0, 128)
GetColorName = "Navy"
Case RGB(75, 0, 130)
GetColorName = "Indigo"
Case RGB(255, 69, 0)
GetColorName = "Orange Red"
Case RGB(255, 20, 147)
GetColorName = "Deep Pink"
Case RGB(139, 69, 19)
GetColorName = "Saddle Brown"
Case RGB(210, 105, 30)
GetColorName = "Chocolate"
Case RGB(245, 245, 220)
GetColorName = "Beige"
Case RGB(220, 20, 60)
GetColorName = "Crimson"
Case Else
GetColorName = "Other"
End Select
End Function

Sub GetFontColorsInColumnA()
Dim wsInput As Worksheet
Dim wsOutput As Worksheet
Dim cell As Range
Dim fontColorName As String
Dim fontColorValue As Long
Dim lastRow As Long
Dim outputRow As Long

' Set the input and output worksheets
Set wsInput = ThisWorkbook.Sheets("Sheet1")
On Error Resume Next
Set wsOutput = ThisWorkbook.Sheets("Sheet2")
If wsOutput Is Nothing Then
Set wsOutput = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
wsOutput.Name = "Sheet2"
End If
On Error GoTo 0

' Clear any existing data in the output sheet
wsOutput.Cells.Clear

' Write headers to the output sheet
wsOutput.Cells(1, 1).Value = "Name"
wsOutput.Cells(1, 2).Value = "Font Color"

' Get the last row with data in the input sheet
lastRow = wsInput.Cells(wsInput.Rows.Count, "A").End(xlUp).Row

' Iterate through each cell in column A of the input sheet
outputRow = 2 ' Start from row 2 to skip header
For Each cell In wsInput.Range("A2:A" & lastRow)
fontColorValue = cell.Font.Color

' Determine the font color name
fontColorName = GetColorName(fontColorValue)

' Write the name and font color name to the output sheet
wsOutput.Cells(outputRow, 1).Value = cell.Value
wsOutput.Cells(outputRow, 2).Value = fontColorName

outputRow = outputRow + 1
Next cell
End Sub

Please add the above code in the Macro Enabled worksheet

My output:

Try with Blue Prism to generate the output in Sheet2 and read as Collection

Please try the above approach and let me know if you need any help.