Excel change chart formula

Question

I'm currently generating Excel-documents using the Office Object Model. I have a problem with editing charts. In a template file I got a bar-chart which uses the following source:

    2008 2009 2010
A   10%     25%     15%
B   20%     25%     35%
C   30%     25%     45%
D   40%     25%     5%

The chart has the following formula: =sheet2!$A$1:$D$5

When for example the column '2009' is empty, I don't want to show the bar in the chart. So I want to change the formula to something like: =sheet2!A$1:D$5;sheet2!C$1:C$5

I know there is a method setSourceData, but I need to get the current formula or range first.

My question is; How can I get the chart formula? Or maybe there is another way to do what I want?

I also tried something with dynamic ranges in Excel, but this seems to only work with columns that are added or removed from the end of the range, not in the middle like column '2009'.

Answer 1

There isn't a property that holds the full data range of a chart. However each Series holds information about it's range.

The code below will list all series and then delete the second one.

Sub ChartRanges()

Dim lngSeries As Long

    ActiveSheet.ChartObjects("Chart 1").Select

    For lngSeries = 1 To ActiveChart.SeriesCollection.Count
        Debug.Print ActiveChart.SeriesCollection(lngSeries).Formula
    Next lngSeries
'List out series in chart

    ActiveChart.SeriesCollection(2).Delete
'Delete a series from the chart

End Sub

Based on your example data, the code will output this

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$5,Sheet1!$B$2:$B$5,1) =SERIES(Sheet1!$C$1,Sheet1!$A$2:$A$5,Sheet1!$C$2:$C$5,2) =SERIES(Sheet1!$D$1,Sheet1!$A$2:$A$5,Sheet1!$D$2:$D$5,3)

The Series is made up from four arguments:

(Series Name, XValues, Values, Plot Order)

Answer 2

I made the following code to solve my problem. It rebuilds all the existings series formulas. This won't work for all possible charts, but it does for the ones I currently have. In the future I'll probably look at it again and try to improve it. Suggestions to the code below are welcome.

(sorry for the lack of code comments)

        foreach (Excel.ChartObject chart in (Excel.ChartObjects)sheet.ChartObjects(Type.Missing))
        {
            IDictionary<int, Boolean> colHasValues = new Dictionary<int, Boolean>(); 
            ArrayList seriesFormulas = new ArrayList(); 

            foreach (Excel.Series series in (Excel.SeriesCollection)chart.Chart.SeriesCollection(Type.Missing))
            {
                seriesFormulas.Add(series.Formula);

                Array sValues = (Array)series.Values;
                int i = 1;
                foreach (Object o in sValues)
                {
                    if(!colHasValues.Keys.Contains(i)) colHasValues.Add(i, false);                        
                    if (o != null)
                    {
                        colHasValues[i] = true;                             
                    }
                    i++;
                }
            }

            if (!colHasValues.Values.Contains(true))
            {   
                chart.Delete();
            }
            else if (colHasValues.Values.Contains(false) && seriesFormulas.Count > 1)
            {    

                ArrayList newSeriesFormulas = new ArrayList(); 

                foreach (String formula in seriesFormulas)
                {

                    String[] formulaBits = formula.Split(";".ToCharArray());
                    if (formulaBits.Length == 4)
                    { 

                        for (int arrNr = 1; arrNr <= 2; arrNr++)
                        {   //1 = XValues, 2 = Values
                            int indexFirstChar = formulaBits[arrNr].IndexOf(':');
                            int indexLastChar = formulaBits[arrNr].LastIndexOf('$', indexFirstChar) + 1;

                            String firstRow = formulaBits[arrNr].Substring(indexLastChar, indexFirstChar - indexLastChar);
                            String firstColumn = formulaBits[arrNr].Substring(indexLastChar - 2, 1);

                            formulaBits[arrNr] = "";

                            foreach (KeyValuePair<int, Boolean> cat in colHasValues)
                            {
                                if (cat.Value == true)
                                {
                                    formulaBits[arrNr] += "overzichten!$" + getExcelColumnName((getExcelColumnNumber(firstColumn) + cat.Key - 1)) + "$" + firstRow + ":$" + getExcelColumnName((getExcelColumnNumber(firstColumn) + cat.Key - 1)) + "$" + firstRow + ";";
                                }
                            }
                            formulaBits[arrNr] = formulaBits[arrNr].TrimEnd(";".ToCharArray());
                            if (formulaBits[arrNr].Contains(';')) 
                            {
                                formulaBits[arrNr] = "(" + formulaBits[arrNr] + ")";
                            }
                        }

                        newSeriesFormulas.Add(String.Join(";", formulaBits));

                    }

                }

                int seriesid = 0;
                foreach (Excel.Series series in (Excel.SeriesCollection)chart.Chart.SeriesCollection(Type.Missing))
                {
                    series.Formula = newSeriesFormulas[seriesid].ToString();
                    seriesid++;
                }

            }

        }