OFF97: Sample Macro That Converts the Default Chart to Microsoft Excel (165872)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. This macro requires that you select the default chart on a PowerPoint 97 slide before you run the macro.

Selecting the Default Chart

To create and select the default chart, use the following steps:

1. Start Microsoft PowerPoint 97 and create a blank slide.
2. To create the default chart on the slide, click Chart on the Insert menu.
   
   The default chart appears on the slide. In Visual Basic for Applications, this chart is the Graph object.
3. Switch to PowerPoint by clicking outside the chart.
4. Click the chart once to select it.

Sample Macro

Sub ConvertGraphToExcel()

   ' Used for error trapping.
   On Error Resume Next

   ' Clear the error object.
   Err.Clear

   ' Holds a reference to an object.
   Dim oGraph As Object

   ' Check whether the selection is a Graph 8 object.
   With ActiveWindow.Selection.ShapeRange(1).OLEFormat

      If Err.Number <> 0 Then

         ' A run-time error is generated if the selection is not a chart.
         ' This code exits the macro if the selection is not a chart.
         MsgBox "Select one graph and run the macro again.", vbExclamation

         ' Stop the macro.
         End
      Else

         If .ProgID <> "MSGraph.Chart.8" Then
            ' A run-time error is generated if the selection is not a
            ' chart.
            ' This code exits the macro if the selection is not a chart.
            MsgBox "Select one graph and run the macro again.", _
            vbExclamation
            ' Stop the macro.
            End
         End If
      End If
   End With

   ' Reference the Graph object.
   Set oGraph = ActiveWindow.Selection.ShapeRange(1).OLEFormat.Object

   ' Call the CreateExcelChart procedure and pass a reference to the
   ' Graph 8 object.
   CreateExcelChart oGraph
End Sub

Sub CreateExcelChart(oChart As Object)

   On Error Resume Next

   Dim oExcel As Object
   Dim i As Long, j As Long

   ' Clear the Err object.
   Err.Clear

   ' Reference the Microsoft Excel object model.
   Set oExcel = GetObject(, "Excel.Application.8")
   If Err.Number <> 0 Then

      ' Clear the Err object.
      Err.Clear
      Set oExcel = CreateObject("Excel.Application.8")

      ' Check whether Microsoft Excel can be started.
      If Err.Number <> 0 Then
         MsgBox "Unable to start Excel 97. Try starting Excel" _
            & " and run the macro again.", vbExclamation
         ' Stop the macro.
         End
      End If

      ' Make Microsoft Excel visible.
      oExcel.Visible = True
   End If
   ' Create a new workbook.
   oExcel.Workbooks.Add
   ' Transfer the data in the Graph datasheet to
   For i = 1 To 4

       For j = 1 To 5
         oExcel.Worksheets(1).Cells(j, i).Value = _
            oChart.Application.DataSheet.Cells(i, j).Value
       Next j

   Next i

   'Add a chart to the Excel Workbook.
   oExcel.Charts.Add

   'Set up the chart.
   With oExcel.ActiveChart

      .ChartType = oChart.ChartType
      .Elevation = oChart.Elevation
      .Perspective = oChart.Perspective
      .Rotation = oChart.Rotation
      .RightAngleAxes = oChart.RightAngleAxes
      .AutoScaling = oChart.AutoScaling

      ' Specify the data source.
      .SetSourceData Source:=oExcel.Sheets("Sheet1").Range("A1:D5")

      ' Place the chart on worksheet 1.
      .Location Where:=xlLocationAsObject, Name:="Sheet1"
   End With
End Sub
				

NOTE: To use this code with Graph objects that are not the default chart, modify the macro.