how do I change the data series an an excel graph

[Guest]

I have a series of graphs that have been created that now reflect 2002, 2003
& 2004 and now need to change to reflect 2003, 2004 & 2005 the quickest and
easiest way since there are over 300+ spreadsheet and each has three
indivdual graphs that need to be changed.

 

[Jon Peltier]

Deb -

This one's a bit more intricate than your typical newsgroup question.
Here's how I'd go about it, in pseudocode.

First, it is all included within a set of nested loops:

For Each oWorkbook In Directory.Workbooks
For Each oWorksheet In oWorkbook.Worksheets
For Each oChartOb in oWorkSheet.ChartObjects
For Each oSeries in oChartOb.Chart.SeriesCollection

Then for each series, I'd use John Walkenbach's Chart Series Class
Module (http://j-walk.com) to find the actual range containing the X
values and the Y values. If each range is a column, I'd offset it by one
row; if each is a row, by one column.

I know I'm making this sound easy <g>, but there are some tricky bits to
it. You'd like to use some kind of dialog to select the directory (or
directory tree) to search for workbooks, probably with FileSearch or
Dir. The chart series class is very nice, but still the interface
between your code and the class requires some testing to make sure it
won't crash, and you'd need some kind of checking to make sure the new X
range contains 2003 thru 2005.

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