using automation in excel to create multiple charts

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lizjohn1@gmail.com - 04 Jan 2007 20:43 GMT

My problem is pretty simple:

I want to create several pie charts from rows of data (1 chart
worksheet per row of data).

I know how to do this for 1 chart, but how can I pass in a range to a
routine so as all of the charts I need are created at once (in some
sort of loop) ?

Thanks in advance.

Reply to this Message

Jon Peltier - 05 Jan 2007 02:28 GMT

This does the trick. Select a range with categories (legend entries) in the
top column and series names (chart title) in the first column. Adjust size
and position through the parameters in the ChartObjects.Add() method.

Sub OnePieChartPerRow()
Dim rngChartData As Range
Dim iRowIx As Integer, iRowCt As Integer, iColCt As Integer
Dim oChart As ChartObject
Dim NewSrs As Series

If Not TypeName(Selection) = "Range" Then Exit Sub

Set rngChartData = Selection
iRowCt = rngChartData.Rows.Count
iColCt = rngChartData.Columns.Count

For iRowIx = 2 To iRowCt
Set oChart = ActiveSheet.ChartObjects.Add(Top:=25 + (iRowIx - 2) * 200,
_
Height:=200, Left:=450, Width:=300)
Set NewSrs = oChart.Chart.SeriesCollection.NewSeries
oChart.Chart.ChartType = xlPie
With oChart.Chart.PlotArea
.Border.LineStyle = xlNone
.Interior.ColorIndex = xlNone
End With
With NewSrs
'' Name in first column
.Name = rngChartData.Cells(iRowIx, 1)
.Values = rngChartData.Cells(iRowIx, 2).Resize(1, iColCt - 1)
'' XValues in first row
.XValues = rngChartData.Cells(1, 2).Resize(1, iColCt - 1)
End With
Next

End Sub

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

> My problem is pretty simple:
>
[quoted text clipped - 6 lines]
>
> Thanks in advance.

Reply to this Message

lizjohn1@gmail.com - 05 Jan 2007 18:10 GMT

I will try it (but now have some other urgent work to do). I am
assuming that if I want to make all the charts into their own sheets, I
can just say "As ChartSheet" instead of "As ChartObject".

Thanks a lot!

> This does the trick. Select a range with categories (legend entries) in the
> top column and series names (chart title) in the first column. Adjust size
[quoted text clipped - 50 lines]
> >
> > Thanks in advance.

Reply to this Message

Jon Peltier - 05 Jan 2007 23:48 GMT

To do them as chart sheets, you need to make a few changes (see lines
between v v v and ^ ^ ^

Sub OnePieChartPerRow()
Dim rngChartData As Range
Dim iRowIx As Integer, iRowCt As Integer, iColCt As Integer
v v v v v v v v v v v v v v v v v v v v
Dim oChart As Chart
^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^
Dim NewSrs As Series

If Not TypeName(Selection) = "Range" Then Exit Sub

Set rngChartData = Selection
iRowCt = rngChartData.Rows.Count
iColCt = rngChartData.Columns.Count

For iRowIx = 2 To iRowCt
v v v v v v v v v v v v v v v v v v v v
Set oChart = Charts.Add
Set NewSrs = oChart.SeriesCollection.NewSeries
oChart.ChartType = xlPie
With oChart.PlotArea
^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^
.Border.LineStyle = xlNone
.Interior.ColorIndex = xlNone
End With
With NewSrs
'' Name in first column
.Name = rngChartData.Cells(iRowIx, 1)
.Values = rngChartData.Cells(iRowIx, 2).Resize(1, iColCt - 1)
'' XValues in first row
.XValues = rngChartData.Cells(1, 2).Resize(1, iColCt - 1)
End With
Next

End Sub

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

>I will try it (but now have some other urgent work to do). I am
> assuming that if I want to make all the charts into their own sheets, I
[quoted text clipped - 59 lines]
>> >
>> > Thanks in advance.

Reply to this Message

lizjohn1@gmail.com - 23 Jan 2007 23:23 GMT

Hi,

I tried this; however, I get errors in the last With NewSrs block.
When I comment that out, I get a pie chart of the data in the first
column (not the first row), and single value pie charts for the rest of
the rows.

my data looks like this:

Pie Chart Name Field1 Field2 Field3 ... FieldN
Chart1 Name val1 val2 val3 ... valN
Chart2 Name val1 val2 val3 ... valN

ChartN Name val1 val2 val3 ... valN

So I am getting a pie with all the val1's instead of from val1 - valN
in Row Chart1 Name.

Thanks for any assistance you can provide.

On Jan 5, 6:48 pm, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:

> To do them as chart sheets, you need to make a few changes (see lines
> between v v v and ^ ^ ^
[quoted text clipped - 107 lines]
>
> >> > Thanks in advance.- Hide quoted text -- Show quoted text -

Reply to this Message