How do I edit an existing graph datasheet in word using VBA?

[JAG777]

I have a .dot file with a number of microsoft graph chart (originally created
from Word tables) objects.
I want to be able to update the datasheet (and tables) of each of the graphs
from the user input captured in text boxes of a user form.
I am struggling with being able to identify the individual chart and then
update its individual data sheet - is this possible?

 

[Jean-Guy Marcil]

JAG777 was telling us:
JAG777 nous racontait que :

I have a .dot file with a number of microsoft graph chart (originally
created from Word tables) objects.
I want to be able to update the datasheet (and tables) of each of the
graphs from the user input captured in text boxes of a user form.
I am struggling with being able to identify the individual chart and
then update its individual data sheet - is this possible?

You need to set a reference to "Microsoft Graph 11.0 Object Library" in
Tools > References (VBA Editor menu bar).

Declare some variables like:

Dim LE As Word.OLEFormat
Dim diag As Graph.Chart

Then, set the variables, like:

Set LE = ActiveDocument.Shapes(1).OLEFormat
LE.DoVerb wdOLEVerbShow
Set diag = LE.Object

If it is an inlineshape use
Set LE = ActiveDocument.Shapes(1).OLEFormat

Finally manipulate the object like this:

With diag.Application.DataSheet
.cells
.columns
.rows
End With

or

With diag
.HasLegend = False
.Application.PlotBy = xlColumns
.Width = CentimetersToPoints(25)
.PlotArea.Width = CentimetersToPoints(20)
.Axes(xlCategory, xlPrimary).TickLabelSpacing = 1
.Axes(xlCategory, xlPrimary).TickLabels.Orientation = 90
End With

And do not forget to destroy the objects:

'Deactivate the graph object
diag.Application.Quit

'Clear objects
Set diag = Nothing
Set LE = Nothing

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org