Editing Excel or MSGraph Charts in Word

ajbrogan

New Member
Joined
Nov 3, 2003
Messages
7
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????

Code:
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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Watch MrExcel Video

Forum statistics

Threads
1,123,304
Messages
5,600,864
Members
414,409
Latest member
FloordAlex

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top