Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Populating Data from Excel sheet into Powerpoint Template Slide I created
Hello,
I have used VBA macros before to copy data from Excel to Excel and from Access to Excel. But I have never used macros for copying Excel data to Powerpoint before. I was wondering if I create a Powerpoint template slide, can I copy values of fields from an Excel to populate the value of a corresponding Text Box in a powerpoint slide. And then repeat the process for each row in my Excel worksheet on a subsequent slide in powerpoint
This sounds totally reasonable to me
Thanks
David
I have used VBA macros before to copy data from Excel to Excel and from Access to Excel. But I have never used macros for copying Excel data to Powerpoint before. I was wondering if I create a Powerpoint template slide, can I copy values of fields from an Excel to populate the value of a corresponding Text Box in a powerpoint slide. And then repeat the process for each row in my Excel worksheet on a subsequent slide in powerpoint
This sounds totally reasonable to me
Thanks
David
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi David,
here is VBA to make a powerpoint presentation. It simply loops and creates 5 slides -- hopefully you can see how to change it to get what you need from your workbook. You will need to reference the PowerPoint Object library. Later, it could be changed to late binding.
here is VBA to make a powerpoint presentation. It simply loops and creates 5 slides -- hopefully you can see how to change it to get what you need from your workbook. You will need to reference the PowerPoint Object library. Later, it could be changed to late binding.
Sub MakePowerpoint()
'130504 Crystal
Dim appPpt As Object _
, objSlide As PowerPoint.Slide
Dim sFilename As String _
, nSlideNumber As Integer _
, nMaxSlides As Integer
'Look for existing instance
On Error Resume Next
Set appPpt = GetObject(, "PowerPoint.Application")
On Error GoTo Proc_Err
If appPpt Is Nothing Then
Set appPpt = New PowerPoint.Application
End If
'Make presentation
If appPpt.Presentations.Count = 0 Then
appPpt.Presentations.Add
End If
appPpt.Visible = True
sFilename = ActivePresentation.Path & "\" & "MyPresentation.ppt"
nSlideNumber = 1
nMaxSlides = 5
With appPpt
Do While nSlideNumber < nMaxSlides
If nSlideNumber > .ActivePresentation.Slides.Count Then
.ActivePresentation.Slides.Add .ActivePresentation.Slides.Count + 1, ppLayoutText
Else
objSlide.Layout = ppLayoutText
End If
.ActiveWindow.View.GotoSlide nSlideNumber
Set objSlide = .ActivePresentation.Slides(nSlideNumber)
objSlide.Shapes(1).TextFrame.TextRange.Text = "slide title"
objSlide.Shapes(2).TextFrame.TextRange.Text = "content " & nSlideNumber
nSlideNumber = nSlideNumber + 1
Loop
.ActivePresentation.SaveAs sFilename
MsgBox "Done making " & sFilename, , "Done"
.ActivePresentation.Close
.Quit
End With
Proc_Exit:
On Error Resume Next
Set appPpt = Nothing
Exit Sub
Proc_Err:
MsgBox Err.Description, , "ERROR MakePowerpoint: " & Err.Number
Resume Proc_Exit
Resume
End Sub
Hi Crystal,
Sorry for the late response but I was tied up for the holiday weekend. I will take a look and get back to you as soon as possible
Thanks
David
Sorry for the late response but I was tied up for the holiday weekend. I will take a look and get back to you as soon as possible
Thanks
David
Hi Crystal,
Ok, so I did take a look at the code for a sec. I did have a few questions:
1 - It seems to look for an existing instance of a powerpoint presentation. Does this mean that I need to open my powerpoint template first before I can run code like this?
2 - Is this piece of code going to reside in my Excel file or in my powerpoint file? I am assuming Excel file
Ultimately, I am in the process of making a Powerpoint slide template. With lets say shape A, shape B, shape C, shape D. With whatever fonts and fill. I would like the code to open up this template, and immediately make a copy of this slide so that I can start populating the slide with the corresponding field in Excel
Please let me know what you think
Thanks
David
Ok, so I did take a look at the code for a sec. I did have a few questions:
1 - It seems to look for an existing instance of a powerpoint presentation. Does this mean that I need to open my powerpoint template first before I can run code like this?
2 - Is this piece of code going to reside in my Excel file or in my powerpoint file? I am assuming Excel file
Ultimately, I am in the process of making a Powerpoint slide template. With lets say shape A, shape B, shape C, shape D. With whatever fonts and fill. I would like the code to open up this template, and immediately make a copy of this slide so that I can start populating the slide with the corresponding field in Excel
Please let me know what you think
Thanks
David
What if I wanted to open a specific PowerPoint file in a directory like My Documents without having PowerPoint already open? Like I said, I'm preparing a template slide to be used for this.
What line of code would I use?
Thanks
David
What line of code would I use?
Thanks
David
modify appPpt.Presentations.Add to specify the name of the template
if you created/modified one of the layouts for what you want, then change ppLayoutText to whichever one it is
appPpt.Presentations.Add Template:= "c:\path\filename.thmx"
if you created/modified one of the layouts for what you want, then change ppLayoutText to whichever one it is
Hi Crystal,
So I decided to get this going with having my ppt template open first so it doesn't need to open a new instance.
So it looks like the code goes to error on this line of code:
sFilename = ActivePresentation.Path
Am I missing a object library reference?
-David
So I decided to get this going with having my ppt template open first so it doesn't need to open a new instance.
So it looks like the code goes to error on this line of code:
sFilename = ActivePresentation.Path
Am I missing a object library reference?
-David
I forgot to mention that I also get an error dialog box saying "ActiveX component cannot create object" when I try to execute the line of code:
ActivePresentation.Slides.
ActivePresentation.Slides.
not missing anything -- make a variable, ie, sPath, and assign that to the path to save the file -- and pass the value or use it instead
Well, I feel like whether the code uses ActivePresentation.Path or ActivePresentation.Slides.
Because I even did this using a variable like you said:
Dim openSlidesCnt as integer
openSlidesCnt = ActivePresentation.Slides.
I still get "ERROR MakePowerpoint 429 ActiveX Component can't create object"
-David
Because I even did this using a variable like you said:
Dim openSlidesCnt as integer
openSlidesCnt = ActivePresentation.Slides.
I still get "ERROR MakePowerpoint 429 ActiveX Component can't create object"
-David
Ok, so I added "appPpt" in front of ActivePresentation and it seems to work now.
Dim openSlidesCnt as integer
openSlidesCnt = appPpt.ActivePresentation.
And the variable is returning the correct count now. I guess I needed to add something before ActivePresenation in the code?
Thank you for being patient with me. I have never used the Powerpoint library before.
-David
Dim openSlidesCnt as integer
openSlidesCnt = appPpt.ActivePresentation.
And the variable is returning the correct count now. I guess I needed to add something before ActivePresenation in the code?
Thank you for being patient with me. I have never used the Powerpoint library before.
-David
I am now proceeding through the sample code you gave me. Can you explain what you are doing with this piece of code? And what is ppLayoutText in your code?
If nSlideNumber > .ActivePresentation.Slides
.ActivePresentation.Slides
Else
objSlide.Layout = ppLayoutText
End If
Thanks,
David
If nSlideNumber > .ActivePresentation.Slides
.ActivePresentation.Slides
Else
objSlide.Layout = ppLayoutText
End If
Thanks,
David
Actually I think I got it. I think it is this:
appPpt.ActivePresentation.
appPpt.ActivePresentation.
appPpt.ActivePresentation.
appPpt.ActivePresentation.
even easier ...
https://msdn.microsoft.com/en-us/library/office/ff745804.aspx
to change the code to run from Access, preface objects with
appPpt. 'or whatever is your PowerPoint application object variable name
-- you can change ActivePresentation too
Duplicate returns a slide object which you can assign to an object variable -- or maybe just use With ActivePresentation.Slides(
ActivePresentation.Slides(#).Duplicate
https://msdn.microsoft.com/en-us/library/office/ff745804.aspx
to change the code to run from Access, preface objects with
appPpt. 'or whatever is your PowerPoint application object variable name
-- you can change ActivePresentation too
Duplicate returns a slide object which you can assign to an object variable -- or maybe just use With ActivePresentation.Slides(
Great, I think I am almost done. I just have one more clarification I need. Here is what I have for my Do While Loop:
Do While nSlideNumber < nMaxSlides
appPpt.ActivePresentation.
nSlideNumber = nSlideNumber + 1
appPpt.ActiveWindow.View.G
Set objSlide = appPpt.ActivePresentation.
objSlide.Shapes(1).TextFra
objSlide.Shapes(2).TextFra
Loop
I can see which shape changes to "slide title" when it gets to that line of code. My question is, how do I actually know which shape number or name I want to change. For example, in MS Access forms, each text box has a specific name. So I can easily set the value of the text box to a value from a table/query.
Here, I see where objSlide.Shapes(1) only after the line of code runs. And the objSlide.Shapes(2) line of code errors out (maybe because that shape doesn't exist.)
Please let me know if you have any questions
-David
Do While nSlideNumber < nMaxSlides
appPpt.ActivePresentation.
nSlideNumber = nSlideNumber + 1
appPpt.ActiveWindow.View.G
Set objSlide = appPpt.ActivePresentation.
objSlide.Shapes(1).TextFra
objSlide.Shapes(2).TextFra
Loop
I can see which shape changes to "slide title" when it gets to that line of code. My question is, how do I actually know which shape number or name I want to change. For example, in MS Access forms, each text box has a specific name. So I can easily set the value of the text box to a value from a table/query.
Here, I see where objSlide.Shapes(1) only after the line of code runs. And the objSlide.Shapes(2) line of code errors out (maybe because that shape doesn't exist.)
Please let me know if you have any questions
-David
Hi Crystal,
I was just wondering if you saw my question above. Basically I can see which object objSlide.Shapes(1) is on my slide. But I need to change the text on about a dozen shapes on the body of my template slide. How can I identify the shape on the slide so that I can code it in VBA?
Thanks
David
I was just wondering if you saw my question above. Basically I can see which object objSlide.Shapes(1) is on my slide. But I need to change the text on about a dozen shapes on the body of my template slide. How can I identify the shape on the slide so that I can code it in VBA?
Thanks
David
Hi Crystal,
Ok so, I decided to just start the slide from scratch and I think it works now when I change .shapes(1) or .shapes(2)
I guess this will only work when I create the slide myself and then I will know which shape is which. So if someone else gives me a slide, I would not know which one is shape(1) or shape(16) then? Because like I said, in an MS Access form, I can just right click the object -> Properties and each shape has a name or identifier. So I can easily assign a value to it using VBA. Here in MS Powerpoint, I feel like I am guessing...but I'll use trial and error I guess.
Thanks for all your help and patience!
-David
Ok so, I decided to just start the slide from scratch and I think it works now when I change .shapes(1) or .shapes(2)
I guess this will only work when I create the slide myself and then I will know which shape is which. So if someone else gives me a slide, I would not know which one is shape(1) or shape(16) then? Because like I said, in an MS Access form, I can just right click the object -> Properties and each shape has a name or identifier. So I can easily assign a value to it using VBA. Here in MS Powerpoint, I feel like I am guessing...but I'll use trial and error I guess.
Thanks for all your help and patience!
-David
you're welcome, David ~ happy to help
>> each shape has a name or identifier <<
you can, and should, change the object names to be logical (rather than the random names assigned). You can reference objects by their name or number.
if someone else gives you a slide, you can also identify the objects by their position (ie: Left, Top)
>> each shape has a name or identifier <<
you can, and should, change the object names to be logical (rather than the random names assigned). You can reference objects by their name or number.
if someone else gives you a slide, you can also identify the objects by their position (ie: Left, Top)
Premium Content
You need an Expert Office subscription to comment.Start Free Trial