locked
Macro to update links from Excel to PPT (Powerpoint 2010) RRS feed

  • Question

  • Hi All,

    I have a PowerPoint file that has a mix of charts and graphs that are linked to an excel file. I would like to know if there is a way to update these links without having to close the PPT file, re-open it and clicking on the "Update Links" button. Is there a macro for this?

    Thanks in advance for you help!

    Friday, May 6, 2016 2:12 PM

All replies

  • Try...

    Option Explicit
    
    Public Const PresName As String = "Presentation1.pptm"
    
    Sub UpdateLinks()
        Dim oSlide As Slide
        Dim oShape As Shape
        For Each oSlide In Application.Presentations(PresName).Slides
            For Each oShape In oSlide.Shapes
                If oShape.Type = msoLinkedOLEObject Then
                    oShape.LinkFormat.Update
                End If
            Next oShape
        Next oSlide
    End Sub

    Hope this helps!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    Friday, May 6, 2016 6:34 PM
  • Thank you so much for replying.

    I tried this and I get an error message, maybe because I'm not copying and pasting the code entirely? This is what I pasted...and named the macro "Updatelinks"

    Sub UpdateLinks()
       
    Dim oSlide As Slide
       
    Dim oShape As Shape
       
    For Each oSlide In Application.Presentations(PresName).Slides
           
    For Each oShape In oSlide.Shapes
               
    If oShape.Type = msoLinkedOLEObject Then
                    oShape
    .LinkFormat.Update
               
    End If
           
    Next oShape
       
    Next oSlide
    End Sub

    The Error message im getting reads something like below.

    Run-Time Error'-2147024809 (80070057)

    Presentations (unknown member) : Bad Argument type. Expected collection index (string or integer).

    Am I doing something wrong?  

    Friday, May 6, 2016 9:15 PM
  • Make sure that you include the following constant declaration at the module level...

    Public Const PresName As String = "Presentation1.pptm"

    ...and replace "Presentation1.pptm" with the actual name of the presentation in which the code resides.


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    Saturday, May 7, 2016 1:35 AM
  • Works! Thank you!
    Wednesday, May 11, 2016 3:03 PM
  • You're very welcome!

    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    Wednesday, May 11, 2016 4:49 PM