using automation in excel to create multiple charts
 

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.

using automation in excel to create multiple charts
 

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!



Jon Peltier wrote:
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
_______


wrote in message
oups.com...
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.

using automation in excel to create multiple charts
 

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
_______


wrote in message
ups.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
can just say "As ChartSheet" instead of "As ChartObject".

Thanks a lot!



Jon Peltier wrote:
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
_______


wrote in message
oups.com...
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.

using automation in excel to create multiple charts
 

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"
wrote:
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, MicrosoftExcelMVP
Tutorials and Custom Solutionshttp://PeltierTech.com
_______

wrote in oglegroups.com...



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

Thanks a lot!

Jon Peltier wrote:
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, MicrosoftExcelMVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

wrote in message
groups.com...
My problem is pretty simple:

I want tocreateseveral piechartsfrom 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 thechartsI need are created at once (in some
sort of loop) ?

Thanks in advance.- Hide quoted text -- Show quoted text -