Exporting data from Excel to an existing PowerPoint slide using VBA

Question

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

Solution

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

Comments

[Admin]

thanks, you really excel at pointing out! i had also found that. however, it seems there is no easy way of getting the individual shape names directly from power point. so i think i'll just have to try and check them in the immediate window and then set them accordingly.

[Our Man in Bananas]

yeah, you would have to use ?ActiveWindow.Selection.SlideRange.Name after selecting them, or loop through all of the slideranges in the active window. I have edited my answer to add that!

[Admin]

i have just found out that you can view/rename the objects using the Arrange ribbon in powerpoint ! ill rename them all and then see if i can reference them easily from within vba !