[SOLVED] - Excel - Get an array of cell colors

[golfinhu]

Hi all, fine?

i need a little help!

I need to get an array with the colors of the cells in the specified range!

 

i've tried this, but don't work!

 

Local $aCellColors = $oWorkbook.ActiveSheet.Usedrange.Columns("BK:BP").ColorIndex
MsgBox(0,'',IsArray($aCellColors))

 

anyone help me? thanks a lot!

Edited May 17, 2015 by golfinhu

[nitekram]

Just a guess...("BK1:BP255")?

[water]

i've tried this, but don't work!

​What do you mean by: Don't work? Could you please be a bit more specific?

[golfinhu]

​What do you mean by: Don't work? Could you please be a bit more specific?

​Look my generic code:

 

expandcollapse popup

#include <Array.au3>

$oExcel = ObjCreate("Excel.Application")
With $oExcel ; open new workbook
    .Visible = True
    .WorkBooks.Add
    .ActiveWorkbook.Sheets(1).Select()
EndWith

$sRange ="A1:G8"

With $oExcel.ActiveSheet.Range($sRange)
    .Select
    .HorizontalAlignment = -4108 ; center
    .ColumnWidth = 18
    ;.EntireColumn.AutoFit
EndWith

;Color Cells
Local $n = 1
For $i = 1 To 7
    For $j = 1 To 8
        $oExcel.ActiveSheet.Range(Chr(64 + $i) & $j).Interior.ColorIndex = $n
        $oExcel.ActiveSheet.Range(Chr(64 + $i) & $j).value = "Color " & $n
        $n+=1
    Next
Next

;Array of cell values:
Local $aValue = $oExcel.ActiveSheet.Range($sRange).value
_ArrayDisplay($aValue)

;Array of cell colors:
Local $aColors = $oExcel.ActiveSheet.Range($sRange).Interior.ColorIndex
If Not IsArray($aColors) Then
    MsgBox(0,'','failed to get array colors')
Else
    _ArrayDisplay($aValue)
EndIf

I can't get an color array like a data array!

[lorenkinzel]

Incomplete but:

;For $i = 1 To 7
    ;etc
Local $theRange = (Chr(64 + 2) & 2)
With $oExcel
    $theColor = .ActiveSheet.Range($theRange).Interior.ColorIndex
EndWith
Msgbox(0, "", $theColor)
;Next

 

[water]

As far as I know: ColorIndex (and other properties) only accepts or returns a single value.
I'm nor sure how ColorIndex behaves:

1. You only will get this single value when all cells of the Range have the same color, else you will get Null.
2. You get the value for the upper left cell of the Range

Which one is true needs to be tested.

[golfinhu]

Incomplete but:

;For $i = 1 To 7
    ;etc
Local $theRange = (Chr(64 + 2) & 2)
With $oExcel
    $theColor = .ActiveSheet.Range($theRange).Interior.ColorIndex
EndWith
Msgbox(0, "", $theColor)
;Next

 

​it's work, thanks a lot!!!

 

As far as I know: ColorIndex (and other properties) only accepts or returns a single value.
I'm nor sure how ColorIndex behaves:

1. You only will get this single value when all cells of the Range have the same color, else you will get Null.
2. You get the value for the upper left cell of the Range

Which one is true needs to be tested.

​i understand, thanks a lot for you explanation and help!

[water]