Get Data into a Powerpoint Graph from Microsoft Excel using VBA
20,387
The new way of doing things in PowerPoint 2010 is to create an Excel sheet and link it to the chart's ChartData
.
An example of how to do this is given at http://msdn.microsoft.com/en-us/library/ff973127.aspx and reproduced below for convenience.
Sub CreateChart()
Dim myChart As Chart
Dim gChartData As ChartData
Dim gWorkBook As Excel.Workbook
Dim gWorkSheet As Excel.Worksheet
' Create the chart and set a reference to the chart data.
Set myChart = ActivePresentation.Slides(1).Shapes.AddChart.Chart
Set gChartData = myChart.ChartData
' Set the Workbook and Worksheet references.
Set gWorkBook = gChartData.Workbook
Set gWorkSheet = gWorkBook.Worksheets(1)
' Add the data to the workbook.
gWorkSheet.ListObjects("Table1").Resize gWorkSheet.Range("A1:B5")
gWorkSheet.Range("Table1[[#Headers],[Series 1]]").Value = "Items"
gWorkSheet.Range("A2").Value = "Coffee"
gWorkSheet.Range("A3").Value = "Soda"
gWorkSheet.Range("A4").Value = "Tea"
gWorkSheet.Range("A5").Value = "Water"
gWorkSheet.Range("B2").Value = "1000"
gWorkSheet.Range("B3").Value = "2500"
gWorkSheet.Range("B4").Value = "4000"
gWorkSheet.Range("B5").Value = "3000"
' Apply styles to the chart.
With myChart
.ChartStyle = 4
.ApplyLayout 4
.ClearToMatchStyle
End With
' Add the axis title.
With myChart.Axes(xlValue)
.HasTitle = True
.AxisTitle.Text = "Units"
End With
'myChart.ApplyDataLabels
' Clean up the references.
Set gWorkSheet = Nothing
' gWorkBook.Application.Quit
Set gWorkBook = Nothing
Set gChartData = Nothing
Set myChart = Nothing
End Sub
Author by
Admin
Updated on June 16, 2020Comments
-
Admin almost 4 years
I am trying to get data into a Powerpoint Graph from Excel using VBA (pasting data into the datasheet that's behind a Powerpoint Graph Object).
I'm using this code as an example (source):
'Code by Mahipal Padigela 'Open Microsoft Powerpoint,Choose/Insert a Graph type Slide(No.8), then double click to add a graph and click... '...outside the graph to close the Datasheet, then rename the Graph to "Mychart",Save and Close the Presentation 'Open Microsoft Excel, add some test data to Sheet1(This example assumes that you have some test data... '...(numbers between 0-100) in Rows 2,3,4 and Columns B,C,D,E). 'Open VBA editor(Alt+F11),Insert a Module and Paste the following code in to the code window 'Reference 'Microsoft Powerpoint Object Library' (VBA IDE-->tools-->references) 'Reference 'Microsoft Graph Object Library' (VBA IDE-->tools-->references) 'Change "strPresPath" with full path of the Powerpoint Presentation created earlier. 'Change "strNewPresPath" to where you want to save the new Presnetation to be created later 'Close VB Editor and run this Macro from Excel window(Alt+F8) Dim oPPTApp As PowerPoint.Application Dim oPPTShape As PowerPoint.Shape Dim oPPTFile As PowerPoint.Presentation Public oGraph As Graph.Chart Dim SlideNum As Integer Sub PPGraphMacro() Dim strPresPath As String, strExcelFilePath As String, strNewPresPath As String strPresPath = "H:\PowerPoint\Presentation1.ppt" strNewPresPath = "H:\PowerPoint\New1.ppt" Set oPPTApp = CreateObject("PowerPoint.Application") oPPTApp.Visible = msoTrue Set oPPTFile = oPPTApp.Presentations.Open(strPresPath) SlideNum = 1 oPPTFile.Slides(SlideNum).Select Set oPPTShape = oPPTFile.Slides(SlideNum).Shapes("Mychart") Set oGraph = oPPTShape.OLEFormat.Object Sheets("Sheet1").Activate oGraph.Application.DataSheet.Range("A1").Value = Cells(2, 2).Value oGraph.Application.DataSheet.Range("A2").Value = Cells(3, 2).Value oGraph.Application.DataSheet.Range("A3").Value = Cells(4, 2).Value oGraph.Application.DataSheet.Range("B1").Value = Cells(2, 3).Value oGraph.Application.DataSheet.Range("B2").Value = Cells(3, 3).Value oGraph.Application.DataSheet.Range("B3").Value = Cells(4, 3).Value oGraph.Application.DataSheet.Range("C1").Value = Cells(2, 4).Value oGraph.Application.DataSheet.Range("C2").Value = Cells(3, 4).Value oGraph.Application.DataSheet.Range("C3").Value = Cells(4, 4).Value oGraph.Application.DataSheet.Range("D1").Value = Cells(2, 5).Value oGraph.Application.DataSheet.Range("D2").Value = Cells(3, 5).Value oGraph.Application.DataSheet.Range("D3").Value = Cells(4, 5).Value oGraph.Application.Update oGraph.Application.Quit oPPTFile.SaveAs strNewPresPath oPPTFile.Close oPPTApp.Quit Set oGraph = Nothing Set oPPTShape = Nothing Set oPPTFile = Nothing Set oPPTApp = Nothing MsgBox "Presentation Created", vbOKOnly + vbInformation End Sub
When I run this the PPT opens just fine and the code then stops at:
Set oGraph = oPPTShape.OLEFormat.Object
with the error message "OLEFormat (unknown member) : Invalid request. This property only applies to OLE Objects."
I am using Excel and PowerPoint 2010.
What am I doing wrong? I'm quite new to all this so I assume it's something simple.
Thank you
/Jimmy
-
Admin almost 13 yearsThank you! I found something similar yesterday and made it work, but it was very ugly. This worked fine though.