Excel to PPT Using embedded PPT Template

rafaelspeixoto

Board Regular
Joined
May 14, 2007
Messages
85
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...
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,962
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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:

Code:
  ActiveWindow.Selection.SlideRange.Layout = ppLayoutTitle

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

Code:
  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

Board Regular
Joined
May 14, 2007
Messages
85
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 :p) .

Thanks again!
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,962
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

Board Regular
Joined
May 14, 2007
Messages
85

ADVERTISEMENT

Hi Jon

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

Thanks a lot
 

rafaelspeixoto

Board Regular
Joined
May 14, 2007
Messages
85
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:

Code:
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

MrExcel MVP
Joined
May 14, 2003
Messages
4,962
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Somewhere you need to add

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

so PowerPoint knows you want to apply another template.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,162
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top