使用 VBA 从 Microsoft Excel 获取数据到 Powerpoint Graph

Get Data into a Powerpoint Graph from Microsoft Excel using VBA

提问by Jimmy Sj?berg

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

我正在尝试使用 VBA 从 Excel 获取数据到 Powerpoint Graph（将数据粘贴到 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:

当我运行它时，PPT打开得很好，然后代码停在：

Set oGraph = oPPTShape.OLEFormat.Object

with the error message "OLEFormat (unknown member) : Invalid request. This property only applies to OLE Objects."

错误消息“OLEFormat (unknown member) : Invalid request. 此属性仅适用于 OLE 对象。”

I am using Excel and PowerPoint 2010.

我正在使用 Excel 和 PowerPoint 2010。

What am I doing wrong? I'm quite new to all this so I assume it's something simple.

我究竟做错了什么？我对这一切都很陌生，所以我认为这很简单。

Thank you

谢谢

/Jimmy

/吉米

采纳答案by Jean-Fran?ois Corbett

The new way of doing things in PowerPoint 2010 is to create an Excel sheet and link it to the chart's ChartData.

PowerPoint 2010 中的新处理方式是创建 Excel 工作表并将其链接到图表的ChartData.

An example of how to do this is given at http://msdn.microsoft.com/en-us/library/ff973127.aspxand reproduced below for convenience.

http://msdn.microsoft.com/en-us/library/ff973127.aspx提供了如何执行此操作的示例，并在下面复制以方便使用。

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