Excel to PPT Using embedded PPT Template

[rafaelspeixoto]

Hi guys.

I have put together a macro to copy some excel ranges and charts and place them in a new PPT file and it works fine. Now I need this same macro to paste the charts in a new PPT file that uses a specific template that has two kinds of slides (one for the fist slide of the presentation and one for the other slides). I know how to do that opening the template file and running the macro to fill it, but I am not the one that will run this macro in the future. Is it possible to make excel use a PPT template file attached to itself (embedded file)?
The code is not that small, but if it helps I may post it here...

 

[Jon Peltier]

Keep the PowerPoint template in the same directory as the Excel file. The PowerPoint template has two slide layouts in it. To make a slide use the title layout, you need to apply the title layout to the slide:

  ActiveWindow.Selection.SlideRange.Layout = ppLayoutTitle

To use the other layout, apply a non-title layout, such as

  ActiveWindow.Selection.SlideRange.Layout = ppLayoutTitleOnly
  ActiveWindow.Selection.SlideRange.Layout = ppLayoutText
  ActiveWindow.Selection.SlideRange.Layout = ppLayoutTwoColumnText
  ActiveWindow.Selection.SlideRange.Layout = ppLayoutBlank

(Ain't the macro recorder wonderful?)

 

[rafaelspeixoto]

Hi Jon!

First of all, thank you very much for the help. You have been helping me a lot through your page (specially this one lately: http://peltiertech.com/Excel/XL_PPT.html)
I also thank you for your answer about working with a template in the same folder as the excel file, but I would really like to know if it is possible to have this template available inside the excel file in a way to reduce the number of files and make the macro more user friendly. I have already experienced quite a few cases of people failing to do some very simple proceedures, like opening a certain file before running the macro...

I am still a beginner in macros, but I'm working hard on learning and your answer is not just helping me to solve the problem, it is also helping me to learn (by the end of last year, the hardest thing I could do with excel was using a Vlookup function ) .

Thanks again!

 

[Jon Peltier]

To make it really user friendly, you have to make it more complicated on yourself. The Excel code should go into an add-in, and the add-in and PowerPoint template should be installed in a directory with any other necessary files. A good directory would be something like "C:\Program Files\Rafael XL to PPT". A good, free installer is Inno Setup (Google will find it for you).

Fewer files does not automatically mean a simpler solution. If everything is all together in one file, how do you give someone an update to the code without hosing the data? Keep the data, logic, and presentation in different files, so changing one does not overwrite the other. It's more difficult this way, but the resulting project will be more flexible and more reliable.

 

[rafaelspeixoto]

Hi Jon

It looks like the voice of experience is talking and I should listen.

Thanks a lot

 

[rafaelspeixoto]

I am facing problems on doing what you said. I have placed the template in the same folder as the excel file, but it looks like the macro is taking the template settings from the default ppt template instead of the template that is in the folder. Do I have to do something else in order to force the macro to use the template I want?

I will post a piece of the code here to show how I'm doing it:

Set PPPres = PPApp.Presentations.Add
SlideCount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutTitleOnly)

PPApp.ActiveWindow.ViewType = ppViewSlide
    
Worksheets("Presentation").Range("A2:Q26").Select
    ' Copy the range as a picture
Selection.CopyPicture Appearance:=xlScreen, _
    Format:=xlPicture

' Paste the range
PPSlide.Shapes.PasteSpecial(ppPasteEnhancedMetafile).Select
' Change the size of pasted range

PPApp.ActiveWindow.Selection.ShapeRange.ScaleHeight 0.7, msoTrue
PPApp.ActiveWindow.Selection.ShapeRange.ScaleWidth 0.7, msoTrue


PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
'Text title and title properties
PPSlide.Shapes.Placeholders(1).TextFrame.TextRange.Text = "2007 YTD results"

Am I doing something wrong? I'm going nuts with this code

 

[Jon Peltier]

Somewhere you need to add

PPPres.ApplyTemplate "C:\Temp\Template.pot"

so PowerPoint knows you want to apply another template.

 

[rafaelspeixoto]

Thank you very much! This works perfectly!!