Excel Chart SeriesCollection Formula
 

I am trying to use VBA code to capture the X and Y values of existing chart.
The auto convertion to exponential format is give me problem. Please find the
details below:
VBA code :
Set obj = ActiveSheet.ChartObjects(cnt1).Chart
for series_cnt=1 to obj.SeriesCollection.Count
seriesFormula = obj.SeriesCollection(series_cnt).Formula
msgbox seriesFormula
next

The formula which I am trying to retrieve is
SERIES("data",{"2007-10-31","2007-11-30","2007-12-31","2008-01-31","2008-02-29","2008-03-31","2008-04-30","2008-05-31","2008-06-30","2008-07-31","2008-08-31","2008-09-30"},{1060960,965924,904893,1009245,942404,961522, 1007654,997132,965041,1006511,965152,976880},2)

From 1006511,965152,976880, it is getting displayed in Exponential format
with output being

SERIES("data",{"2007-10-31","2007-11-30","2007-12-31","2008-01-31","2008-02-29","2008-03-31","2008-04-30","2008-05-31","2008-06-30","2008-07-31","2008-08-31","2008-09-30"},{1060960,965924,904893,1009245,942404,961522, 1007654,997132,965041,1E,}

I need vba code which will help us to retain the value as it is and not to
get converted in Exponential format and with no truncation. These are chart
plot points.

Excel Chart SeriesCollection Formula
 

When I run your code I get the correct formula in my message box.

If you want to get the X and Y values, use this:

vXValues = obj.SeriesCollection(series_cnt).XValues
vYValues = obj.SeriesCollection(series_cnt).Values

vXValues and vYValues should be declared as type Variant, not Variant().
Each contains an array of values.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"sgpl" wrote in message
...
I am trying to use VBA code to capture the X and Y values of existing
chart.
The auto convertion to exponential format is give me problem. Please find
the
details below:
VBA code :
Set obj = ActiveSheet.ChartObjects(cnt1).Chart
for series_cnt=1 to obj.SeriesCollection.Count
seriesFormula = obj.SeriesCollection(series_cnt).Formula
msgbox seriesFormula
next

The formula which I am trying to retrieve is
SERIES("data",{"2007-10-31","2007-11-30","2007-12-31","2008-01-31","2008-02-29","2008-03-31","2008-04-30","2008-05-31","2008-06-30","2008-07-31","2008-08-31","2008-09-30"},{1060960,965924,904893,1009245,942404,961522, 1007654,997132,965041,1006511,965152,976880},2)

From 1006511,965152,976880, it is getting displayed in Exponential format
with output being

SERIES("data",{"2007-10-31","2007-11-30","2007-12-31","2008-01-31","2008-02-29","2008-03-31","2008-04-30","2008-05-31","2008-06-30","2008-07-31","2008-08-31","2008-09-30"},{1060960,965924,904893,1009245,942404,961522, 1007654,997132,965041,1E,}

I need vba code which will help us to retain the value as it is and not to
get converted in Exponential format and with no truncation. These are
chart
plot points.