使用VBA将数据从Microsoft Excel中的Microsoft Excel中获取数据
问题描述:
我想从使用VBA的Excel(将数据粘贴到Powerpoint图形对象后面的数据表中)中将数据转换为Excel中的Powerpoint图形。使用VBA将数据从Microsoft Excel中的Microsoft Excel中获取数据
我使用这个代码为例(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(未知成员):请求无效,此属性仅适用于OLE对象。”
我使用Excel和PowerPoint 2010中
我在做什么错?我对这一切都很陌生,所以我认为这很简单。
谢谢
/吉米
答
在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
+0
谢谢!我昨天发现了一些类似的东西,并使其工作,但它非常丑陋。这工作得很好,但。 – 2011-06-16 08:37:58
您的代码在Excel 2003中正常运行...您有什么版本?您是否设置了引用,并执行了代码顶部的注释中描述的所有其他内容?是否安装了Microsoft Graph? – 2011-06-15 12:10:21
@ Jean-FrançoisCorbett我正在使用Office 2010.所有引用都已设置,其他所有操作都已完成。这适用于例如:http://www.mahipalreddy.com/vba.htm#pptable 我需要安装Microsoft Graph来执行此操作吗? AFAIK我没有安装。 – 2011-06-15 12:20:10