Excel OLE automatisation: modify colour palette

dpechacek - Wed Aug 19 09:15:21 EDT 2009
Here you go. :)

{code}/*
* Sets the cell's Background color to the specified color.
* Referenced Enhanced Export to Excel for this function.
*/
bool setCellBackgroundColor(int rowNum, int col, int colorValue) {
OleAutoObj objInterior = null;
bool successful = false;

// get a handle to the cell.

if(!getCell(rowNum, col)) {
errorBox("Null cell reference");
}
else {
if(!checkResult(oleGet(objCell, cPropertyInterior, objInterior))) {
errorBox("Failed to get handle on cell's Interior Property");
}
else {
if(!checkResult(olePut(objInterior, cPropertyColorIndex, colorValue))) {
errorBox("Failed to set cell's Color Property");
}
else {
successful = true;
}
}
}

closeIfNonNull(objInterior);

return(successful);
}

AAI Services, Textron
dpechacek@aai-textron.com
David.Pechacek@gmail.com

dpechacek - Wed Aug 19 09:20:40 EDT 2009

Sorry Peter. Accidentally hit the tab key and then enter which ended up posting the message.

Try number 2. You can add this to my ExcelFunctions on Kevin Murphy's site. It might already be in there. Some of the functions called are there.
 

// here are the possible color values I use.  Basically the whole drop down palette in Excel
// this goes in my ole.inc file or you can put them wherever you like.
//--------------------Microsoft Excel Color Values------------------
const int cColorAutomatic =      0
const int cColorBlack =          1
const int cColorBrown =          53
const int cColorOliveGreen =     52
const int cColorDarkGreen =      51
const int cColorDarkTeal =       49
const int cColorDarkBlue =       11
const int cColorIndigo =         55
const int cColorGrey80 =         56
const int cColorDarkRed =        9
const int cColorOrange =         46
const int cColorDarkYellow =     12
const int cColorGreen =          10
const int cColorTeal =           14
const int cColorBlue =           5
const int cColorBlueGray =       47
const int cColorGray50 =         16
const int cColorRed =            3
const int cColorLightOrange =    45
const int cColorLime =           43
const int cColorSeaGreen =       50
const int cColorAqua =           42
const int cColorLightBlue =      41
const int cColorViolet =         13
const int cColorGray40 =         48
const int cColorPink =           7
const int cColorGold =           44
const int cColorYellow =         6
const int cColorBrightGreen =    4
const int cColorTurquoise =      8
const int cColorSkyBlue =        33
const int cColorPlum =           54
const int cColorGray25 =         15
const int cColorRose =           38
const int cColorTan =            40
const int cColorLightYellow =    36
const int cColorLightGreen =     35
const int cColorLightTurquoise = 34   
const int cColorPaleBlue =       37
const int cColorLavender =       39
const int cColorWhite =          2
const int cColorxlNone =         -4142
 
/*
 * Sets the cell's Background color to the specified color.
 * Referenced Enhanced Export to Excel for this function.
 */
bool setCellBackgroundColor(int rowNum, int col, int colorValue) {
   OleAutoObj objInterior = null;
   bool successful = false;
   
   // get a handle to the cell
   if(!getCell(rowNum, col)) {
      errorBox("Null cell reference");
   }
   else {
      if(!checkResult(oleGet(objCell, cPropertyInterior, objInterior))) {
         errorBox("Failed to get handle on cell's Interior Property");
      }
      else {
         if(!checkResult(olePut(objInterior, cPropertyColorIndex, colorValue))) {
            errorBox("Failed to set cell's Color Property");
         }
         else {
            successful = true;
         }
      }
   }
   
   closeIfNonNull(objInterior);
   
   return(successful);
}

AAI Services, Textron
dpechacek@aai.textron.com
David.Pechacek@gmail.com

Peter_Albert - Wed Aug 19 09:58:11 EDT 2009
Hi David,

thanks for responding, however, I was probably not clear enough in my OP: the function you attached I have already found in your script and successfully applied, but I would like to change the actual colour which is displayed, e.g. a cell which has colour index 46 should have the RGB values (126, 224, 17) instead of the default orange. As one can not apply RGB values to cell backgrounds directly, you have to modify the palette (not the cell background colour index!). Doing so in a macro is straight forward, but I can't get it to work with OLE.

Regards,

Peter

dpechacek - Wed Aug 19 15:15:16 EDT 2009
Send me the macro code and I'll see if I can get it to work when I have a spare minute.

AAI Services, Textron
dpechacek@aai.textron.com
David.Pechacek@gmail.com

Peter_Albert - Thu Aug 20 03:39:15 EDT 2009
The macro code is actually just one line per colour, e.g.

ActiveWorkbook.Colors(17) = RGB(0, 102, 204)

Regards,

Peter