Macro to update ole links not working

kisa500 · #1 02-01-2013, 02:38 PM

I found a macro that is supposed to update ole links in powerpoint but it doesn't seem to be working. Any suggestions?

Original source of macro: http://www.pptfaq.com/FAQ00759_Searc...link_paths.htm

VB Script with my mods:

Code:

Sub ChangeOLELinks()

    Dim oSld As Slide
    Dim oSh As Shape
    Dim sOldPath As String
    Dim sNewPath As String

    ' EDIT THIS TO REFLECT THE PATHS YOU WANT TO CHANGE
    ' Include just the portion of the path you want to change
    ' For example, to change links to reflect that files have moved from
    ' \\boss\p-drive\temp\*.* to
    ' \\boss\Q-drive\temp\*.*
    sOldPath = "c:\Finance Pitch Automation\UK PDL Weekly Ops Pitch\"                                '"\\boss\p-drive\"
    sNewPath = "c:\Finance Pitch Automation\Development\UK PDL Weekly Ops Pitch\"                    ' "\\boss\q-drive\"
    
    On Error GoTo ErrorHandler

    For Each oSld In ActivePresentation.Slides
        For Each oSh In oSld.Shapes
            ' Change only linked OLE objects
            If oSh.Type = msoLinkedOLEObject Then
             '   On Error Resume Next
                ' Verify that file exists
               ' If Len(Dir$(Replace(oSh.LinkFormat.SourceFullName, sOldPath, sNewPath))) > 0 Then
                     oSh.LinkFormat.SourceFullName = Replace(oSh.LinkFormat.SourceFullName, sOldPath, sNewPath)
              '  Else
               '       MsgBox ("File is missing; cannot relink to a file that isn't present")
                End If
             '   On Error GoTo ErrorHandler
            ' End If
        Next    ' shape
    Next    ' slide

  MsgBox ("Done!")

NormalExit:
    Exit Sub
    
ErrorHandler:
    MsgBox ("Error " & Err.Number & vbCrLf & Err.Description)
    Resume NormalExit

End Sub

JohnWilson · #2 02-03-2013, 09:29 AM

Assuming you do have OLElinkedObjects try:

sOldPath = "\UK PDL Weekly Ops Pitch\"
sNewPath = "\Development\UK PDL Weekly Ops Pitch\"

kisa500 · #3 02-04-2013, 09:57 AM

So it seems that the problem was with the replace statement. Modified code thus and this seems to work, hopefully this will help others as I found several other posts showing that this macro didn't work for them but without a clear/simple solution.

Code:

Sub ChangeOLELinks()

    Dim oSld As Slide
    Dim oSh As Shape
    Dim sOldPath As String
    Dim sNewPath As String
  
    ' EDIT THIS TO REFLECT THE PATHS YOU WANT TO CHANGE
    sOldPath = InputBox("Enter Old Project ie: \Development\", "Old Path") 
    sNewPath = InputBox("Enter New Project ie: \Test\", "New Path") 
     
    On Error GoTo ErrorHandler

    For Each oSld In ActivePresentation.Slides
        For Each oSh In oSld.Shapes
            If oSh.Type = msoLinkedOLEObject Then
            
                Dim stringPath   As String
                stringPath = Replace(oSh.LinkFormat.SourceFullName, sOldPath, sNewPath, 1, , vbTextCompare)
               
                oSh.LinkFormat.SourceFullName = stringPath
               ' set update mode to auto and update then set it back to manual
                oSh.LinkFormat.AutoUpdate = ppUpdateOptionAutomatic
                oSh.LinkFormat.Update
                oSh.LinkFormat.AutoUpdate = ppUpdateOptionManual
                               
            End If
        Next oSh
    Next oSld
    ActivePresentation.Save

MsgBox ("Done!")

NormalExit:
    Exit Sub
    
ErrorHandler:
    MsgBox ("Error " & Err.Number & vbCrLf & Err.Description)
    Resume NormalExit

End Sub

JohnWilson · #4 02-04-2013, 10:02 AM

I think your problem was probably the use of lower case c: but since the drive doesn't change it was unecessary.

kisa500 · #5 02-04-2013, 10:05 AM

nope, the drives were fine, it had nothing to do with the way the paths were being pushed/stored, it was something with the actual replace syntax, once i modified it to the above, every variation of old path/new path (whether full string or just portion, etc) seemed to work fine. I just ended up leaving it as an input box in the end, but I tried it out with several different options.

JohnWilson · #6 02-04-2013, 10:45 AM

What I mean is you used lower case c: I guess your drive is really upper case C:??

Probably to the code the lower case new path doesn't exist and so it wouldn't do the replace. Just a guess though

kisa500 · #7 02-04-2013, 10:47 AM

Right, i'm saying i had tried the replace with lower case full path, I also tried it with just portions of the path (similar to what u suggested in your earlier post) and it still didnt' work. Once i modified the replace statement, even with lowercase c, etc, it worked every time. it had to do with the syntax of the replace statement (either that or there is some environment variable that made the syntax change necessary).

JohnWilson · #8 02-05-2013, 03:52 AM

So to clairify: (for others)

By adding vbTextCompare you are essentially saying Ignore Case. This is why the lower case drive letter is accepted. The unstated default in Replace is vbBinaryCompare which IS case sensitive.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
PPT 2010 Update Links Error	MichaelinSJ	PowerPoint	2	10-03-2012 10:44 AM
How to set links that automatically update	tkelly5446	Project	1	11-17-2010 04:26 AM
mailto links not working in outlook	matt_sheehy	Outlook	0	07-26-2010 04:11 PM
Automatic Update of links has been disabled:	Kal	Excel	1	03-21-2010 09:37 AM