Excel -> PowerPoint multiple presentations

wstach · #1 03-14-2014, 09:07 AM

Hi,

One of my frequent tasks at work require generating multiple PowerPoint presentations from Excel pivot tables and pivot charts. Therefore I am very interested in automating the entire process as much as possible. Basically, these different presentations are populated from the same pivot tables/charts with different filter settings. Currently, I have a slicer connected to all pivot tables/charts which allows me update all Excel pivot tables/charts as well as PowerPoint slides accordingly (PowerPoint tables and charts are linked to the Excel file). This is working well. However, for each slicer setting, I have to manually (in PowerPoint) break links into data sources (Edit Links to Files option) to avoid changing the slides when the slicer settings change, and then manually save the presentation to a different file.

Given that the number of options in the slicer is relatively large (about 50) and the fact that I need to go over this procedure quite frequently, I have been wondering if it is do-able to automate this process any further. Ultimately, what I am interested in is a procedure that would iterate over all slicer options in Excel (updating the PowerPoint slides) and save individual presentations to different files. Given my fairly limited knowledge of VBA I am not really able to figure out if it's feasible at all and, if so, where to start.

I use in Office 2010. Unfortunately, I am not allowed to use/install any additional software to complete this task.

Thank you in advance for any suggestions!
Voytek

macropod · #2 03-15-2014, 08:55 PM

You could probably use a macro like the following in your master PowerPoint file, to both update & break the links, then save the updated file:

Code:

Sub Demo()
Dim Sld As Slide, Shp As Shape
With ActivePresentation
  .UpdateLinks
  On Error Resume Next
  For Each Sld In .Slides
    For Each Shp In Sld.Shapes
      With Shp
        .LinkFormat.BreakLink
      End With
    Next
  Next
  On Error GoTo 0
  .SaveAs FileName:=Left(.FullName, InStrRev(.FullName, ".")) & _
    Format(Now, "YYYYMMDD") & ".pptx", Fileformat:=ppSaveAsDefault
End With
End Sub

The above code saves the updated presentation file back to the same folder, with the date appended to the filename in YYYYMMDD format.

wstach · #3 03-18-2014, 06:20 AM

Thank you very much! That will make this process much more efficient!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
PowerPoint 2007 process hangs	ChrisK	PowerPoint	6	02-03-2012 02:00 PM
I dig music with Powerpoint presentations but ......	jackdashack	PowerPoint	1	11-26-2010 11:14 PM
Enhancing PowerPoint Presentations	MarkStrobel3367	PowerPoint	1	11-16-2010 08:28 AM
Aaxatech p1 for powerpoint presentations	hardcoresoft	PowerPoint	3	02-19-2010 11:08 AM
Web Site for PowerPoint Presentations	Flexibase	PowerPoint	3	02-14-2010 12:32 PM