Microsoft KB Archive/267974

= GRAPH2000: VBA: How to Paste New Excel Data into an Existing Graph =

Article ID: 267974

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft Graph 2000
 * Microsoft PowerPoint 2000 Standard Edition

-



This article was previously published under Q267974



SUMMARY
This article provides sample Visual Basic for Applications (VBA) code that takes a range of cells from a Microsoft Excel 2000 worksheet and pastes the data into a Microsoft Graph 2000 object inside another Microsoft Office 2000 program (in this case, PowerPoint).



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.

Sample Code
This sample assumes the following:
 * An existing Excel workbook.
 * A range of cells, with labels for both data series and categories.
 * A PowerPoint presentation called &quot;Presentation1.ppt&quot;, which is saved in the My Documents folder.
 * Presentation1.ppt has one slide with a title and an existing graph.
 * The range of cells in the Excel workbook is the same size as the range of cells in the existing Graph datasheet.

Sub UpdateGraph Dim oPPTApp As PowerPoint.Application Dim oPPTShape As PowerPoint.Shape Dim rngNewRange As Excel.Range Dim oGraph As Object ' ' Set oPPTApp to PowerPoint by creating a new instance of PowerPoint. ' If PowerPoint is already open, you would instead use the GetObject ' method instead. '     Set oPPTApp = CreateObject(&quot;PowerPoint.Application&quot;) ' ' Set PowerPoint to be Visible. '  oPPTApp.Visible = msoTrue ' ' Open Presentation1.ppt from My Documents. '  oPPTApp.Presentations.Open &quot;C:\My Documents\Presentation1.ppt&quot; ' ' Set rngNewRange to the collection of cells in the active Excel ' workbook and active sheet. '  Set rngNewRange = ActiveSheet.Range(&quot;A1:F4&quot;) ' ' Select the range then copy it. '  rngNewRange.Select rngNewRange.Copy ' ' On slide one of Presentation1.ppt, loop through each shape. '     With oPPTApp.ActivePresentation.Slides(1) For Each oPPTShape in .Shapes ' ' Check to see whether shape is an OLE object. '        If oPPTShape.Type = msoEmbeddedOLEObject Then ' ' Check to see whether OLE object is a Graph 2000 object. The ProgID ' is case sensitive. '           If oPPTShape.OLEFormat.ProgID = &quot;MSGraph.Chart.8&quot; Then ' ' Set oGraph to the Graph object on the slide. '              Set oGraph = oPPTShape.OLEFormat.Object ' ' Paste the cell range into the upper leftmost cell of the graph ' datasheet. This position is designated &quot;00&quot; (two zeros). To designate ' a range to start in the second row, first column, you would use &quot;01&quot;. ' Likewise first row, second column is &quot;A0&quot;. This will also link the ' datasheet to the Excel Workbook cell range. If you do not want to ' link to the Workbook, just omit the word &quot;True&quot;. The default ' choice for the Paste method is &quot;False&quot;. '              oGraph.Application.DataSheet.Range(&quot;00&quot;).Paste True

End If

End If ' ' Select the next shape on the slide. '     Next oPPTShape End With End Sub

