generating dynamic excel charts within word

[drako]

Hi,

I'm currently trying to figure out how to dynamically create and insert
an Excel Chart into a Word Document, based upon several paramaters that
are selected within a Word VBA Userform.

I've looked at inserting an OLE Object, or firing up Excel from Word,
passing the paramaters to a VBA Macro, then generating the chart.

On the surface, using an OLE Object from Word looks easier. I would use
the following code to generate an exploded pie chart:

Sub pie_chart()
Selection.InlineShapes.AddOLEObject ClassType:="Excel.Chart.8",
FileName:= _
"", LinkToFile:=False, DisplayAsIcon:=False
End Sub

However, I have no idea how to pass data to object once it has been
created.

If I was to generate a chart in Excel from VBA code, I would use
something like:

Sub pie_chart()

Range("A3:B7").Select
Charts.Add
ActiveChart.ChartType = xl3DPieExploded
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A3:B7"),
PlotBy:= _
xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Recommended Asset Allocation"
End With
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowPercent,
LegendKey:=False _
, HasLeaderLines:=False
End Sub


I suspect that I would need to use similar code to that above to fine
tune the chart, but really am unsure how I would pass it to the OLE
generated chart.

Any suggestions or tips appreciated.

Rgds
Neil.

 

[Robert Paulsen]

Using the OLEFormat.Object property will expose Excel's method's to your Word
code. I slightly modified the code you supplied and it created a chart. The
range you set needs to be populated with your data of course.

Sub pie_chart()
Dim oChart As InlineShape

Set oChart =
Selection.InlineShapes.AddOLEObject(ClassType:="Excel.Chart.8", _
FileName:="", LinkToFile:=False, DisplayAsIcon:=False)

With oChart.OLEFormat.Object.ActiveChart
.ChartType = xl3DPieExploded
.SetSourceData
Source:=oChart.OLEFormat.Object.Worksheets(1).Range("A3:B7"),
PlotBy:=xlColumns
' .Location Where:=xlLocationAsObject, Name:="Sheet1"
.HasTitle = True
.ChartTitle.Characters.Text = "Recommended Asset Allocation"
.ApplyDataLabels Type:=xlDataLabelsShowPercent,
LegendKey:=False, HasLeaderLines:=False
End With
End Sub

 

[drako]

OK. Thanks for replying to this. It makes sense to me.

If I want to take this a step further, can I create a chart object in
Word, then pass 'range' data to the object via an existing array of
values created in Word VBA ?


Thanks
Neil.

 

[Robert Paulsen]

I haven't manipulated a chart object in Word in awhile. I believe quite a bit
of Excel's object model is exposed, but there are some methods that are
tricky to work with. If I remember correctly I generally created an Excel
macro to do what I wanted, and then through trial and error got the Word code
to replicate it.

 

[Cindy M -WordMVP-]

<[email protected]>
<[email protected]>
Newsgroups: microsoft.public.word.vba.general
NNTP-Posting-Host: 214-161.0-85.cust.bluewin.ch 85.0.161.214
Lines: 1
Path: number1.nntp.dca.giganews.com!border1.nntp.dca.giganews.com!nntp.giganews.com!newshub.sdsu.edu!msrtrans!TK2MSFTFEEDS01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl
Xref: number1.nntp.dca.giganews.com microsoft.public.word.vba.general:84027

Hi Drako,

If I want to take this a step further, can I create a chart object in
Word, then pass 'range' data to the object via an existing array of
values created in Word VBA ?

Yes.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail

 

[drako]

Hi,

I've just been reading over this post again, and have spent most of the
day (unsucessfully) trying to pass data to the Excel Object from Word.

What I have is two arrays of equal value, one of which I would use for
Column A, and one for Column B.

What I tried to do was modify the code above, and add the Excel Range
as a string.

I added the following code within the sub:

For intRow = 1 To UBound(temp1)
oChart.Cells(intRow, 1).value = temp1(intRow)
oChart.Cells(intRow, 2).value =temp2(intRow)

Next intRow

strRange = "A1:" & Chr$(Asc("B") + UBound(temp2))


where: temp1 + temp2 are Publicly declared Arrays that hold data which
can be accessed by the sub.

I think it is having a problem with the 'Cells' method, but I can't
figure out any way to create the range .....

Any suggestions.

Thanks
Neil.

 

[Cindy M -WordMVP-]

Hi Drako,

Sorry, just picked this up...

What, more exactly, is oChart? How did you assign it?

An Excel CHART won't have any cells; you need to be working with a
Worksheet object in order to write data to cells.

I've just been reading over this post again, and have spent most of the
day (unsucessfully) trying to pass data to the Excel Object from Word.

What I have is two arrays of equal value, one of which I would use for
Column A, and one for Column B.

What I tried to do was modify the code above, and add the Excel Range
as a string.

I added the following code within the sub:

For intRow = 1 To UBound(temp1)
oChart.Cells(intRow, 1).value = temp1(intRow)
oChart.Cells(intRow, 2).value =temp2(intRow)

Next intRow

strRange = "A1:" & Chr$(Asc("B") + UBound(temp2))


where: temp1 + temp2 are Publicly declared Arrays that hold data which
can be accessed by the sub.

I think it is having a problem with the 'Cells' method, but I can't
figure out any way to create the range .....

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[drako]

Hi,

I assined 'oChart' as an inline shape by:

Dim oChart As Word.InlineShape

then set it by:

Set oChart =
Selection.InlineShapes.AddOLEObject(ClassType:="Excel.Chart.8", _
FileName:="", LinkToFile:=False, DisplayAsIcon:=False)


It is kind of difficult to explain exactly what I am doing, so I
probably need to post the whole sub() below:
What I need to do (and obviously not doing correctly) is to start Excel
in the background, loop through an existing array of values, and write
them to the Cells if they are not '0'. I then am trying to create a
range from these cells, and construct a pie chart based on the cell
values which I then am trying to insert into a Word bookmark.

Needless to say, this is stretching my VBA knowledge to the
limit....Hope this 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

 

[Cindy M -WordMVP-]

Hi Drako,

OK... You still haven't described exactly what problem you're
experiencing with this code - where it's failing.

Note that I probably wouldn't do this:

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

How can you be sure there IS an ActiveChart? Instead, I'd pick this up
directly from OLEFormat.Object, right at the start
Set theChart = wkbEmbedded.Charts(1) 'If this is the correct syntax

I would assume there'd be no reason to use the .ADD method? And also no
need to select the range. I'm pretty sure there's a way in the object
model to set the range a Chart is based on, without having to select
anything. You should ask in excel.programming

I assined 'oChart' as an inline shape by:

Dim oChart As Word.InlineShape

then set it by:

Set oChart =
Selection.InlineShapes.AddOLEObject(ClassType:="Excel.Chart.8", _
FileName:="", LinkToFile:=False, DisplayAsIcon:=False)


It is kind of difficult to explain exactly what I am doing, so I
probably need to post the whole sub() below:
What I need to do (and obviously not doing correctly) is to start Excel
in the background, loop through an existing array of values, and write
them to the Cells if they are not '0'. I then am trying to create a
range from these cells, and construct a pie chart based on the cell
values which I then am trying to insert into a Word bookmark.

Needless to say, this is stretching my VBA knowledge to the
limit....Hope this 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

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail