Avatar of lebeau26
lebeau26
Flag for United States of America asked on

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
Microsoft PowerPointMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
crystal (strive4peace) - Microsoft MVP, Access

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
crystal (strive4peace) - Microsoft MVP, Access

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
crystal (strive4peace) - Microsoft MVP, Access

if you need help implementing the code, please explain more about your data, thank you.
lebeau26

ASKER
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
lebeau26

ASKER
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
crystal (strive4peace) - Microsoft MVP, Access

1. only if PowerPoint is not already open
2. code will be in Excel -- where the data is
lebeau26

ASKER
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
SOLUTION
crystal (strive4peace) - Microsoft MVP, Access

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
lebeau26

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
lebeau26

ASKER
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.Count
crystal (strive4peace) - Microsoft MVP, Access

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
lebeau26

ASKER
Well, I feel like whether the code uses ActivePresentation.Path or ActivePresentation.Slides.Count It seems like the error lies in "ActivePresentation."

Because I even did this using a variable like you said:

Dim openSlidesCnt as integer
openSlidesCnt = ActivePresentation.Slides.Count

I still get "ERROR MakePowerpoint 429 ActiveX Component can't create object"

-David
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
lebeau26

ASKER
Ok, so I added "appPpt" in front of ActivePresentation and it seems to work now.  

Dim openSlidesCnt as integer
openSlidesCnt = appPpt.ActivePresentation.Slides.Count

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
lebeau26

ASKER
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.Count Then
            .ActivePresentation.Slides.Add .ActivePresentation.Slides.Count + 1, ppLayoutText
         Else
            objSlide.Layout = ppLayoutText
         End If

Thanks,
David
crystal (strive4peace) - Microsoft MVP, Access

adding a new slide, or changing the current slide layout if a slide is already there
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
lebeau26

ASKER
How about if I just want to copy and paste another copy of a slide that is already there?
lebeau26

ASKER
Actually I think I got it.  I think it is this:

appPpt.ActivePresentation.Slides(nSlideNumber).Copy
appPpt.ActivePresentation.Slides.Paste
SOLUTION
crystal (strive4peace) - Microsoft MVP, Access

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
lebeau26

ASKER
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.Slides(nSlideNumber).Duplicate
            nSlideNumber = nSlideNumber + 1        
            appPpt.ActiveWindow.View.GotoSlide nSlideNumber
            Set objSlide = appPpt.ActivePresentation.Slides(nSlideNumber)
            objSlide.Shapes(1).TextFrame.TextRange.Text = "slide title"
            objSlide.Shapes(2).TextFrame.TextRange.Text = "test"
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
lebeau26

ASKER
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
crystal (strive4peace) - Microsoft MVP, Access

please post a sample of your template so I can give you the code it needs
lebeau26

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
lebeau26

ASKER
Crystal was a great help with helping me figure out my problem.
crystal (strive4peace) - Microsoft MVP, Access

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)