Export 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'm not a VBA programmer;)

what i have done so far:

• extract data from excel huge workbook and perform calculations
• write the results to a neatly pre-formatted excel skeleton file

what i want to do next:

• write the same data into the already neatly prepared ppt slide directly from excel vba code.

I think it’s possible to define some text fields inside the slide and then just put the values ​​in them ... just nothing was found!

any help on this subject is appreciated;)

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

TIA

Answer 1

Here is the code I found on the Mahipal Padigela VBA website

(it is also referenced by fooobar.com/questions/1051438 / ... )

''# 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 additional help on Powerpoint Automation with VBA and http://www.mahipalreddy.com/vba/ppvba.htm

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

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

which should lead you in the right direction! (so droll)

Philip