Creating a Static Chart

Hi, every month I am distributing a set of reports containing several charts. The reports that I distribute are copies where I have converted formulaes to values. In order to create "dead" charts I go through the charts, click each chart series, press F2 and then F9 to convert the range references to arrays.

The alternative is to convert the chart to a picture, but I don't want to to that as I believe it increases the size of the workbook, and this alternative is not really much less timeconsuming and tedious than the other one!

So, how can I use VBA code to loop through each graph, and each series within the graphs and convert the range references to arrays?

I can always manage to write the code that loops through the charts and series, but the tricky thing that I cannot see any solution for is how to do the actual convertion from range refernces to arrays.

Any ideas will be greatly appreciated!

Johannes

I'm not sure how to integrate this into your code, but it will take a range object and translate it into a comma seperated string. I think you should be able to use it for what you want. Let me know

Code:

---

Private Sub CommandButton1_Click()
Dim Test As Range, cell As Range
Dim Values As String
Set Test = Range("a1:a10")
For Each cell In Test
    If cell.Value <> "" Then
        If Values <> "" Then
            Values = Values & "," & cell.Value
        Else
            Values = cell.Value
        End If
    End If
Next
MsgBox Values
End Sub

---

Thank you, I did not have time to try this out yet, but it looks like something that could work. Ideally, I do not want to need to know what the source range is when writing the code, but in one of John Walchenbach's books I have found a description of how to parse the series function to find a chart's source range, so the two of you together may just have solved my problem!

Can't wait to try it out!

Hi,

This will convert the cell references to array values.

Code:

---

Sub MakeDeadChart()
    Dim intSeries As Integer
    Dim objChart As ChartObject
   
    For Each objChart In ActiveSheet.ChartObjects
        With objChart.Chart
            For intSeries = 1 To .SeriesCollection.Count
                With .SeriesCollection(intSeries)
                    .XValues = .XValues
                    .Values = .Values
                    .Name = .Name
                End With
            Next
        End With
    Next
End Sub

---

Depending on the amount of data in your charts the process may bomb, as there is a limit the the length of the resulting formula

Andy,


It works! It is elegant! Even brilliant! And so simple! And runs so fast!

Totally grateful and in awe,
Johannes