Embedding Excel Workbook into Powerpoint

[mkmiller]

Hi,

I am trying to embed a very large excel workbook into powerpoint. I want to create a template that I can dump a data output into 3 seperate data tabs and have 100+ graphs populate throughout the powerpoint. I currently have this model built in a workbook and have all the graphs in a powerpoint file linked to the excel workbook. So as I update the data the graphs will adjust if I update the links in the powerpoint. Because I cannot change the file names of the either the powerpoint or the excel file I have to do to break the links and save the powerpoint as a different name. This works fine, until I have to make updates to the data, then I have to load the data into the excel template and update the links on the powerpoint template and then make any changes I have made to the powerpoint all over again. If there is a way to embed the workbook into the powerpoint and have the graphs on separate slides point to the data embedded this would be ideal. Does any one know of a way to do this? Any help would be appreciated!!

 

[dnortje23]

Hi MKMiller,

Did you every find a solution to your question? Am in exactly the same predicament, so any advice you might have would be MUCH appreciated.

Thanks!

 

[Anne Troy]

What ever happened to Copying the data and/or chart and going to PPT and hitting Paste Special, Paste Link?

It's been around for years and years.

Also, there is an option to update links...

 

[dnortje23]

Precisely what I've done, only problem happens when you want to save different versions of the same set. Basically I've got a template of each but I need to be able to redirect the links in ppt to pull figures from another workbook. All the charts and tables have been set up so there aren't any ordering or layout changes, just updating the figures.

Any ideas on how to create separate versions that link to different workbooks?

 

[Anne Troy]

File-->Prepare-->Edit Links

 

[dnortje23]

Won't that mean having to edit each and every link? Is there a script or macro I could use to automate this? I'm sitting with close to 300 charts and have to make 5 versions....

 

[Anne Troy]

I gotta test and can't do it from work right now.
I'll be home in a few hours.

 

[dnortje23]

Ok, great, thanks in advance!

 

[Anne Troy]

Well, okay, then.

Open your presentation.
Hit Alt+F11.
At the top of the screen hit Insert, then Module.

Paste the following code into the code window at right:

Sub UpdateLinks() 
    Dim ExcelFile 
    Dim exl As Object 
    Set exl = CreateObject("Excel.Application") 
     
     'Open a dialog box to promt for the new source file.
    ExcelFile = exl.Application.GetOpenFilename(, , "Select Excel File") 
     
    Dim i As Integer 
    Dim k As Integer 
     
     'Go through every slide
    For i = 1 To ActivePresentation.Slides.Count 
        With ActivePresentation.Slides(i) 
             'Go through every shape on every slide
            For k = 1 To .Shapes.Count 
                Turn of error checking s that it doesn 't crash if the current shape doesn't already have a link
                On Error Resume Next 
                 'Set the source to be the same as teh file chosen in the opening dialog box
                .Shapes(k).LinkFormat.SourceFullName = ExcelFile 
                If .Shapes(k).LinkFormat.SourceFullName = ExcelFile Then 
                     'If the change was successful then also set it to update automatically
                    .Shapes(k).LinkFormat.AutoUpdate = ppUpdateOptionAutomatic 'other option is ppUpdateOptionManual
                End If 
                On Error Goto 0 
            Next k 
        End With 
    Next i 
     
End Sub

Click anywhere in the code and hit the Run button from the top of the screen. It's gonna bring up the link dialog. You're gonna choose the file, and it's gonna change ALL the links.

To check it, in 2010, you hit File, and all the way down bottom-right, you will see Edit links. It is really stupid that you can't edit them all at once. Used to able to do that in Word.

Problem: I wouldn't want to save the code in the PPT. So keep a copy of the code in a Notepad txt file and do what I just said when you wanna run it.

If you need a screen-shared walkthrough, I can do that with ya.

 

[dnortje23]

Thanks a mil! Will give it a try and let you know how it goes.