creating and populating an excel chart from within word

[neil]

Hi,

I've posted questions on a similar topic, but never managed to get to
the bottom of this issue.

Without explaining in tedious details what I am trying to do, it may be
easier to look at the following thread in word.vba.programming


http://groups.google.co.uk/group/mi...2?q=drako+excel.chart&rnum=1#29baff2195a0dfa2

What I am trying to do is to open an instance of excel from word,
create an inline chart object, pass data to excel, then embed the
completed pie chart in word.

OK, I can manage to create an excel chart within word via VBA, but the
real difficulty seems to be either passing data to an excel range, or
simulating an excel range within word (there may be another obscure way
to do this).

The data I need to pass to the range comes from two existing arrays
that exist in the word VBA macro - I then look through them, and any
data in one loop that not '0' is passed to the range.

The whole loop is as follows [Hope it makes sense]:


Sub currentChart()


' This loop writes the data from the array to the Excel sheet


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, and ignore if percentage set to 0

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


Any pointers in the right direction would be appreciated.

Rgds
Neil.

 

[Jon Peltier]

Neil -

I reworked your code a bit. I inserted a sheet, not a chart, then added a
chart to the embedded workbook object, and finally kept it as a chart sheet,
without embedding it on the embedded sheet. More important is how I passed
the data. I built an array, then passed it in one step to the worksheet. You
can build your real array the way you currently step through and eliminate
blanks from the larger array.

Here's my code:

Sub currentChart()

' This loop writes the data from the array to the Excel sheet

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.Sheet.8", _
FileName:="", LinkToFile:=False, DisplayAsIcon:=False)
'' this is probably best place to size inline shape to desired size

Set wkbEmbedded = oChart.OLEFormat.Object
Set wksEmbedded = wkbEmbedded.Worksheets(1)

'' make dummy array
'' construct your own array rather than passing one cell at a time
Dim vArray() As Variant
Dim i As Integer
ReDim vArray(1 To 5, 1 To 2)
For i = 1 To 5
vArray(i, 1) = Chr$(64 + i)
vArray(i, 2) = i
Next

'' pass array to worksheet
With wksEmbedded
With .Range("A1").Resize(UBound(vArray, 1), UBound(vArray, 2))
.Value = vArray
strRange = .Address
End With
End With

' ' loop through asset class array, and ignore if percentage set to 0
' 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

With wkbEmbedded
.Charts.Add
With .Charts(1)
.ChartType = xl3DPieExploded ' ugh!

.SetSourceData Source:=wksEmbedded.Range(strRange), _
PlotBy:=xlColumns
.HasTitle = True
.ChartTitle.Characters.Text = "Current Asset Allocation"
.ApplyDataLabels Type:=xlDataLabelsShowPercent, _
LegendKey:=True, HasLeaderLines:=False
With .PlotArea
.Border.LineStyle = xlNone
.Interior.ColorIndex = xlNone
'' probably also want to maximize its size here too
End With
End With
End With

End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Hi,

I've posted questions on a similar topic, but never managed to get to
the bottom of this issue.

Without explaining in tedious details what I am trying to do, it may be
easier to look at the following thread in word.vba.programming


http://groups.google.co.uk/group/mi...2?q=drako+excel.chart&rnum=1#29baff2195a0dfa2

What I am trying to do is to open an instance of excel from word,
create an inline chart object, pass data to excel, then embed the
completed pie chart in word.

OK, I can manage to create an excel chart within word via VBA, but the
real difficulty seems to be either passing data to an excel range, or
simulating an excel range within word (there may be another obscure way
to do this).

The data I need to pass to the range comes from two existing arrays
that exist in the word VBA macro - I then look through them, and any
data in one loop that not '0' is passed to the range.

The whole loop is as follows [Hope it makes sense]:


Sub currentChart()


' This loop writes the data from the array to the Excel sheet


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, and ignore if percentage set to 0

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


Any pointers in the right direction would be appreciated.

Rgds
Neil.