Generating Excel Chart in Word

[AAhrenst]

Hi

I am trying to add a excel chart from a word template which is causing me a lot of frustration. Code seems to be simple enough but for some reason I just cannot make it work. I got serveral problems:

1: cannot edit ChartArea.Width/Height. I get following message "Object doesn't support this function". The PlotArea.Width/Height I am able to set.
2: I am not able to set the series using cell row and column no reference ie. Range(cells(x,y),Cells(z,y). I really need this but for now I am limited to using following: .SeriesCollection(i).Values = "='Sheet1'!$N$2:$N$52"
3: Setting specific color to each line is a problem too:
- I have tried using: .SeriesCollection(i).Border.ColorIndex = xx (which seem to set all all lines the same color
- nothing happens on this code: .SeriesCollection(i).Interior.Color = RGB(xxx, yyy, zzz)

Any feedback will be much appriciated...

below is the code I am using:
-----------------------------------------------------------------
Sub createchart()


Dim WellSecurityChart As Chart
Dim ChartWorkSheet As Excel.Worksheet

Set WellSecurityChart = ActiveDocument.Shapes.AddChart.Chart
Set ChartWorkSheet = WellSecurityChart.ChartData.Workbook.WorkSheets(1)

With WellSecurityChart
.ChartType = xlXYScatterLinesNoMarkers
.PlotArea.Width = 300 ' number not important now
.PlotArea.Height = 400 ' number not important now
.ChartArea.Width = 400 ' number not important now
.ChartArea.Height = 500 ' number not important now
.SeriesCollection(3).Delete 'deleting default series as automatically setup on creation of chart
.SeriesCollection(2).Delete
.SeriesCollection(1).Delete
'....more setup


.SeriesCollection.NewSeries
With .SeriesCollection(1)
.Name = "='Sheet1'!$F$1"
.XValues = "='Sheet1'!$H$2:H$52" ' works but I would prefer to use Range(Cells(x,y,),(Cells(z,i))
.Values = "='Sheet1'!$F$2:$F$52" ' data added elsewhere in code
'.Border.ColorIndex = 3 'set all lines to red
'.Interior.Color = RGB(255, 0, 0) ' does not work
'.Format.Line.ForeColor.RGB = vbRed 'does not work

End With
'......adding more series....
End With
end sub

[JonPeltier]

Here are some ideas.

1. Chart area is fixed by the size of the containing shape. You need to then adjust the size of the parent of the chart.

With WellSecurityChart
.Parent.Width = 400
.Parent..Height = 500

2. You need to reference ranges properly:

.XValues = ChartWorkSheet.Range(ChartWorkSheet.Cells(2, 8), ChartWorkSheet.Cells(52, 8))

3. When Office 2007 came out, we gained a lot of colors (compared to the old Excel palette of 56 color indexes), and we lost a lot of control over the colors. The color object model is obscure, erratic, and buggy, and often the old syntax must be used. At least in 2010 you can record a macro and use it to guess at the required syntax, but you still need to unhide deprecated elements in the object browser and hack through the jungle.

I don't know why ColorIndex didn't work, except that 2007/2010 don't reliably use ColorIndex. In an XY chart, there's no Interior property. As soon as you try to use .Format.Line, strange and wonderful things start to happen. So...

With .SeriesCollection(1)
' both of these work:
.Border.Color = vbRed
.Border.Color = RGB(255, 0, 0)

[AAhrenst]

Thanks for the reply Jon

I managed to fix my problems with inserting the excel graphs into word.
Instead of inserting them directly into word I generate the graphs in excel and manipulate same before they are copied into word. To me this was a lot easier (and eaiser to lookup correct code) and it also sorted out the color problem.
Since my chart took almost 45 sec to update (even with hidden excel object) while importing DDE variables, I decided not to populate the excel sheet with data but instead used arrays. This did cut the time down to about 15 sec.

Thanks again