How to update embeded excel links in powerpoint 2010 using vba

4
votes

My problem is i have pasted my charts into and I am trying to update the embedded links via Excel-VBA.

I have tried the codes below and failed:

code 1

    AppPPT.Presentations.Open "D:\Demo.pptx", Untitled:=msoTrue
    AppPPT.ActivePresentation.UpdateLinks
    AppPPT.ActivePresentation.SaveAs "D:\Demo.pptx"

code 2

    For i = 1 To AppPPT.ActivePresentation.Slides.Count
    For s = 1 To AppPPT.ActivePresentation.Slides(i).Shapes.Count
        If AppPPT.ActivePresentation.Slides(i).Shapes(s).Type = msoLinkedOLEObject Then
            AppPPT.ActivePresentation.Slides(i).Shapes(s).LinkFormat.Update
        End If
    Next s
Next i

code 3

    Set PPTTemplate = AppPPT.Presentations.Open("D:\Demo.pptx")

    ' update chart
    Dim osld As Slide
    Dim oshp As PowerPoint.Shape

    For Each osld In PPTTemplate.Slides
    For Each oshp In osld.Shapes
    With oshp
    If .HasChart Then
    .Chart.ChartData.Activate
    .Chart.ChartData.Workbook.Close
    .Chart.Refresh
    End If
    End With
    Next oshp
    Next osld

    AppPPT.Activate
2
excelhyperlinkpowerpointvba
It would be good to see a sample PPT file, links can be directly in the charts, or via Excel underneath the charts. – brettdj
+ 1 for showing what have you tried :) – Siddharth Rout
Did you see this post by @brettdj stackoverflow.com/questions/9207267/… – Siddharth Rout
Thanks for the Reply,I have got another problem which i'm trying to copy the original ppt to another ppt which i need to remove all update links in the duplicate ppt is this possible?If so please provide me any sample code. – Dinesh Haraveer

2 Answers

5
votes

I have achieved it by spending some days trying on it

AppPPT.ActivePresentation.Slides(1).Shapes("Chart 75").LinkFormat.Update

and BreakLines code

AppPPT.ActivePresentation.Slides(1).Shapes("Chart 75").LinkFormat.BreakLink
1
votes

Thank you! It worked once i was able to identify the object/chart#. (Click on an object, under Drawing Tools, Arrange, Selection Pane.) Here is my code. Now i can just run it and it instantly updates all my links. I didn't want to set it to auto-update because then when i send it out, the recipients get a warning message about links which is confusing. Thanks again. 

Sub update()

ActivePresentation.Slides(1).Shapes("Object 1").LinkFormat.update
ActivePresentation.Slides(1).Shapes("Object 2").LinkFormat.update

ActivePresentation.Slides(4).Shapes("Chart 5").LinkFormat.update

ActivePresentation.Slides(5).Shapes("Object 2").LinkFormat.update
ActivePresentation.Slides(5).Shapes("Chart 5").LinkFormat.update

ActivePresentation.Slides(6).Shapes("Object 1").LinkFormat.update
ActivePresentation.Slides(6).Shapes("Chart 4").LinkFormat.update
ActivePresentation.Slides(6).Shapes("Chart 6").LinkFormat.update

ActivePresentation.Slides(7).Shapes("Object 1").LinkFormat.update
ActivePresentation.Slides(7).Shapes("Chart 4").LinkFormat.update
ActivePresentation.Slides(7).Shapes("Chart 5").LinkFormat.update

ActivePresentation.Slides(8).Shapes("Object 3").LinkFormat.update

ActivePresentation.Slides(9).Shapes("Chart 4").LinkFormat.update

ActivePresentation.Slides(10).Shapes("Object 1").LinkFormat.update

ActivePresentation.Slides(11).Shapes("Object 6").LinkFormat.update
ActivePresentation.Slides(11).Shapes("Object 7").LinkFormat.update
ActivePresentation.Slides(11).Shapes("Object 8").LinkFormat.update

End Sub