How To Automate An Embedded MSGraph Object with Visual Basic (244589)

MORE INFORMATION

You can create a chart in a Microsoft Office document by using Automation from Visual Basic. The following code creates a new document in Microsoft Word, embeds and formats a chart, and then adds data to the chart.

1. Start a new Standard Exe project in Visual Basic. Form1 is created by default.
2. On the Project menu, click References. Set references in the project to the Microsoft Word Object Library and the Microsoft Graph Object Library.
3. Paste the following code into the Form_Load Event procedure:

   
      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")
      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
   					

4. Save the project.
5. Press F5 to run the project. A new Word document is created that contains an embedded chart populated with data.