Today I would like to present a possibility to fill pre-defined text fields in a Powerpoint slides with the entries of a cell in a Excel spreadsheet. As I got the basic idea from a website written in English, I also write this post in English.
The basic idea is that the data of each row in the Excel spreadsheet is copied on a separate slide in Powerpoint. The VBA macro that can be found at the website linked above does this already for one column. I only extended the code by the possibility to copy more data of a row and to assign an Identifier to each column.
Therefore, I do not only paste the code, but also upload example files (one Excel spreadsheet and one Powerpoint file - see end of this post at the very very bottom).
The files and the code work with Excel 2010 and Powerpoint 2010. The VBA code is a macro for Powerpoint, not for Excel (hence, the ppt file contains the code). You might have to add Microsoft Excel XX Object Library, so that it works.
Sub CreateSlides()
'*** Original Sourcecode taken from http://superuser.com/questions/323408/excel-data-into-powerpoint-slides ***
'Open the Excel workbook. Change the filename here.
Dim OWB As New Excel.Workbook
Set OWB = Excel.Application.Workbooks.Open("C:\List.xlsx")
'Grab the first Worksheet in the Workbook
Dim WS As Excel.Worksheet
Dim sCurrentText As String
Dim sIdentifier As String
Dim oSl As Slide
Dim oSh As Shape
Set WS = OWB.Worksheets(1)
Dim i As Long
'Loop through each used row in Column A
For i = 3 To WS.Range("A65536").End(xlUp).Row
Debug.Print "Bin hier"
'Copy the first slide and paste at the end of the presentation
ActivePresentation.Slides(1).Copy
ActivePresentation.Slides.Paste
Set oSl = ActivePresentation.Slides(ActivePresentation.Slides.Count)
For c = 1 To 3
sCurrentText = WS.Cells(i, c).Value
sIdentifier = WS.Cells(2, c).Value
' find each shape with sIdentifier of the current column (e.g. "field1~", "field2~", and so on) in text, replace it with value from worksheet
For Each oSh In oSl.Shapes
' Make sure the shape can hold text and if is, that it IS holding text
If oSh.HasTextFrame Then
If oSh.TextFrame.HasText Then
' it's got text, do the replace
With oSh.TextFrame.TextRange
.Replace sIdentifier, sCurrentText
End With
End If
End If
Next
Next
Next
ActiveWorkbook.Close
End Sub
Of course, I do not take any responsibility from whatever damage might happen by using the code, the Powerpoint or the Excel file. Everything you do, you do at your own risk.
Thanks, this is a really useful macro.
AntwortenLöschenI tried doing something like this once before, going from Excel to Powerpoint. It worked OK but was very complicated. This is so simple and elegant.
One point for others (it isn't so obvious to me just from reading the code) the 'template' slide (first one in the powerpoint deck) just needs to contain a variety of shapes where the only text in the shape is identical to a column heading in the Excel list), e.g. in the above example, the template slide might contain a rounded rectangle with the text field1 and a transparent text box with the text 'field2'.)
Vielen Danke !
You are welcome!
LöschenThanks for this, this sounds exactly like what I need!
AntwortenLöschenUnfortunately using your exemple files, it doesn't work and I get error "Active X component can't create object" for the line Set OWB = Excel.Application.Workbooks.Open("C:\List.xlsx")" (id did change to the right file path.
Any idea what's wrong? I'm using Powerpoint for Mac 2011. Any Help appreciated!
Hi Fabien, sorry, I have no idea about Powerpoint for Mac. Maybe it has something to do with my hint "You might have to add Microsoft Excel XX Object Library, so that it works."
LöschenGood luck, Marcus
Dieser Kommentar wurde vom Autor entfernt.
AntwortenLöschenCan I spread the fields to more than one slide (the macro adjusted accordingly) i.e. my template spreads over three foils where I need to distribute the row contents into...
AntwortenLöschen