Retrieving Data Range for Chart
 

All

I have the code working that will let me retrieve the series formula for a
chart but I need the data range for the chart.

For Each xlSheet In xlBook.Worksheets
For Each EChart In xlSheet.ChartObjects
rst!series = EChart.Chart.SeriesCollection(1).Formula
Next EChart
Next xlSheet

Now this works and I can save the information off into a table. What I want
is to save the data range for the chart in a table within MS Access. Can
anyone help with with the correct code to access and save the datarange for
each chart. Once i have that I will modifiy it within Access and then write
it back to the excel chart.

thanks

Terry

Retrieving Data Range for Chart
 

Terry,
It gets a little complicated. <g
See what John Walkenbach has to say...
http://j-walk.com/ss/excel/tips/tip83.htm
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Terry"
wrote in message
All
I have the code working that will let me retrieve the series formula for a
chart but I need the data range for the chart.

For Each xlSheet In xlBook.Worksheets
For Each EChart In xlSheet.ChartObjects
rst!series = EChart.Chart.SeriesCollection(1).Formula
Next EChart
Next xlSheet

Now this works and I can save the information off into a table. What I want
is to save the data range for the chart in a table within MS Access. Can
anyone help with with the correct code to access and save the datarange for
each chart. Once i have that I will modifiy it within Access and then write
it back to the excel chart.
thanks
Terry

Retrieving Data Range for Chart
 

A chart's data range isn't exposed to VBA. You can get all the series
formulas and piece it together this way, but there are frequently charts
that do not have a defined data range. Make a chart using A1:B10, then copy
the data in D5:E27 and paste it onto the chart. What's the data range?
Excel's Source Data dialog tells you it's too complex to be displayed, and
that's only a two series chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Terry" wrote in message
...
All

I have the code working that will let me retrieve the series formula for a
chart but I need the data range for the chart.

For Each xlSheet In xlBook.Worksheets
For Each EChart In xlSheet.ChartObjects
rst!series = EChart.Chart.SeriesCollection(1).Formula
Next EChart
Next xlSheet

Now this works and I can save the information off into a table. What I
want
is to save the data range for the chart in a table within MS Access. Can
anyone help with with the correct code to access and save the datarange
for
each chart. Once i have that I will modifiy it within Access and then
write
it back to the excel chart.

thanks

Terry