Changing Cell Colours in Excel using Win32::OLE?

[Jiggerman]

Alright Folks,

My sensational project for interrogating and writting to Excel is nearly finnished,......But I would like to know how to change the properties of the cells, Background colour, Border, Formatting etc I assume this is done something like this:

$newSheet->Cells(1,1)->{**WhatIWantToChange**} = "**WhatIWantItChangedTo**";
or

$newSheet->Range("A1:F6&quot -> {**WhatIWantToChange**} = ['**WhatIWantItChangedTo**'];

I've read in CPAN that the Cell properties are linked to Visual Basic code, but nothing I have tryed seems to work.

Does anyone have any idea's, or know where I can get a list of the property names.

Thanks very much

 

[goBoating]

I don't have a specific answer except that I use the Object Browser in the Visual Basic Editor that is embedded in EXCEL.

Open EXCEL.
Go to 'Tools'->'Macros'->'Visual Basic Editor' and open the object browser there. That will show you what is avaliable. You can find what is available and then implement it in your Perl code.

It is a little tedious, but, I've been successful in that past using this approach.

'hope this helps

If you are new to Tek-Tips, please use descriptive titles, check the FAQs, and beware the evil typo.

 

[Jiggerman]

Right I sorted it, the code is

$workSheet->Range("A1:A6&quot->Interior->{ColorIndex} =XX;

There are 56 colour index's so change XX for a number between 0 and 56.

There is a away to directly set the colour using the VB ->color(RGB); (RGB -Red-Green-Blue colour index's) I havn't tryed this nor checked the syntax, but aparently that's how it's done.

For help with Win32::OLE, check Excels own programming Help files, and convert the VB Command into a Perl Command, very Simple(ish).

Basically it's done along the lines of
$Object ->{Property} = $OtherObject;
corresponds to the VB command Set Object.Property = OtherObject.

(This is an extract from the Win32::OLE definition on CPAN)

ok, I think I'm spent for the day!

 

[fishiface]

The module Spreadsheet::WriteExcel (which I use to create Excel documents on Linux platforms) includes code for custom colors. It might be worth looking at the source.

Yrs,


fish

"As soon as we started programming, we found to our surprise that it wasn't as easy to get programs right as we had thought. Debugging had to be discovered. I can remember the exact instant when I realized that a large part of my life from then on was going to be spent in finding mistakes in my own programs."
--Maurice Wilk