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 "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
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 PPT2000: Sample Code to Change Source of Linked Excel Worksheet
251337 PPT2000: Sample Visual Basic Code to Update Links in an Excel Chart
Additional query words: vba
Keywords: kbdtacode kbhowto kbprogramming KB267974
