使用VBA将数据从Excel导出到现有的PowerPoint幻灯片 [英] Exporting data from Excel to an existing PowerPoint slide using VBA

问题描述

我正在为一些简单的数据分析写一个小的excel加载项，但不幸的是我不是VBA程序员;）

我现在所做的：

• 从巨大的excel工作簿中获取数据并执行计算


• 将结果写入整齐预格式化的骨架文件

我接下来要做什么：

• 将直接从excel vba代码写入完全相同的数据到已经整齐地准备好的ppt 幻灯片
  。

我认为可以在幻灯片中定义某些文本框，然后将值放入它们...只是没有找到任何东西！

对此的任何帮助都表示赞赏;）

这是excel& powerpoint 2007，但是我想尽可能独立版本。

TIA

解决方案

这里是我在 Mahipal Padigela的VBA网站

（它也在计算器）

 ''＃代码由Mahipal Padigela 
''＃打开Microsoft Powerpoint，选择/插入表格类型幻灯片（No.4） ，然后双击添加... 
''＃...表（3 Cols& 2行）然后将表重命名为Table1，保存并关闭演示文稿
'打开Microsoft Excel，将一些测试数据添加到Sheet1（此示例假设您有一些数据... 
＃...行1,2和列1,2,3）
'打开VBA编辑器（Alt + F11），插入模块并将以下代码粘贴到代码窗口
''＃引用Microsoft Powerpoint对象库（VBA IDE  - >工具 - >引用）
''＃使用之前创建的Powerpoint Presentation的完整路径更改strPresPath。 
''＃将strNewPresPath更改为要保存要稍后创建的新的预定义的位置
'关闭VB编辑器并从Excel窗口运行此宏（Alt + F8）
 
 Dim oPPTApp as PowerPoint.Application 
 Dim oPPTShape As PowerPoint.Shape 
 Dim oPPTFile As PowerPoint.Presentation 
 Dim SlideNum As Integer 
 Sub PPTableMacro（）
 Dim strPresPath As String，strExcelFilePath As String，strNewPresPath As String 
 strPresPath =H：\PowerPoint\Presentation1.ppt
 strNewPresPath =H：\PowerPoint\\\
ew1.ppt
 
设置oPPTApp = CreateObject（PowerPoint.Application）
 oPPTApp.Visible = msoTrue 
设置oPPTFile = oPPTApp.Presentations.Open（strPresPath）
 SlideNum = 1 
 oPPTFile.Slides（SlideNum）。选择
设置oPPTShape = oPPTFile.Slides（SlideNum）。形状（Table1）
 
表单（Sheet1）。激活
与oPPTShape.Table 
 .Cell（1，1）.Shape.TextFrame.TextRan ge.Text = Cells（1，1）.Text 
 .Cell（1，2）.Shape.TextFrame.TextRange.Text = Cells（1，2）.Text 
 .Cell（1，3 ）.Shape.TextFrame.TextRange.Text = Cells（1，3）.Text 
 .Cell（2，1）.Shape.TextFrame.TextRange.Text = Cells（2，1）.Text 
 .Cell（2，2）.Shape.TextFrame.TextRange.Text = Cells（2，2）.Text 
 .Cell（2，3）.Shape.TextFrame.TextRange.Text = Cells（2,3） .Text 
结束
 
 oPPTFile.SaveAs strNewPresPath 
 oPPTFile.Close 
 oPPTApp.Quit 
 
设置oPPTShape = Nothing 
设置oPPTFile = Nothing 
设置oPPTApp = Nothing 
 
 MsgBoxPresentation Created，vbOKOnly + vbInformation 
 End Sub 
  

，这里有一些额外的帮助使用VBA自动化Powerpoint 和 h ttp：//www.mahipalreddy.com/vba/ppvba.htm

获取个别幻灯片的名称，尝试如下所示：

  Dim oSlide As Slide 
 
对于每个oSlide在ActiveWindow.Presentation.Slides 
 Debug.Print oSlide.Name 
 Next 
 End Sub 
  

>电源指向正确的方向！ （so droll）

Philip

i am writing a small excel add-in for some simple data analytics but unfortunately i am not a VBA-programmer ;)

what i have done up to now:

• fetch data from a huge excel workbook and perform calculations
• write the results to a neatly preformatted excel 'skeleton' file

what i want to do next:

• write the very same data to an also already neatly prepared ppt slide directly from the excel vba code.

i think it is possible to define some sort of textboxes inside the slide and then just put the values into them... just havent found anything yet !

any help on this is appreciated ;)

this is excel&powerpoint 2007, but i want to do it as version independent as possible.

TIA

解决方案

here is some code I found on Mahipal Padigela's VBA website

(it is also referenced on StackOverFlow)

''# Code by Mahipal Padigela
''# Open Microsoft Powerpoint,Choose/Insert a Table type Slide(No.4), then double click to add a...
''# ...Table(3 Cols & 2 Rows) then rename the Table to "Table1", Save and Close the Presentation
''# Open Microsoft Excel, add some test data to Sheet1(This example assumes that you have some data in...
''# ... Rows 1,2 and Columns 1,2,3)
''# 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)
''# 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
Dim SlideNum As Integer
Sub PPTableMacro()
    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("Table1")

    Sheets("Sheet1").Activate
    with oPPTShape.Table
        .Cell(1, 1).Shape.TextFrame.TextRange.Text = Cells(1, 1).Text
        .Cell(1, 2).Shape.TextFrame.TextRange.Text = Cells(1, 2).Text
        .Cell(1, 3).Shape.TextFrame.TextRange.Text = Cells(1, 3).Text
        .Cell(2, 1).Shape.TextFrame.TextRange.Text = Cells(2, 1).Text
        .Cell(2, 2).Shape.TextFrame.TextRange.Text = Cells(2, 2).Text
        .Cell(2, 3).Shape.TextFrame.TextRange.Text = Cells(2, 3).Text
    end with 

    oPPTFile.SaveAs strNewPresPath
    oPPTFile.Close
    oPPTApp.Quit

    Set oPPTShape = Nothing
    Set oPPTFile = Nothing
    Set oPPTApp = Nothing

    MsgBox "Presentation Created", vbOKOnly + vbInformation
End Sub

and here is some extra help on Automating Powerpoint with VBA and http://www.mahipalreddy.com/vba/ppvba.htm

to get the names of individual slides try something like the below:

Dim oSlide As Slide

For Each oSlide In ActiveWindow.Presentation.Slides
    Debug.Print oSlide.Name
Next
End Sub

that should power point you in the right direction ! (so droll)

Philip

这篇关于使用VBA将数据从Excel导出到现有的PowerPoint幻灯片的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！