[python-win32] Cannot assign values to the Excel Colors property

[Joe Goldthwaite]

Hello again,

I got it working.  I first created a macro in my Excel template called
SetColor;

   Sub SetColor(Workbook, c1, c2, c3, c4, c5, c6, c7, c8, c9)

       'Set the first nine colors
       With Workbook
           .Colors(1) = c1
           .Colors(2) = c2
           .Colors(3) = c3
           .Colors(4) = c4
           .Colors(5) = c5
           .Colors(6) = c6
           .Colors(7) = c7
           .Colors(8) = c8
           .Colors(9) = c9
       End With

       'Remove all the macros from the passed workbook
       For x = Workbook.VBProject.vbcomponents.Count To 1 Step -2
           On Error Resume Next
           Workbook.VBProject.vbcomponents.Remove
Workbook.VBProject.vbcomponents(x)
       Next

   End Sub

Next, I just execute this line in Python;

XLApp.Run("ISRecapTemplate.xls!SetColor", self.XLWorkbook, C[0], C[1], C[2],
C[3], C[4], C[5], C[6], C[7], C[8])

C is a list of the colors that I want to use to set the first nine Excel
pallet entries.  The loop in the SetColor subroutine removes all macros from
the workbook.  I tested it out and it worked!  I get the correct color
settings and there are no macro's in the resulting workbook.

It isn't pretty but it does work.  I'd still like to find out how to set the
colors directly.  This is a pretty kludgy way of doing it.

Graham,

I tried the SetColors(1, xxx) but I get an error message that there is not
SetColors attribute.  I looked for it in my Excel VBA language reference and
couldn't find it there either.

Thanks for the help everyone.

Joe Goldthwaite


-----Original Message-----
From: Graham Bloice [mailto:graham.bloice at trihedral.com]
Sent: Tuesday, August 24, 2004 9:14 AM
To: joe at goldthwaites.com
Cc: python-win32 at python.org
Subject: RE: [python-win32] Cannot assign values to the Excel Colors
property


> Thanks Niki!
>
> Nice try :).  I got an error message "TypeError: 'tuple' object is not
> callable".  I would think that the only way to do this would be to use the
> list assignment syntax.  I guess the COM interface doesn't support it
> though.  I'm sure there must be a way to do it.  I'm going to try
> writting a
> VB macro and then see if I can get Python to execute it. Then I'll delete
> the macro before I save the workbook.  It's kind of a round about
> way but it
> should get the job done until I can find out the right way to do it.
>
> Joe Goldthwaite
>
> -----Original Message-----
> From: Niki Spahiev [mailto:niki at vintech.bg]
> Sent: Tuesday, August 24, 2004 12:54 AM
> To: joe at goldthwaites.com
> Cc: Python-win32 at python.org
> Subject: Re: [python-win32] Cannot assign values to the Excel Colors
> property
>
>
> Joe Goldthwaite wrote:
> > Here's another strange one.  Automating Excel with Python is turning out
> to
> > be a little more difficult than I thought.  I'm trying to set the color
> > property of the workbook object. In VB it goes like this;
> >
> > Workbook.Colors(1) = &H254A70
> >
> > When I try doing this in Python, It complains about the syntax and tells
> me
> > I can't assign a value to a function call.  I also tried this;
> >
> > Workbook.Colors[1] = 0xH254A70
> >
> > But it tells me "TypeError: object doesn't support item
> assignment".  I'm
> > sure there's a way to do it but the syntax eludes me.  Anyone have any
> > ideas?
> >
>
> Maybe workbook.Colors(1, 0x223344) ?
>
> Niki Spahiev


try workbook.SetColors(1, xxx)

The Colors property of the Workbook interface is an indexed property, and
the Python COM support has to provide Setxxx and GetXXX methods to support
the indexing.  Have a look in the COM MakePy generated file for more info.

Graham