Altering the data behind a chart in VBA

[Rod]

I want to be able to alter the data behind a chart using code.

Something like

Dim ObjChart As Word.InlineShape

Set ObjChart = ObjDoc.InlineShapes(1)

ObjChart.Cells(1,2).Range.Text = 42 'but that's wrong

etc

Many thanks for any help

 

[Jean-Guy Marcil]

I want to be able to alter the data behind a chart using code.

Something like

Dim ObjChart As Word.InlineShape

Set ObjChart = ObjDoc.InlineShapes(1)

ObjChart.Cells(1,2).Range.Text = 42 'but that's wrong

Close, but no cigar!

You need to go through the OLEFormat property...


Dim oChart As Graph.Chart

Set oChart = ActiveDocument.InlineShapes(1).OLEFormat.Object

With oChart
.Application.DataSheet.Range("A1").Value = 48
'etc.
End With

But, for this code to work, you need to set a reference to the "Microsoft
Graph xx.0 Object Library" (VBA Editor > Tools > References...)

Otherwise, you can use

Dim oChart As Object

But you will not get the IntelliSense help in the VBA editor...

 

[Rod]

Close, but no cigar!

You need to go through the OLEFormat property...


Dim oChart As Graph.Chart

Set oChart = ActiveDocument.InlineShapes(1).OLEFormat.Object

With oChart
.Application.DataSheet.Range("A1").Value = 48
'etc.
End With

But, for this code to work, you need to set a reference to the "Microsoft
Graph xx.0 Object Library" (VBA Editor > Tools > References...)

Otherwise, you can use

Dim oChart As Object

But you will not get the IntelliSense help in the VBA editor...

Not quite working. Have set the reference OK but get the following error
message on the Set objChart = ...
"Class does not support Automation or does not support expected interface."

I am working in Access and manipulating a Word document.

Dim objDoc As Word.Document, objChart As Graph.Chart

Set objDoc = objWord.Documents.Open(..........

Set objChart = objDoc.InlineShapes(1).OLEFormat.Object

The last line throws the error.

I tried changing objChart to an Object but that did not help.

 

[Jean-Guy Marcil]

Not quite working. Have set the reference OK but get the following error
message on the Set objChart = ...
"Class does not support Automation or does not support expected interface."

I am working in Access and manipulating a Word document.

Dim objDoc As Word.Document, objChart As Graph.Chart

Set objDoc = objWord.Documents.Open(..........

Set objChart = objDoc.InlineShapes(1).OLEFormat.Object

The last line throws the error.

Sorry, my bad, you need to Activate it first...

Dim objWord As Word.Application
Dim objDoc As Word.Document
Dim objChart As Graph.Chart

Set objWord = New Word.Application
Set objDoc = objWord.Documents.Open("C:\My Documents\TestChart.doc")

With objDoc
.InlineShapes(1).OLEFormat.Activate
Set objChart = .InlineShapes(1).OLEFormat.Object
End With

With objChart
.Application.DataSheet.Range("A1").Value = 50
'etc.
End With

objDoc.Close wdSaveChanges
objWord.Quit

Set objWord = Nothing
Set objDoc = Nothing
Set objChart = Nothing

 

[Rod]

That worked.

Thank you