Board index » delphi » Excel Chart access and newseries (newbie)

Excel Chart access and newseries (newbie)

<<Ralph Gottschalg:
ChartSheet:=ExcelWorkbook.Worksheets[AChartPageName] as
_Worksheet;

Quote

Charts and worksheets aren't the same, even though the
chart may be a sheet. Try this:

var
  ChartSheet: _Chart;
begin
  ChartSheet :=
    ExcelWorkbook.Sheets[AChartPageName] as _Chart;

--
Deborah Pate (TeamB) http://delphi-jedi.org

  Use Borland servers; TeamB don't see posts via ISPs
  http://www.borland.com/newsgroups/genl_faqs.html

 

Re:Excel Chart access and newseries (newbie)


<<Ralph Gottschalg:
Ch.SetSourceRange.. 'incompatible types: 'Olevariant' and
'Range''.

Quote

Your Rnge variable should be of Range type, like this:

var
  Rnge: Range;
  NewSeries, SC: OleVariant;
..
  Rnge :=  WS.Range['B2', 'D10'];
  ExcelChart.ChartType := xlXYScatter;
  ExcelChart.SetSourceData(Rnge, xlColumns);

I think that should cure the problem, but I don't have the
means at hand to test it just now - let me know if there
are more glitches. Excel charts are somewhat quirky. :)

--
Deborah Pate (TeamB) http://delphi-jedi.org

  Use Borland servers; TeamB don't see posts via ISPs
  http://www.borland.com/newsgroups/genl_faqs.html

Re:Excel Chart access and newseries (newbie)


Thanks, Deborah, your a star!

Nearly there (amazing how much time one can spend on something
supposedly easy). I also made a mistake in the SC.add method (should be
xlColumns rather than xlXYScatter when calling it). Well, now I can
compile but I get more than I have bargained for. In a test-spreadsheat,
I have 3 columns populated, and I plot Col2 vs. Col1 to start with. Then
I try to add the series Col3 vs Col1. Well, I get this plot but
unfortunately also Col1 vs Col1 and Col2 vs Col1, i.e. too many. So I
guess my range is out - interesting, it worked when plotting the
original plot.

Ok, I tried to use Excel-Macrorecording to look up the VBA command and
that should look like:

ActiveChart.SeriesCollection.Add Source:=("Sheet1").Range("A:A,C:C"),
... (rest is the xlColumns,False,False,False stuff)

I define my range as:
Rnge := DataSheet.Range['A1:A25','C1:C25']        

 and later use

sc.add(Rnge,xlColumns,false,false,false)

and get the 3 series added - most peculiar. And yes, I have checked:
there is no loop that by accident calls the add method several times.

Does anybody have an idea? Thank you very much for any help

Ralph

Re:Excel Chart access and newseries (newbie)


<<Ralph Gottschalg:
ActiveChart.SeriesCollection.Add Source:=("Sheet1").Range("A:A,C:C"),
... (rest is the xlColumns,False,False,False stuff)

I define my range as:
Rnge := DataSheet.Range['A1:A25','C1:C25']        

Quote

Is it better if you define it like this?

  Rnge := DataSheet.Evaluate('A:A,C:C');      

--
Deborah Pate (TeamB) http://delphi-jedi.org

  Use Borland servers; TeamB don't see posts via ISPs
  http://www.borland.com/newsgroups/genl_faqs.html

Re:Excel Chart access and newseries (newbie)


Dear Deboray,

no unfortunately it isn't. I tried

Rnge := DataSheet.Evaluate('A:A,C:C',LCID)

and get the complaint that range and olevariant are not compatible.(Without
the LCID it doesn't work at all)

Cheers,
Ralph

"Deborah Pate (TeamB)" <d.p...@cableinet.co.not-this-bit.uk> wrote in
message news:VA.00000193.005a80ac@cableinet.co.not-this-bit.uk...

Quote
> <<Ralph Gottschalg:
> ActiveChart.SeriesCollection.Add Source:=("Sheet1").Range("A:A,C:C"),
> ... (rest is the xlColumns,False,False,False stuff)

> I define my range as:
> Rnge := DataSheet.Range['A1:A25','C1:C25']

> Is it better if you define it like this?

>   Rnge := DataSheet.Evaluate('A:A,C:C');

> --
> Deborah Pate (TeamB) http://delphi-jedi.org

>   Use Borland servers; TeamB don't see posts via ISPs
>   http://www.borland.com/newsgroups/genl_faqs.html

Re:Excel Chart access and newseries (newbie)


Thanks Deborah,

again you helped me a step further but this whole business feels like a
mooving target now.

I use the IDispatch version as given by you and get a 50% better result. I
now get the same - but correct - curve added twice, i.e. I get 2 times A:A
vs. C:C, while before I got A:A vs A:A, A:A vs B:B and A:A vs C:C as 'bonus
curves'. Strange...

Above the actual plotting line, all I am doing is setting the cell format as
follows, and checking the used range:

  if IsTimeAxis then
    NumberFormat:=LongDateFormat
  else
    NumberFormat:='General';

ExcelWorkSheet.Range[XColIdent+'2',XColIdent+IntToStr(NCols)].NumberFormat:=
NumberFormat;

but I don't see how this should influence the plotting (might be my lack of
experience, though). Otherwise I don't see how this duplication could
happen.

Thanks for your help,
Ralph

"Deborah Pate (TeamB)" <d.p...@cableinet.co.not-this-bit.uk> wrote in
message news:VA.00000195.00e55141@cableinet.co.not-this-bit.uk...

Quote
> <<Ralph Gottschalg:
> and get the complaint that range and olevariant are not
> compatible.(Without the LCID it doesn't work at all)

> Sorry - it would help if we both said what types our
> variables were. :)

> Either Rnge should be an olevariant, or if it is a Range
> you should cast it to an IDispatch:
>   IDispatch(Rnge) := DataSheet.Evaluate('A:A,C:C', LCID);

> You need the LCID if Datasheet is an interface rather than
> a component or a variant.

> --
> Deborah Pate (TeamB) http://delphi-jedi.org

>   Use Borland servers; TeamB don't see posts via ISPs
>   http://www.borland.com/newsgroups/genl_faqs.html

Other Threads