Creating and updating graphs in Word

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Richard John - 27 Apr 2006 23:22 GMT

I am building an Office 2000 automation application under XP Pro (2), where
an MS Access Database, Excel and Word work together. This question is
related to the MS Word component. I can't work out how to get an embedded
graph in Word to automatically update when the underlying data changes. I
want to be able to programmatically directly update (using VBA) *only* the
table data from an MS Access database and let the embedded graph look after
itself. I need some help and advice with implementing the MS Graph component
in Word. Have some trouble with it. I've had to implement a roundabout
solution to achieve this:

Access ==> Excel (for the graphs) ===>Word

Maybe clunky, but it has provided a solution ... but I need a better one
...:-)

A second question:
Is it possible to name a table, or any embedded object in a Word document?
In the case of a table, is it possible then to traverse this table using
VBA. A recommended book perhaps? There is not much information around on
Word VBA.

Thanks

--
Richard John
rjbpond@bigpond.net.au

Reply to this Message

Cindy M -WordMVP- - 01 May 2006 11:31 GMT

Hi Richard,

> I am building an Office 2000 automation application under XP Pro (2), where
> an MS Access Database, Excel and Word work together. This question is
[quoted text clipped - 10 lines]
> Maybe clunky, but it has provided a solution ... but I need a better one
> ....:-)

As far as I know, there's no way to link a graph in Word directly to Access
data. The path you show is the only possibility for a link that does NOT
involve any programming code to update the graph.

For an MS Graph to automatically update to outside data, that data must be a
Word table, in a Word document. Unfortunately, it can't be a linked table
from an outside source because updating the link destroys the bookmark that's
maintaining the link for MS graph.

> Is it possible to name a table, or any embedded object in a Word document?
> In the case of a table, is it possible then to traverse this table using
> VBA. A recommended book perhaps? There is not much information around on
> Word VBA.

Only indirectly, in that you can select a table or an object and apply a
bookmark to it. You then pick whatever up from the bookmark's range.

Set tbl = doc.Bookmarks("Name").Range.Tables(1)

If the embedded object were formatting with textflow, then it would belong to
the Shapes collection, and a Shape does have a .NAME property. However,
working with Shapes presents other difficulties...

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister
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 :-)

Reply to this Message

Richard John - 05 May 2006 03:02 GMT

Thanks very much Cindy for your highly informative response. It encouraged
me to look deeper and I finally came up with this solution (which still uses
the Excel ==> Word traversal):

'*************************************
xlSheet.ChartObjects(1).Activate
With ActiveChart
.ChartArea.Select
.ChartArea.Copy
End With
Selection.GoTo What:=wdGoToBookmark, Name:=sbmkChart
Selection.Paste

'*************************************
The tables and associated charts are each stored in separate worksheets in a
single workbook. This code then just identifies the chart as shown. Works a
treat... :-) No messy artifacts.

> Hi Richard,
>
[quoted text clipped - 21 lines]
> data. The path you show is the only possibility for a link that does NOT
> involve any programming code to update the graph.

Thanks for that. This gives me some encouragement that I've followed the
most feasible path.. <my first time linking Excel and Word via Word VBA)

> For an MS Graph to automatically update to outside data, that data must be
> a
[quoted text clipped - 23 lines]
> http://homepage.swissonline.ch/cindymeister
> http://www.word.mvps.org

Signature

Richard John (Melbourne, Australia)
rjbpond@bigpond.net.au

Reply to this Message

Cindy M -WordMVP- - 05 May 2006 14:08 GMT

Hi Richard,

> The tables and associated charts are each stored in separate worksheets in a
> single workbook. This code then just identifies the chart as shown. Works a
> treat... :-) No messy artifacts.

OK, going this route, I can go you one better and avoid needing to open Excel
or use the Clipboard :-) I didn't want to go into the details of this approach
until you'd settled on how to handle the data, etc.

First, we do this manually so that you can see how it works: click on a chart
object in Excel, then copy. Switch to Word and go over Edit/Paste Special;
activate "Link" and choose a picture format, since you apparently don't require
a dynamic link back to the original.

In the Word document, press Alt+F9 to view the LINK field code that's
maintaining the link (which we're going to get rid of).

In VBA you can insert the LINK field directly into your document to bring in
the chart. No need to have the workbook open in Excel. The only caveat is that
you need to know the chart objects' names in order to reference them. The basic
syntax for inserting the field:

Dim fld as Word.Field
Set fld = ActiveDocument.Fields.Add( _
Range:=Selection.Range, _
Type:=wdFieldEmpty, _
Text:="copy the content of the LINK field here, without the { field
brackets }. You can edit the string so that you can specify the chart name
using a variable, if you wish"
PreserveFormatting:=true
'Now break the link to turn this into a static object
fld.Unlink

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister
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 :-)

Reply to this Message