Updating Excel charts using VBA

[pyth0n]

Hi All

What's the best way to update an Excel Chart using VBA?

At the moment, my script does some work to find the cell where the data is, then edits the Formula for the chart, but after a certain number of iterations this gives me a problem.

Like this:

For i = 1 To chart_count + 1
Set chart_item = xlswk.Charts.Item(i)
series_count = chart_item.SeriesCollection.Count
        
For j = 1 To series_count
Set series_item = chart_item.SeriesCollection(j)
series_formula = series_item.FormulaR1C1

does some string processing and then:

series = series_formula_arr(0) & "," & xseries_output & "," & series_output & "," & j & ")"
            
series_item.FormulaR1C1 = series

Any clues would be greatly received.

 

[SkipVought]

Hi,

Looks like you're trying to discover the data source from the series formula.

NOT a trivial matter. To paraphrase the guru of Excel, John Walkenbach, to attempt to parse out the various parts of a series formula is not a trivial programming task. After working on the problem several years he has published several functions. I bought the book/CD that has the solutions. I'd urge you to do the same.

Excel Charts by John Walkenbach,

http://www.j-walk.com

Skip,

_[red][/red]

 

[SkipVought]

Please explain what your FUNCTIONAL objective is, not what METHOD you are using to achieve it.

Skip,

_[red][/red]

 

[pyth0n]

The objective is to update a chart that is associated with the worksheet (workbook1). The data is copied across from a temporary workbook (workbook2) - which itself is written by VBA from Access.

The requirement is that only select cells in workbook1 are required to build the chart and the paste operation moves the previous cells across to the right. This means that I only have to find a single cell for each SeriesCollection iteration and add it into said SeriesCollection.

This seems to work for the first 2 maybe 3 iterations of the entire project, but I am coming to the conclusion that the length of the string that I am trying to force back into the SeriesCollection is too long - it is getting to be longer than 255 charaters, even though I should have more space (at least 64K)

I understand that this is not a trivial thing to do, I have been working on this project for several months, off and on.

I will take a look at the book that you mention and see what I think. Thanks for that

 

[SkipVought]

It would be much easier to reference the range dynamically and add the data to the series data RANGE.

For instance, if one of your ranges were dynamically named rMonth, then the SERIES reference in the chart would be
[tt]
=YourSheetName!rMonth
[/tt]
The ONLY maintenance operation is to append the data to the range. SIMPLE!

Skip,

_[red][/red]

 

[pyth0n]

Thanks - I will take a look and see. I will probably have to change a significant amount of code to do this, but if it works then it will be worth it.

 

[xlbo]

Certainly will - I can highly recommend this way of working. dynamic ranges are one of the most useful tools you can learn if your work requires you to continually update data / reports / charts / tables

There is a very good FAQ in the MSOffice forum on this written by Skip.

Once the dynamic range is set, all you need to do is add the data in and the range will update itself and then the chart / table based on that range will update automatically

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question