Retrieving Data Range for Chart

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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" <[email protected]>
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
 
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
 
Back
Top