Microsoft KB Archive/165872

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

Article ID: 165872

Article Last Modified on 8/17/2005

-

APPLIES TO


 * Microsoft Graph 97
 * Microsoft Office 97 Standard Edition
 * Microsoft Excel 97 Standard Edition
 * Microsoft PowerPoint 97 Standard Edition

-



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.
 * 1) Switch to PowerPoint by clicking outside the chart.
 * 2) 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.

