N
neil
Hi,
I finally managed to figure out how to pass data from a VBA in word,
start a copy of Excel, pass the data to an Excel Range, create a Pie
Chart, then copy and paste this back into Word.
However, I have a couple of niggly little points that I can't seem to
figure out.
Firstly, I need to set the ChartTitle to the left hand side of the
chart, because if there a lot of entries, the DataLabels obscures the
title.
I've tried various things such as:
myChart.ChartTitle.Left = 0
....But it always remains centered, no matter what I seem to do.
Secondly, if there lots of entries, while the chart is created
correctly, the DataLabels can spill below the bottom of the chart.
I know I could ammend this by resizing the chart (but this is a bit
more complicated, as the chart data comes from a Word VBA userform),
but there seems to be huge gaps between the DataLabel entries, and I'm
sure I could get about ten entries on the chart quite easily if I was
able to remove these massive gaps (If I create a similar chart directly
in Excel, this in in proportion and everything fits well).
I'm currently going round in circles looking at the docs, so if anyone
has any useful pointers, please let me know....
Thanks
Neil.
The code I use to create the chart, if it is of any use is below:
Set oChart = oSheet.ChartObjects.Add(left:=30, Width:=350, Top:=75,
Height:=225).Chart
oChart.SetSourceData Source:=oSheet.Range("A1").Resize(cNumRows,
cNumCols), PlotBy:= _
xlColumns
oChart.ChartType = xl3DPieExploded
oChart.RightAngleAxes = True
oChart.PlotArea.Height = 215
oChart.PlotArea.Width = 215
oChart.PlotArea.left = 5
oChart.PlotArea.Fill.Visible = False
oChart.PlotArea.Fill.Visible = False
oChart.PlotArea.Border.LineStyle = -4142
oChart.Elevation = 30
oChart.Rotation = 80
oChart.Pie3DGroup.VaryByCategories = True
oChart.HasTitle = True
oChart.ChartTitle.Characters.Text = "Current Asset
Allocation"
oChart.ApplyDataLabels Type:=xlDataLabelsShowPercent,
LegendKey:=False _
, HasLeaderLines:=False
oChart.SeriesCollection(1).DataLabels.Font.Size = 8
oChart.Legend.Shadow = True
I finally managed to figure out how to pass data from a VBA in word,
start a copy of Excel, pass the data to an Excel Range, create a Pie
Chart, then copy and paste this back into Word.
However, I have a couple of niggly little points that I can't seem to
figure out.
Firstly, I need to set the ChartTitle to the left hand side of the
chart, because if there a lot of entries, the DataLabels obscures the
title.
I've tried various things such as:
myChart.ChartTitle.Left = 0
....But it always remains centered, no matter what I seem to do.
Secondly, if there lots of entries, while the chart is created
correctly, the DataLabels can spill below the bottom of the chart.
I know I could ammend this by resizing the chart (but this is a bit
more complicated, as the chart data comes from a Word VBA userform),
but there seems to be huge gaps between the DataLabel entries, and I'm
sure I could get about ten entries on the chart quite easily if I was
able to remove these massive gaps (If I create a similar chart directly
in Excel, this in in proportion and everything fits well).
I'm currently going round in circles looking at the docs, so if anyone
has any useful pointers, please let me know....
Thanks
Neil.
The code I use to create the chart, if it is of any use is below:
Set oChart = oSheet.ChartObjects.Add(left:=30, Width:=350, Top:=75,
Height:=225).Chart
oChart.SetSourceData Source:=oSheet.Range("A1").Resize(cNumRows,
cNumCols), PlotBy:= _
xlColumns
oChart.ChartType = xl3DPieExploded
oChart.RightAngleAxes = True
oChart.PlotArea.Height = 215
oChart.PlotArea.Width = 215
oChart.PlotArea.left = 5
oChart.PlotArea.Fill.Visible = False
oChart.PlotArea.Fill.Visible = False
oChart.PlotArea.Border.LineStyle = -4142
oChart.Elevation = 30
oChart.Rotation = 80
oChart.Pie3DGroup.VaryByCategories = True
oChart.HasTitle = True
oChart.ChartTitle.Characters.Text = "Current Asset
Allocation"
oChart.ApplyDataLabels Type:=xlDataLabelsShowPercent,
LegendKey:=False _
, HasLeaderLines:=False
oChart.SeriesCollection(1).DataLabels.Font.Size = 8
oChart.Legend.Shadow = True