Referencing a MS Graph Chart 1

[huBBLe]

I need to make a reference to a MS Graph chart that i have inserted in a Word document. how do i go about doing it? do i use the path of the Word document?

 

[MarkSweetland]

Untested, but will get you started:

Dim WrdApp As Word.Application
Dim WrdDoc As Word.Document
Dim MSCht As Word.InlineShape
Dim oGraph As Object
Dim oDataSheet As Object

Set WrdApp = New Word.Application

WrdApp.Visible = True
    
Set WrdDoc = App.Documents.Add

Set MSCht = Selection.InlineShapes.AddOLEObject(ClassType:="MSGraph.Chart", _
        LinkToFile:=False, DisplayAsIcon:=False)
   ' Find Chart Object

Set oGraph = WrdDoc.InlineShapes(1).OLEFormat.object
    
With oGraph.Application.Chart
  .ChartType = xlColumnClustered
  .Axes(xlCategory).HasMajorGridlines = True
  .Axes(xlCategory).HasMinorGridlines = False
  .HasTitle = True
  .ChartTitle.Text = "Title Here"
  .HasLegend = True
  .Legend.Font.ColorIndex = 5
  .Legend.Font.Size = 8
  .Legend.Height = 72 * 2
  .Legend.Width = 72 * 3
  .ApplyDataLabels
  .SeriesCollection(1).HasDataLabels = True
  .SeriesCollection(1).DataLabels.Font.Size = 10 - UBound(arrTemp)
  .SeriesCollection(1).DataLabels.Font.Color = RGB(255, 255, 255)
  .SeriesCollection(1).DataLabels.AutoScaleFont = False
  .SeriesCollection(1).DataLabels.Position = xlLabelPositionInsideEnd

End With

Set oDataSheet = oGraph.Application.DataSheet

' Clear the datasheet.
oDataSheet.Cells.Clear
    
' set series
oDataSheet.Cells(1, 2).Value = "Category"
oDataSheet.Cells(2, 2).Value = 6 ' Value
oDataSheet.Cells(2, 2).NumberFormat = "#.00"

oGraph.Application.Quit
 
Set oGraph = Nothing
Set WrdDoc = Nothing
Set WrdApp = Nothing

 

[huBBLe]

Hi Mark,

thanks for the reply. tried running the above but got some errors. anyway, i found another article that describes how to achieve wat i want (to a certain extent) which looks similar to urs. have a look:

'************************************************************
Private Sub Form_Load()
Dim oWordApp As Word.Application
Dim oWordDoc As Word.Document

Dim oShape As Word.Shape
Dim oGraphChart As Graph.Chart

'Create a new document in Word.

Set oWordApp = CreateObject("Word.Application&quot
oWordApp.Visible = True
Set oWordDoc = oWordApp.Documents.Add

'Add some text to the document.
oWordDoc.Content.Text = "This is my new chart:"

'Embed a chart on the document.
Set oShape = oWordDoc.Shapes.AddOLEObject( _
Left:=100, Top:=100, Width:=350, Height:=200, _
ClassType:="MSGraph.Chart", DisplayAsIcon:=False)
Set oGraphChart = oShape.OLEFormat.object

With oGraphChart

'Format the embedded chart.
.ChartArea.Font.Size = 8
.Application.Update
.ChartType = xl3DBarClustered
.HasTitle = True
.ChartTitle.Text = "Sales per Product"
.ChartTitle.Font.Size = 12
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Caption = "Dollars ($)"
.ChartArea.AutoScaleFont = False

'Add data for the chart to the DataSheet in MSGraph.
With .Application.DataSheet

.Cells.Clear

'Add the chart row labels.
.Cells(2, 1).Value = "Widgets"
.Cells(3, 1).Value = "Gadgets"
.Cells(4, 1).Value = "Gizmos"

'Add the chart column labels.
.Cells(1, 2).Value = "1999"
.Cells(1, 3).Value = "2000"

'Add data to the chart.
Dim r As Integer, c As Integer
For r = 2 To 4
For c = 2 To 3
.Cells(r, c).Value = Rnd() * 100000
Next
Next

End With

.Application.Update 'Update the changes
.Application.Quit 'and deactivate the chart.

End With

'Clean up.
Set oGraphChart = Nothing
Set oShape = Nothing
Set oWordDoc = Nothing
Set oWordApp = Nothing
End Sub

'************************************************************

my requirement is to be able to reference a specific chart in the Word document that already exist. both your way and the above's inserts a new chart, which is not exactly what i wanted. i tried modifying the code to suit my need but its really a tremendous task as i'm still a newbie in VB. i juz keep getting lots of syntax errors.

The above code is from microsoft and i tested it. it works. is it possible that some modifications be made to the declarations, like the " Set oGraph = ..... " statement, so that i can make it reference an existing chart?

Thank You

PS: I noticed that there are very limited books, if not none, on automating MS Graph Charts. I did a search on this forum and found that there were few responses regarding this topic. Is this area something new or it is so seldom used that few people actually knows how to do it?

 

[MarkSweetland]

You're right--MSGraph documentation is hard to find. Trial and error is probably the only way to get anything done...

One way to find the chart object is by itterating through the InlineShapes collection of the Document:

For i = 0 To Doc.InlineShapes.Count
  strName = CStr(Doc.InlineShapes(i).OLEFormat.ClassType)
  If InStr(1, strName, "MSGraph") > 0 Then
    Set oShape = Doc.InlineShapes(i)
  End If
Next

if not oShape is nothing then 
    '... found the chart
end if