1

I have a PowerPoint presentation in which I create charts in Excel and then link them into the PowerPoint. There are two ways to do this:

  1. Paste Special > Paste Link > Microsoft Excel Chart Object
  2. Paste > Keep Source Formatting and Link Data / Use Destination Theme and Link Data

I would late like to use VBA to change the source Excel file. To do this, consider the following code:

Private Sub PrintLinks()
    Dim pptPresentation As Presentation
    Dim pptSlide As Slide
    Dim pptShape As Shape
    
    Set pptPresentation = ActivePresentation
    For Each pptSlide In pptPresentation.Slides
        For Each pptShape In pptSlide.Shapes
            If pptShape.Type = msoChart Or pptShape.Type = msoLinkedOLEObject Or pptShape.Type = msoLinkedChart Then
                Debug.Print pptShape.LinkFormat.SourceFullName
                pptShape.LinkFormat.SourceFullName = "PATH/TO/NEW/FILE"
                pptShape.LinkFormat.Update
            End If
        Next
    Next
End Sub

This will work for the Paste Link case, but not the Link Data case, in which case pptShape.Type = msoChart. My question is if there is a way to make it work with Link Data as well. Wtih Paste Link, the SourceFullName property will point to a specific chart object, like filename1.xlsx!Chart 1, and changing it to filename2.xlsx!Chart 1 will work as expected. In contrast, under the Link Data option the SourceFullName property only points to filename1.xlsx and I cannot figure out how to see what chart object within the file it is pointing to. Regardless, if I change SourceFullName to filename2.xlsx no error will be thrown, but as far as I can tell the pointer is still to filename1.xlsx, as the chart doesn't change.

Abiel
  • 5,251
  • 9
  • 54
  • 74
  • Is this an effort to update a linked chart while a slideshow is running? PowerPoint doesn't like to do that. – John Korchok Feb 10 '23 at 00:12
  • No, imagine a situation where you have a monthly packet of charts and each month you need to archive the old Excel and PPT file and create new copies, then link the new PPT to the new Excel. – Abiel Feb 10 '23 at 13:22
  • got the same problem exactly - have you found a solution elsewhere? – eli-k May 07 '23 at 10:25

0 Answers0