+ Reply to Thread
Results 1 to 2 of 2

how do I change the data series an an excel graph

  1. #1
    Deb
    Guest

    how do I change the data series an an excel graph

    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.

  2. #2
    Jon Peltier
    Guest

    Re: how do I change the data series an an excel graph

    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/
    _______


    Deb wrote:

    > 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.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1