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

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

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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

Selecting the Default Chart

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 

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:

Q176476 OFF: Office Assistant Not Answering Visual Basic Questions

Q163435 VBA: Programming Resources for Visual Basic for Applications

Additional query words: 8.00 ppt8 vba vbe ppt97 xlvbainfo OFF2000 OFF97

Keywords          : kbcode kbinterop kbprg kbdta 
Version           : WINDOWS:97
Platform          : WINDOWS 
Issue type        : kbinfo 

Last Reviewed: July 26, 1999