Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Want to create a separate PDF file for each of multiple charts | Charts and Charting in Excel | |||
Create multiple Charts from one worksheet | Charts and Charting in Excel | |||
Macro to create multiple charts? | Charts and Charting in Excel | |||
Create multiple XY scatter charts | Charts and Charting in Excel | |||
Create Multiple Charts w/ VB | Charts and Charting in Excel |