I have an Excel file where each row lists data for an individual person. I need to transfer the data for each individual in a Powerpoint presentation where each individual has their own slide. The slide layout should be picture with caption. The name of the individual should be the title of the slide, the data in the row of that individual less the name column should be the data in the caption text box. From a separate folder, the picture associated with each individual should be pulled into the picture field in powerpoint (the folder contains individual .jpg files). I can complete this portion if someone can write the macro line to pull a specific file which matches the name in the Excel doc.
Macro Needed: Excel Spreadsheet Cells to Powerpoint Text Fields (shifting to new slide with each row of data)
0
Answers
0
I'll cover the first part here, getting the data into PowerPoint.
Here is some VBA code to get you started:
Sub Data_to_PowerPoint()
Dim newPowerPoint As PowerPoint.Application
Dim activeSlide As PowerPoint.Slide
Dim ExcelRow As Range
Dim CellRange As Range
Dim SlideText As Variant
'The first range of cells in the table.
Set CellRange = Range("A1:A2")
'Look for existing powerpoint instance
On Error Resume Next
Set newPowerPoint = GetObject(, "PowerPoint.Application")
On Error GoTo 0
'Create a PowerPoint
If newPowerPoint Is Nothing Then
Set newPowerPoint = New PowerPoint.Application
End If
'Setup the presentation in PowerPoint
If newPowerPoint.Presentations.Count = 0 Then
newPowerPoint.Presentations.Add
End If
'Make PowerPoint visible
newPowerPoint.Visible = True
'Loop through each chart in the Excel worksheet and paste them into the PowerPoint
For Each ExcelRow In CellRange
'Add a new slide
newPowerPoint.ActivePresentation.Slides.Add newPowerPoint.ActivePresentation.Slides.Count + 1, ppLayoutText
newPowerPoint.ActiveWindow.View.GotoSlide newPowerPoint.ActivePresentation.Slides.Count
Set activeSlide = newPowerPoint.ActivePresentation.Slides(newPowerPoint.ActivePresentation.Slides.Count)
'Create the body text for the slide
SlideText = Cells(ExcelRow.Row, 1) & Chr(13) & Cells(ExcelRow.Row, 2)
'Input the title of the slide
activeSlide.Shapes(1).TextFrame.TextRange.Text = ExcelRow.Value
'Input the body text for the slide
activeSlide.Shapes(2).TextFrame.TextRange.Text = SlideText
Next
AppActivate ("Microsoft PowerPoint")
Set activeSlide = Nothing
Set newPowerPoint = Nothing
End Sub
Make sure to enable the PowerPoint library in VBA:
- In the VBA window, go to Tools > References...
- Scroll down to Microsoft PowerPoint X.0 Object Library, check the box, and press Okay.
- X.0 stands for the version number, it could be, for instance, 14.0 or another number.
What to change:
You need to change some parts of the macro to suit your needs.
- Set CellRange = Range("A1:A2")
- Change Range("A1:A2") to the full length of the first column in your data set.
- SlideText = Cells(ExcelRow.Row, 1) & Chr(13) & Cells(ExcelRow.Row, 2)
- Chr(13) is the bullet point.
- Cells(ExcelRow.Row, 1) is the cell from which you want to get the data to put in for a bullet point. The 1 here means to get data from the first column in the current row; 2 means from the second column in the current row, etc.
- Follow the above pattern adding as many Char(13) and cell references for data as you need in order to get all of the desired information into the slide.
- Each piece needs to be separated using an ampersand & as in the example.
Get this working how you want for your data and then ask another question specific to the photos and we can see about that.