How to link a Word table with a Word graph

Article contributed by Daryl Lucas

Suppose you want to have a graph, such as a pie chart, that is linked to formfields in a protected document. In each formfield, the user will enter numbers. You want the graph to update to reflect the new data. And you want to use MS Graph instead of Excel, to keep the whole thing lightweight and self-contained.

I know that most people use Excel to create graphs in Word documents, but I like using MS Graph. Like a compact car, it does most of what a charting tool needs to do without the expense or heavy duty lifting of a Nimitz-class Excel chart. It's lightweight and functional.

But like Excel, it is also a completely different application, and that makes for some confusion. For example, many new users of Word intuitively expect MS Graph charts to be updateable from Word tables. Alas, they are not. An MS Graph graph is an embedded object. It cannot be linked to the data in a protected table in any way whatsoever. The chart that you see gets its values from a DataSheet that comes with the object.

But you can use the following macro to do the charting updates, and the results are far easier to achieve and far faster to get than Excel results. You can use VBA to export the values in your table to the DataSheet in the MS Graph object.

In English, you do this:

1.	Get the values in the FormFields.
2.	Open the graph for editing.
3.	Put the FormField values in the appropriate DataSheet cells.
4.	Update the chart.
5.	Shut down MSGraph.

The code that follows does exactly that. I run it OnExit from the last FormField entered in the form.

Public Sub UpdateChart()

Dim oMSGraphWrapper As Word.InlineShape
Dim oDoc As Word.Document
Dim oMSGraphObject As Object
Dim oDataSheet As Graph.DataSheet
Dim iEntry1 As Long
Dim iEntry2 As Long

Set oDoc = ActiveDocument
oDoc.Application.ScreenUpdating = False
Set oMSGraphWrapper = oDoc.InlineShapes(1)
iEntry1 = oDoc.FormFields("txtEntry1").Result
iEntry2 = oDoc.FormFields("txtEntry2").Result
oMSGraphWrapper.OLEFormat.Edit
Set oMSGraphObject = oMSGraphWrapper.OLEFormat.Object
Set oDataSheet = oMSGraphObject.Application.DataSheet

With oDataSheet
    .Range("a1").Value = iEntry1
    .Range("b1").Value = iEntry2
End With

With oMSGraphObject.Application
    .Update
    .Quit
End With

oDoc.Application.ScreenUpdating = True

Set oDataSheet = Nothing
Set oMSGraphWrapper = Nothing
Set oDoc = Nothing
Set oMSGraphObject = Nothing

End Sub

If you want to see how this works, just make sure you do three things:

1.	Have a table with two numeric FormFields named txtEntry1 and txtEntry2.
2.	Set at least one of the OnEntry or OnExit macros to the UpdateChart macro.
3.	Set a reference to the Microsoft Graph 8.0 Object Library in the appropriate template in the Visual Basic Editor. (Under Tools + References).