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

View products that this article applies to.

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).

Back to the top

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.

Back to the top

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 "Presentation1.ppt", 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("PowerPoint.Application")
'
' Set PowerPoint to be Visible.
'
   oPPTApp.Visible = msoTrue
'
' Open Presentation1.ppt from My Documents.
'
   oPPTApp.Presentations.Open "C:\My Documents\Presentation1.ppt"
'
' Set rngNewRange to the collection of cells in the active Excel
' workbook and active sheet.
'
   Set rngNewRange = ActiveSheet.Range("A1:F4")
'
' 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 = "MSGraph.Chart.8" 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 "00" (two zeros). To designate
' a range to start in the second row, first column, you would use "01".
' Likewise first row, second column is "A0". 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 "True". The default
' choice for the Paste method is "False".
'
               oGraph.Application.DataSheet.Range("00").Paste True

            End If

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

Back to the top

REFERENCES

For additional information about how to update links to Excel workbooks from PowerPoint, click the article numbers below to view the articles in the Microsoft Knowledge Base:

222708 (http://support.microsoft.com/kb/222708/EN-US/ ) PPT2000: Sample Code to Change Source of Linked Excel Worksheet

251337 (http://support.microsoft.com/kb/251337/EN-US/ ) PPT2000: Sample Visual Basic Code to Update Links in an Excel Chart

Back to the top