Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Macro for changing links (W2000, PPT 2002)

    I found this code in an article and was trying to change it so I could update my ppt links when i put my file on a different drive.

    Sub ChangeLinkSources()
    Dim i As Integer
    Dim k As Integer
    Dim linkname As String

    OldPath = InputBox("What is Old path? ")
    NewPath = InputBox("What is New path? ")

    For i = 1 To ActivePresentation.Slides.Count
    With ActivePresentation.Slides(i)
    For k = 1 To .Shapes.Count
    With .Shapes(k)
    If .Type = msoLinkedOLEObject Then
    With .LinkFormat
    linkname = .SourceFullName
    FinalString = Replace(linkname, OldPath, NewPath)
    .SourceFullName = FinalString
    .AutoUpdate = ppUpdateOptionAutomatic
    End With
    End If
    End With
    Next k
    End With
    Next i

    ActivePresentation.UpdateLinks
    End Sub

    I keep getting a run-time error -2147467259. Method 'SourceFullName" of object 'LinkFormat" failed. What does this mean? I am trying to search for C: and make it M: or some other drive letter. Thank you for any help

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Macro for changing links (W2000, PPT 2002)

    That's sort of a nonsense error, since SourceFullName is a property, not a method. The code seems sensible enough. Which line is highlighted in the de###### when you get the error?

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro for changing links (W2000, PPT 2002)

    .SourceFullName = FinalString
    is highlighted when error occurs. When i hoover over the words i can see the replacement has taken place. for some reason it doesn't like the .linkname.sourceFullName part

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Macro for changing links (W2000, PPT 2002)

    After floundering a bit in earlier versions of this post, here's my new theory: PPT tried to find the file in real time so it could update the link, and the file doesn't exist.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro for changing links (W2000, PPT 2002)

    can you think of any workaround i can use? i'm trying to update the links to excel from a ppt file. I do the file on my C: drive and then have to move it to a network drive later on. There are 50 links so i don't want to change them manually. They are supposed to update "automatically" but they don't so a macro would do the trick. This macro works if i change the filename to something else but it won't change the C: to M:. thanks for your help

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Macro for changing links (W2000, PPT 2002)

    What if you change AutoUpdate to False before changing the source path. Assuming that the problem is PPT looking for the source file, this might suppress it. You then could have a second loop resetting the property to True, hoping that because updates are triggered by (1) opening the container file and/or (2) changing the source, that changing this property won't prompt any errors.

    Worth a shot!

  7. #7
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro for changing links (W2000, PPT 2002)

    I'm having another problem with code like this. You are right about the need to have the file present, thank you for solving my first problem. Now that the code is working, it seems to be changing the links to just the file name. I can see when I step through the code that the variable I have (stNewPath) with the new name contains the path, workbook, sheet, and cell but after the update when I go back into PowerPoint and click Edit Links it shows the links with just the path and file names. Can you help? Here's my code, even though it's very similar to the code above in this thread:
    Sub M1()
    Dim sld As Slide
    Dim sh As Shape
    Dim strNms As String
    Dim intI As Integer
    Dim strNewPath

    For Each sld In ActivePresentation.Slides
    For Each sh In sld.Shapes
    If sh.Type = msoLinkedOLEObject Then
    With sh.LinkFormat
    strNms = .SourceFullName
    intI = InStr(1, strNms, "!")
    strNewPath = "C:MyPathNewFile.xls" & Mid(strNms, intI, Len(strNms) - intI + 1)
    .SourceFullName = strNewPath
    End With
    End If
    Next sh
    Next sld


    UPDATE: Part of my problem was that the source file I was trying to change to did not have the same sheets in it. Dah! This is not simply a string replacement but rather an active process of opening the new source file, locating the referenced object, and then creating the link anew. This is now working for most of my links but
    STILL ISN'T WORKING FOR CHART OBJECTS.
    The original link has the sheet name and chart object with the internal object names "Chart 1", "Chart 2", etc. I checked to be sure the string is built correctly; it is, but the assignment to the SourceFullName still gets truncated to just the path and file name.

    ANOTHER UPDATE: Got it! The chart links have the file name repeated, for some reason, in the sourcefullname string. Not only is it in the path part but it appears again inside square brackets. When I fixed the string accordingly, it resolved the problem. The only reason I'm updating this post is so that the next poor person who tries this will find the full answers here! I encourage anyone with additional information to please add to this thread. There may be other objects that require other fixes to their strings.

Posting Permissions

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