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



The information in this article applies to:

  • Microsoft Graph 97
  • Microsoft Office 97 for Windows
  • Microsoft Excel 97 for Windows
  • Microsoft PowerPoint 97 for Windows

This article was previously published under Q165872

SUMMARY

This article includes a sample Microsoft Visual Basic for Applications macro that converts the default chart in a PowerPoint 97 slide to a Microsoft Excel 97 chart. The macro creates a new Microsoft Excel workbook, and then transfers the chart and datasheet to the first worksheet in the workbook.

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.

REFERENCES

For more information about creating Visual Basic for Applications macros, click the Office Assistant in Microsoft PowerPoint, type "how to create a macro," click Search, and then click to view "Create a macro in Visual Basic Editor."

For more information about running Visual Basic for Applications macros, click the Office Assistant in Microsoft PowerPoint, type "how to run a macro," click Search, and then click to view "Run a macro."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If the Assistant is not able to answer your query, please see the following article in the Microsoft Knowledge Base:

176476 OFF: Office Assistant Not Answering Visual Basic Questions

For more information about getting help with Visual Basic for Applications, please see the following article in the Microsoft Knowledge Base:

163435 VBA: Programming Resources for Visual Basic for Applications


Modification Type:MinorLast Reviewed:8/17/2005
Keywords:kbcode kbinfo kbinterop kbProgramming KB165872