Pie chart for each row - How to
Julia Stark
document. Data is like this:
A B C D E
Row 1 WC UC RAC Ins
Row 2 John $50 $150 $500 $3000 pie chart shows B2:E2
Row 3 April $50 $100 $150 $1500 pie chart shows B3:E3
Row 4 Bill $75 $125 $0 $2500 pie chart shows B4:E4
How do I do this? I have about 200 rows of information. Thanks for your
help..Julia
Peltier
I did the part that makes a bunch of pie charts automatically. I'm not
real great with Word VBA, so someone else will have to help you dump
them where you want them in your Word doc. The code just loops through,
so any change you make to formats or whatever within the loop is applied
to each chart.
I didn't test it with more than a few charts, so you might experience
memory problems. If so, you could make the chart first, loop to the
next row of data, change the data range in the one chart, and copy/paste
the chart into Word, then loop.
- Jon
Sub LotsaPies()
' Macro recorded and adjusted 2/23/01 by Jon Peltier
Dim obChart As ChartObject
Dim myrow As Long
Dim myrows As Long
' How many pies to make
myrows = WorksheetFunction.CountA(ActiveSheet.Range("A:A"))
For myrow = 2 To myrows + 1
' Make a pie with the top left corner in column F
' in same row as data, as wide as columns F through K,
' 17 rows (18 minus 2) high
' Adjust to suit your tastes
Set obChart = ActiveSheet.ChartObjects.Add(Left:=[F:F].Left, _
Top:=[F1].Offset(myrow - 1, 0).Top, _
Width:=[F:K].Width, Height:=[2:18].Height)
With obChart.Chart
.ChartType = xlPie
' A1:E1 has legend entries
' A(myrow):E(myrow) has data
.SetSourceData PlotBy:=xlRows, Source:= _
ActiveSheet.Range("A1:E1,A" & myrow & ":E" & myrow)
.ApplyDataLabels Type:=xlDataLabelsShowValue, _
LegendKey:=False, HasLeaderLines:=True
.HasTitle = True
With .ChartTitle
.Font.Bold = True
.AutoScaleFont = False
.Left = 88
.Top = 1
End With
With .PlotArea
.Border.LineStyle = xlNone
With .Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
.Height = 50
.Left = 22
.Top = 40
.Width = 156
.Height = 156
End With
' For some reason, I have to activate the chart
' to fix the fonts (otherwise they're all size 2)
.Parent.Activate
With .ChartArea
.Font.Size = 10
.AutoScaleFont = False
End With
End With
' Now deactivate the chart
ActiveWindow.Visible = False
Windows(ActiveWorkbook.Name).Activate
ActiveCell.Activate
' Here's where you need to put the code that pastes the
' pie charts into your Word doc.
' I'm not so swift with Word VBA, you're on your own.
Next
End Sub
Peltier
I got to thinking about my last post, and did a macro that only made one
chart, and each loop changed its data. This will probably work better,
because your sheet won't fill up with resource-hogging charts.
- Jon
Sub OnePieLotsaTimes()
' Macro recorded and adjusted 2/23/01 by Jon Peltier
Dim obChart As ChartObject
Dim myrow As Long
Dim myrows As Long
' How many pies to make
myrows = WorksheetFunction.CountA(ActiveSheet.Range("A:A"))
' Make the chart first
' Make a pie with the top left corner in F2,
' bottow right corner in K18
' Adjust to suit your tastes
Set obChart = ActiveSheet.ChartObjects.Add(Left:=[F:F].Left, _
Top:=[2:2].Top, Width:=[F:K].Width, Height:=[2:18].Height)
With obChart.Chart
.ChartType = xlPie
With .ChartArea
.Font.Size = 10
.AutoScaleFont = False
End With
End With
For myrow = 2 To myrows + 1
With obChart.Chart
' A1:E1 has legend entries
' A(myrow):E(myrow) has data
End With
obChart.Visible = True
' Here's where you need to put the code that pastes the
' pie chart into your Word doc.
' I'm not so swift with Word VBA, you're on your own.
MsgBox "Chart of row " & myrow
Next
End Sub
Peltier wrote:
>
> Julia -
>
> I did the part that makes a bunch of pie charts automatically. I'm not
> real great with Word VBA, so someone else will have to help you dump
> them where you want them in your Word doc. The code just loops through,
> so any change you make to formats or whatever within the loop is applied
> to each chart.
>
> I didn't test it with more than a few charts, so you might experience
> memory problems. If so, you could make the chart first, loop to the
> next row of data, change the data range in the one chart, and copy/paste
> the chart into Word, then loop.
>
> - Jon
>
[bogus code removed]
Peltier
One more from the happy idiot. The best thing to do might be just to
plot the first row of data, format the chart the way you like, then
select it and run the simple short macro below (KISSS: Keep It Short and
Simple, Stupid!).
- Jon
Sub JustMoveTheData()
' Macro recorded and adjusted 2/23/01 by Jon Peltier
Dim myChart As Chart
Dim myrow As Long
Dim myrows As Long
' How many pies to make
myrows = WorksheetFunction.CountA(ActiveSheet.Range("A:A"))
Set myChart = ActiveChart
For myrow = 2 To myrows + 1
With myChart
' A1:E1 has legend entries
' A(myrow):E(myrow) has data
' For some reason, I have to activate the chart
' to fix the fonts (otherwise they're all size 2)
.SetSourceData PlotBy:=xlRows, Source:= _
ActiveSheet.Range("A1:E1,A" & myrow & ":E" & myrow)
End With
EarlK
If you'll be pasting 200 charts into one Word doc, you can expect problems.
Pasting a copied Excel chart says it's going to paste an Excel chart object
(as determined by the default option in Paste-special). A bunch of those
would use up resources, and make the Word file very large too. In fact
though, the paste imbeds an Excel WORKBOOK object, which contains an
imbedded chart. After pasting it into Word, you can see this by
double-clicking it into edit mode, and you'll see worksheets, and the
imbedded chart in it.
You may need to instead use Paste-special, and use a picture option.
--
Regards from Virginia Beach,
EarlK
ea...@livenet.net
-------------------------------------------------------------
"Julia Stark" <julia...@riverfrontinc.org> wrote in message
news:edNnoRbnAHA.2276@tkmsftngp05...
Peltier
I forgot about that. Probably copying the Excel chart as a picture
would be best of all. Do it by hand a couple times, trying out the copy
as options, and see how they look in Word. If the pasted chart needs
resizing in Word, it is better to do the resizing in Excel prior to
copying, so it can be displayed in Word at 100% of its original size.
- Jon