CODEWITHSUNDEEP
excelhyperlinkpowerpointvba
Dinesh Haraveer
Dinesh Haraveer

Reputation: 1804

How to update embeded excel links in powerpoint 2010 using vba

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

Upvotes: 4

Views: 20109

Answers (2)

user8773481
user8773481

Reputation: 11

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

Upvotes: 1

Dinesh Haraveer
Dinesh Haraveer

Reputation: 1804

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

Upvotes: 5

Related Questions