Excel 2007 question

[ChrisJRutter]

Hi

I am building a 2d line chart in Excel 2007.

Can anyone explain why the following code generates the message "an error occurred while accessing property/method 'line' - unknown name".

chExcel:ActiveChart:SeriesCollection(1):Line:Color = 7.
(I have also tried replacing the 7 with a color descriptor like "Green")

whereas the following works fine.
chExcel:ActiveChart:SeriesCollection(1):Smooth = True.

I have searched as many VB forums/websites as I can find, many of which refer to the exact syntax I am having trouble with. VB help in Excel 2007 doesn't shed any light either. Any assistance would be greatly appreciated. Thanks in advance

 

[joey.jeremiah]

Have you tried recording a macro, to see what objects and attributes are being used ?

I'm guessing it used to work, right ?

We need to start planning for OpenXML. ActiveX just isn't a good solution.

 

[ChrisJRutter]

Don't know if it use to work as pre-Office 2007 we are still using DDE to Excel. We have been for 10+ years and if it ain't broken, why fix it... Office 2007 has forced us into this development as certain Macro 4.0 functions have been dropped.

Yes, I have tried recording a macro to see the syntax however annoyingly you don't seem to get chart properties. e.g. If I select a line chart, select the 2nd series then set the color to say yellow, the macro only gives me the following:

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(2).Select

Not sure why? If it just gave me the next line all would revealed. Perhaps it doesn't as the actual property setting is done completely differently in 2007.

 

[joey.jeremiah]

just a stab but how about this -

ActiveSheet:ChartObjects("Chart 1"):Activate.
ActiveChart:SeriesCollection(2):Select.
ExcelApplication:Selection:ColorIndex = 10.

you already know that but don't forget to release any com-handles.

i'm with you 100% on if it ain't broken don't fix it and i certainly didn't mean you should rewrite your application.

i was talking about ooxml ( openxml ) in a broader sense, looking ahead. good luck !

 

[ChrisJRutter]

The ColorIndex property returns an 'unknown name' error...

Grrrr..

Thanks for the suggestion though!

And yes I am being a tidy programmer and releasing all my com handles.

 

[joey.jeremiah]

and ... Selection:Smooth = true does work ?

 

[ChrisJRutter]

yes it works prefectly...

 

[joey.jeremiah]

Looks like the object model was changed.

Did some digging @msdn, how about -

ActiveSheet:ChartObjects("Chart 1"):Activate.
ActiveChart:SeriesCollection(2):Select.
ExcelApplication:Selection:Interior:Color = 10.

 

[ChrisJRutter]

Good news and bad news.

Good news is this doesn't produce an error

Bad news is it doesn't change the color of the line

I wonder if a line doesn't actually have an interior color - maybe this is more relevant to say pie charts?

Thanks for trying to help out btw. :blush:

 

[joey.jeremiah]

here's a starting point,

http://msdn2.microsoft.com/en-us/library/bb178378.aspx

good luck !

 

[ChrisJRutter]

The correct syntax for changing a line color is:

chChart:SeriesCollection(n):Border:ColorIndex = <color>

For anyone interested, changing the color of a pie segement is:

chChart:SeriesCollection(1)oints(n):Interior:ColorIndex = <color>.