Excel to Powerpoint (Create presentations with vba)

[FareedKhan]

Hi All,

I prepare presentations that consist of lot of slides on business reviews on daily basis. I do reports/graphs in excel and copy these items to power point on daily basis which consumes lot of time. I want to know is there any way to automate this task using vba.

Thanks in Advance..

 

[Rastaman]

I believe Jon Peltier's site is the best reference for this (as well as for much more chart knowledge).

http://peltiertech.com/Excel/XL_PPT.html

Check out the code under the heading "Paste the Active Excel Chart into the Active PowerPoint Slide (Late Binding)" found about halfway down the page, I've been successful with this macro for a simple copy of the current chart to current ppt slide.

You have to enable the Powerpoint Object Library first. To do this, in the VBA editor top menu select Tools, References, then scroll down to click the library called Microsoft Powerpoint xx.x Object Library. Office 2007 is library 12, each Office version has a different library. FYI, I've experienced some odd errors and file corruption when I activate the 2007 library but someone tries to open and run this macro using Excel 2003. The old version of Excel doesn't recognize the newer library, which seems to cause problems.


Rick

 

[Datsmart]

Lots of suggestions to automate here:
http://peltiertech.com/Excel/XL_PPT.html

OK, this site has already been suggested...

 

[jjbungles]

Hello!

Is there anyway to change the following code such that if I set the print area and page breaks in a worksheet it will make one powerpoint slide per page?

The following code is from the peltiertech.com website. I believe that I need to change areas I indicated in Red. The question is: Is there VBA commands that can select ranges as a formatted excel page, copy each page and insert it into a ppt slide, one slide per page?

If you know of another thread i can research that would be helpful.

Thank you....!

JJ

[LEFT][COLOR=#000000]Sub ChartsToPresentation()' [/COLOR]
[COLOR=#000000]Set a VBE reference to Microsoft PowerPoint Object Library[/COLOR]
[COLOR=#000000]Dim PPApp As PowerPoint.Application[/COLOR]
[COLOR=#000000]Dim PPPres As PowerPoint.Presentation[/COLOR]
[COLOR=#000000]Dim PPSlide As PowerPoint.Slide[/COLOR]
[COLOR=#000000]Dim PresentationFileName As Variant[/COLOR]
[COLOR=#000000]Dim SlideCount As Long[/COLOR]
[COLOR=red][B]Dim iCht As Integer[/B][/COLOR][/LEFT]
 
[LEFT][COLOR=#000000]' Reference existing instance of PowerPoint[/COLOR]
[COLOR=#000000]Set PPApp = GetObject(, "Powerpoint.Application")[/COLOR][/LEFT]
 
[LEFT][COLOR=#000000]' Reference active presentation[/COLOR]
[COLOR=#000000]Set PPPres = PPApp.ActivePresentation[/COLOR]
[COLOR=#000000]PPApp.ActiveWindow.ViewType = ppViewSlide[/COLOR][/LEFT]
 
[LEFT][COLOR=#000000]For iCht = 1 To ActiveSheet.[COLOR=red][B]ChartObjects[/B][/COLOR].Count    [/COLOR]
[COLOR=#000000]  ' copy chart as a picture    [/COLOR]
[COLOR=#000000]  ActiveSheet.[COLOR=red][B]ChartObjects[/B][/COLOR](iCht).[COLOR=red][B]Chart[/B][/COLOR].CopyPicture _ [/COLOR]
[COLOR=#000000]  Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture[/COLOR][/LEFT]
 
[LEFT][COLOR=#000000] ' Add a new slide and paste in the chart[/COLOR]
[COLOR=#000000] SlideCount = PPPres.Slides.Count[/COLOR]
[COLOR=#000000] Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutBlank)    [/COLOR]
[COLOR=#000000] PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex[/COLOR]
[COLOR=#000000] With PPSlide[/COLOR]
[COLOR=#000000]     ' paste and select the chart picture[/COLOR]
[COLOR=#000000]     .Shapes.Paste.Select[/COLOR]
[COLOR=#000000]     ' align the chart       [/COLOR]
[COLOR=#000000]      PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True[/COLOR]
[COLOR=#000000]     PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True    [/COLOR]
[COLOR=#000000]    End With[/COLOR][/LEFT]
 
[LEFT][COLOR=#000000]Next[/COLOR][/LEFT]
 
[LEFT][COLOR=#000000]' Clean up[/COLOR]
[COLOR=#000000]Set PPSlide = Nothing[/COLOR]
[COLOR=#000000]Set PPPres = Nothing[/COLOR]
[COLOR=#000000]Set PPApp = Nothing[/COLOR][/LEFT]
 
[LEFT][COLOR=#000000]End Sub[/COLOR][/LEFT]
 
 
 
 
 

[COLOR=#000000]
</PRE>[/COLOR]

 

[jjbungles]

I am slowly getting there and need some help on how to get the first and last columns of the known print area into a format I can use for the select command.

I am currently using: myRange = Sht.PageSetup.PrintArea which outputs the print area in a string. If I could get some help to find out how to convert this or use another method I will have cracked it.

What I am afraid of is this question has been asked a million times and I have spent at least 12 hours searching this forum for the answer but my search methododology has not been fruitful or everyone is asking the same question which means it might be good to have a common questions like this in an area on this forum so people like me do not start new threads.

The resource material on this forum is outstanding. Its finding the info.

Regards,

JJ

 

[Chris Macro]

Being able to automate creating PowerPoint presentations from Excel is an unbelievably powerful skill and has saved me countless hours over my career! I strongly encourage anyone who does a good amount of copy/pasting from Excel to PowerPoint to look into automating with VBA. To get you started, here are two articles I have written that give you VBA code to automate this process yourself:

Article 1: Copy & Paste An Excel Range Into PowerPoint With VBA
Article 2: Copy & Paste Multiple Excel Ranges To Separate PowerPoint Slides With VBA

If you don't want to bother with coding the automation yourself, I have created an add-in called E2P that will let you create PowerPoints with VBA without writing a single piece of code.

Hopefully, these resources will get you off to a good start in automating Excel to PowerPoint