>LS,
>
>Our application needs to spew management information and we export it to Excel to make lists and graphs. This has worked flawless until now.
>What happens is that I use the following two lines to set the source for the graph (everything in the list is just fine)
>
>oChart.chart.SeriesCollection.Add(oWorkSheet_Cijf.Range("C5:F" + lcTotalLine), 2, .T.)
>
>oChart.chart.SeriesCollection.Add(oWorkSheet_Cijf.Range("A5:A" + lcTotalLine), 2, .F., .T., .T.)
>
>Where C5:Fx hold the numbers and A5:Ax hold the (horizontal)labels.
>This used to be just fine, but after some work on the code (actually the SQLs that generate the data) something goes wrong.
>The content of the list(oWorkSheet_Cijf) is still oke, but when asking for the sourcedata of the graph it tells me it's looking at the range D5:Fx and A5:Ax. Therefor the graph is missing data.
>
>What gives? I can correct the problem by setting the range to B5:Fx, that gives a graph range of C5:Fx, but that doesn't add up. It's plain ugly and not maintainable.
>
>What made Excel 'decide' to change my ranges, and how do I get it back on track?
>
>Any help is greatly appreciated.
>
>Update: I've found that the problem arises when there's only one row. That is, one row of data. In practice this still comes down to 3 rows. One for the labels, one with 'real' data plus one with totals. It seems when the selection is smaller that 4 rows the problem appears. Anyone ever seen this behaviour. I get the feeling it's a bug in the automation, any known workarounds?

Geert,
Based on your update it makes sense. Why don't you directly SetSourceData to new range. Based on RowCount your automation code would calculate the label and data ranges, union them and set as sourcedata. Or if you want it to do via seriescollection you might as well query the label and data positions first instead of hardcoding.

ie: chart.dbf content looks like :

values,count
"AU",6
"NE",4
"NW",4
"UK",4
"TR",9
"US",3

Based on this data there was a precreated chart on Excel (data starting from A3) :

Region	Count	Percentage
AU	6	33.33333333
NE	4	22.22222222
NW	4	22.22222222
UK	4	22.22222222

Total	18	100

Chart sourcedata :
=Sheet1!$A$4:$A$7,Sheet1!$C$4:$C$7

When data changes there are 2 problems, percentages should be recalculated and chart set. Below code does that (assumes columns A:C is reserved for only data from VFP and chart is on the same sheet located right of data) :

#Define SOURCETABLE Fullpath("chart.dbf")
lcXLS = Sys(5)+Curdir()+'chart.xls'
Use (SOURCETABLE)
* Or
*Select * from .... into cursor xx

Local lcTemp, lnRows, lnCols, lnHeaderRow, lnTotalsRow, lcHeader

lnRows = Reccount()
lnCols = Fcount()
lnHeaderRow = 3 && Start data at row 3
lnTotalsRow = lnHeaderRow + lnRows + 1 + 1 && One blank
lcTemp = Sys(2015)+'.tmp'
lcHeader = 'Region'+Chr(9)+'Count'+Chr(9)+'Percentage'+Chr(13)+Chr(10)
lcGraphRange = ;
  '_'+Transform(lnHeaderRow+1)+ ;
  ':_'+Transform(lnHeaderRow+lnRows)

Copy To (lcTemp) Type Delimited With Tab
_Cliptext = lcHeader+Filetostr(lcTemp)
Erase (lcTemp)

Local oExcel As Excel.Application
oExcel=Createobject('Excel.Application')
With oExcel
  .Workbooks.Open(lcXLS)
  With .ActiveWorkBook.ActiveSheet
    .Range('A'+Transform(lnHeaderRow)).Select
    .Paste
    .Range('A'+Transform(lnTotalsRow)).Value = 'Total'
    .Range('B'+Transform(lnTotalsRow)).Formula = ;
      '=SUM(B'+Transform(lnHeaderRow+1)+ ;
      ':B'+Transform(lnHeaderRow+lnRows)+')'
    .Range('C'+Transform(lnTotalsRow)).Formula = ;
      '=SUM(C'+Transform(lnHeaderRow+1)+ ;
      ':C'+Transform(lnHeaderRow+lnRows)+')'
    .Range('C'+Transform(lnHeaderRow+1)+ ;
      ':C'+Transform(lnHeaderRow+lnRows)).Formula = ;
      '=(R[0]C[-1]/R'+Transform(lnTotalsRow)+'C[-1]%)'
    .Range('A1').Activate

    loRange = .Application.Union( ;
      .Range(Strtran(lcGraphRange,'_','A')), ;
      .Range(Strtran(lcGraphRange,'_','C')) ) && Source is in cols Ax:Ay, Cx:Cy

    With .ChartObjects(1).Chart
      .SetSourceData(loRange)
    Endwith

  Endwith
  .Visible = .T.
Endwith

Cetin