Editing Excel or MSGraph Charts in Word

[ajbrogan]

I have a Word document that will need to contain about 50 charts, which need to be embedded so that each chart is linked to its underlying data. I can use either Excel charts or MSGraph charts in Word, it doesn't matter to me - although Excel charts would be slightly more preferable.

What I need is VBA code that runs through all 50 charts in Word and makes the formatting uniform. I have written some code that seems like it should work for either Excel charts or MSGraph charts (shown below -- a sample of all the formatting I'd like to do).

Note: Making the graphs in Excel and then Copy/Paste Special as Picture into Word is not an option since it is imperative that the data are still available and can be changed at a later date.

Problems: (Same problems both in Word/Excel 2000 and Word/Excel 2002)

If chart is Excel chart, then the code below fails at the line
MyChart.PlotArea.Border.LineStyle = xlNone
with the error "Object doesn't support this property or method."
I obtain similar errors if I switch the code around so that a different piece of the formatting job comes first.

If chart is MSGraph chart, then the code below fails at the line
With MyChart.Axes(xlCategory)
with the error "Unable to get the Axes property of the Chart class."
This Axes property seems to be the only problem I'm having among all my formatting needs when the charts are MSGraph charts.

Here's my code. Help????

Sub FormatCharts()
    
    Dim MyChart As Object
    Dim MyShape As InlineShape
    
    For i = 1 To ActiveDocument.InlineShapes.Count    
        Set MyShape = ActiveDocument.InlineShapes(i)
        If MyShape.OLEFormat.ProgID Like "MSGraph*" or _
             MyShape.OLEFormat.ProgID Like "Excel.Chart*" Then
            
              With MyShape.OLEFormat
                  .Edit
                  Set MyChart = .Object
              End With
            
              MyChart.PlotArea.Border.LineStyle = xlNone
             
              If MyChart.HasLegend = True Then
                  MyChart.Legend.AutoScaleFont = False
                  With MyChart.Legend.Font
                      .Name = "Arial"
                      .FontStyle = "Regular"
                      .Size = 11
                  End With
              End If
             
              With MyChart.Axes(xlCategory)
                  .HasMajorGridlines = False
                  .HasMinorGridlines = False
              End With
              With MyChart.Axes(xlValue)
                  .HasMajorGridlines = False
                  .HasMinorGridlines = False
              End With
            
        End If
    Next i
End Sub