Macro for changing links (W2000, PPT 2002)

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

.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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote