using automation to create and embed an excel chart in Word

[neil]

Hi,

I'm currently trying to call an instance of Excel in order to generate
a Pie Chart to be embedded in a Word Document. I thought this forum may
be a better place to post than the Word forums.

My word document is a templating system that uses Macros to pull in
lots of other templates and data to build a document, and I need to
generate a pie chart based on data entered in a word userform.

I can get the data from the userform into a couple of arrays, which I
planned to use to construct the Excel Range which will be needed to
construct the chart.

This is the following Sub I created:


Sub currentChart()


Dim oChart As Word.InlineShape
Dim wkbEmbedded As Excel.Workbook
Dim wksEmbedded As Excel.Worksheet
Dim count_classes As Integer
Dim strRange As String
count_classes = 0


Set oChart =
Selection.InlineShapes.AddOLEObject(ClassType:="Excel.Chart.8", _
FileName:="", LinkToFile:=False, DisplayAsIcon:=False)
Set wkbEmbedded = oChart.OLEFormat.Object
Set wksEmbedded = wkbEmbedded.Worksheets(1)


' loop through asset class array, write array conents to Cells, but
ignore if percentage set to 0 or is blank.

For intRow = 1 To UBound(serialize_current_asset_alloc_sector)

If serialize_current_asset_alloc_percentage(intRow) <> "" Then
wksEmbedded.Cells(count_classes, 1).value =
serialize_current_asset_alloc_sector(intRow)
wksEmbedded.Cells(count_classes, 2).value =
serialize_current_asset_alloc_percentage(intRow)
count_classes = count_classes + 1
End If

Next intRow


' This is to select the data area to draw the graph
' A graph is always made from the selected area
strRange = "A1:" & Chr$(Asc("B") + count_classes)


With oChart.OLEFormat.Object.ActiveChart
.Range(strRange).Select
.Charts.Add
.ChartType = xl3DPieExploded

.SetSourceData Source:=wksEmbedded.Range(strRange), _
PlotBy:=xlColumns
.Location Where:=xlLocationAsObject, Name:="Current Asset
Allocation"
.HasTitle = True
.ChartTitle.Characters.Text = "Current Asset Allocation"
.ApplyDataLabels Type:=xlDataLabelsShowPercent,
LegendKey:=True, HasLeaderLines:=False
End With


End Sub



I'm finding the documentation a bit vague on this subject, so if anyone
has attempted something similar before, could they let me know where I
may be going wrong.

Many Thanks
Rgds
Neil.