使用VBA从Microsoft Excel将数据导入PowerPoint图表

Question

使用VBA从Microsoft Excel将数据导入PowerPoint图表

4

我正在尝试使用VBA从Excel将数据导入Powerpoint图表（将数据粘贴到Powerpoint图表对象后面的数据表中）。

我使用以下代码作为示例（来源：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

当我运行这个程序时，PPT会正常打开，然后代码停在这里：

Set oGraph = oPPTShape.OLEFormat.Object

出现了错误信息"OLEFormat (unknown member) : Invalid request. This property only applies to OLE Objects."

我正在使用Excel和PowerPoint 2010。

我做错了什么？我是新手，所以我认为这是很简单的事情。

谢谢

/ Jimmy

- Jimmy Sjöberg

你的代码在 Excel 2003 上运行良好...你用的是哪个版本？你是否设置了引用并按照代码顶部的注释所述完成了所有其他操作？Microsoft Graph 是否已安装？ - Jean-François Corbett

@Jean-François Corbett 我正在使用 Office 2010。所有引用都已设置，其他所有工作都已完成。例如，这个链接可以正常工作：http://www.mahipalreddy.com/vba.htm#pptable 我需要安装 Microsoft Graph 才能做到这一点吗？据我所知，我没有安装它。 - Jimmy Sjöberg

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Jean-François Corbett · Accepted Answer

在PowerPoint 2010中，新的做法是创建一个Excel表格并将其链接到图表的ChartData。

下面是一个示例，展示如何进行操作：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