Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Excel Macro to change powerpoint links

  1. #1
    New Member
    Join Date
    Mar 2013
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel Macro to change powerpoint links

    Hi,

    I have a Powerpoint File, which has many Excel linked linked Charts. Now the folder name and Excel File names are changed.

    I need a macro to
    a) list all the links of the powerpoint file.
    b) I want to update the new link.

    Help me by a macro to do so.
    Thanks in advance.

    Regards,

    B.Srinivasa Rao

  2. #2
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,595
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Excel Macro to change powerpoint links

    Hi

    This is the first part, a PowerPoint code that lists the links. According to your title, do you prefer to run this from Excel?


    Code:
    ' PowerPoint module
    Sub PowerPointLinks()
    Dim pptPres As Presentation, pptSlide As Slide, pptShape As Shape, s$
    Set pptPres = ActivePresentation
    s = ""
    For Each pptSlide In pptPres.Slides
        For Each pptShape In pptSlide.Shapes
            If pptShape.Type = msoLinkedPicture Or pptShape.Type = msoLinkedOLEObject Then _
            s = s & pptShape.LinkFormat.SourceFullName & vbLf
        Next
    Next
    MsgBox s
    pptPres.UpdateLinks
    End Sub
    Last edited by Worf; Apr 18th, 2019 at 02:09 PM.
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  3. #3
    New Member
    Join Date
    Mar 2013
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Macro to change powerpoint links

    Quote Originally Posted by Worf View Post
    Hi

    This is the first part, a PowerPoint code that lists the links. According to your title, do you prefer to run this from Excel?


    Code:
    ' PowerPoint module
    Sub PowerPointLinks()
    Dim pptPres As Presentation, pptSlide As Slide, pptShape As Shape, s$
    Set pptPres = ActivePresentation
    s = ""
    For Each pptSlide In pptPres.Slides
        For Each pptShape In pptSlide.Shapes
            If pptShape.Type = msoLinkedPicture Or pptShape.Type = msoLinkedOLEObject Then _
            s = s & pptShape.LinkFormat.SourceFullName & vbLf
        Next
    Next
    MsgBox s
    pptPres.UpdateLinks
    End Sub
    Hi,

    Thanks for your response.

    Yes. I want to update from Excel Macro.

    a) I will provide Path of PPT --> Excel Macro to list the Links in PPT
    b) I will enter the New File Path in Next Column --> Macro to update the Links.

    Hope I am clear.

  4. #4
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,595
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Excel Macro to change powerpoint links

    This version is an Excel macro that lists the links starting at cell L20, and updates them with the information entered at column M, starting at M20.
    The PowerPoint file path is retrieved from cell K18.

    Code:
    ' Excel module
    Dim obppt As Object, pres As Presentation, sl As Slide, sh As PowerPoint.shape, r As Range
    
    Sub ListLinks()
    Set obppt = CreateObject("PowerPoint.Application")
    obppt.Visible = True
    obppt.Presentations.Open CStr([k18])
    Set pres = obppt.ActivePresentation
    Set r = [L20]
    For Each sl In pres.Slides
        For Each sh In sl.Shapes
            If sh.Type = msoLinkedPicture Or sh.Type = msoLinkedOLEObject Then
                r = sh.LinkFormat.SourceFullName
                Set r = r.Offset(1)
            End If
        Next
    Next
    UpdateLinks
    End Sub
    
    Sub UpdateLinks()
    Dim result
    For Each sl In pres.Slides
        For Each sh In sl.Shapes
            If sh.Type = 11 Or sh.Type = 10 Then
                result = WorksheetFunction.VLookup(sh.LinkFormat.SourceFullName, _
                [L20].CurrentRegion, 2, False)
                If Len(result) > 0 Then sh.LinkFormat.SourceFullName = result
            End If
    Next sh, sl
    pres.UpdateLinks
    End Sub
    Last edited by Worf; Apr 19th, 2019 at 08:33 PM.
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •