Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-01-2013, 02:38 PM
kisa500 kisa500 is offline Macro to update ole links not working Windows 7 64bit Macro to update ole links not working Office 2010 64bit
Novice
Macro to update ole links not working
 
Join Date: Feb 2013
Posts: 4
kisa500 is on a distinguished road
Default Macro to update ole links not working

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
Reply With Quote
  #2  
Old 02-03-2013, 09:29 AM
JohnWilson JohnWilson is offline Macro to update ole links not working Windows 7 64bit Macro to update ole links not working Office 2010 32bit
Programmer
 
Join Date: Nov 2008
Location: UK
Posts: 1,912
JohnWilson has a spectacular aura aboutJohnWilson has a spectacular aura about
Default

Assuming you do have OLElinkedObjects try:

sOldPath = "\UK PDL Weekly Ops Pitch\"
sNewPath = "\Development\UK PDL Weekly Ops Pitch\"
__________________
Microsoft PowerPoint MVP 2007-2023
Free Advanced PowerPoint Tips and Tutorials
Reply With Quote
  #3  
Old 02-04-2013, 09:57 AM
kisa500 kisa500 is offline Macro to update ole links not working Windows 7 64bit Macro to update ole links not working Office 2010 64bit
Novice
Macro to update ole links not working
 
Join Date: Feb 2013
Posts: 4
kisa500 is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 02-04-2013, 10:02 AM
JohnWilson JohnWilson is offline Macro to update ole links not working Windows 7 64bit Macro to update ole links not working Office 2010 32bit
Programmer
 
Join Date: Nov 2008
Location: UK
Posts: 1,912
JohnWilson has a spectacular aura aboutJohnWilson has a spectacular aura about
Default

I think your problem was probably the use of lower case c: but since the drive doesn't change it was unecessary.
__________________
Microsoft PowerPoint MVP 2007-2023
Free Advanced PowerPoint Tips and Tutorials
Reply With Quote
  #5  
Old 02-04-2013, 10:05 AM
kisa500 kisa500 is offline Macro to update ole links not working Windows 7 64bit Macro to update ole links not working Office 2010 64bit
Novice
Macro to update ole links not working
 
Join Date: Feb 2013
Posts: 4
kisa500 is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 02-04-2013, 10:45 AM
JohnWilson JohnWilson is offline Macro to update ole links not working Windows 7 64bit Macro to update ole links not working Office 2010 32bit
Programmer
 
Join Date: Nov 2008
Location: UK
Posts: 1,912
JohnWilson has a spectacular aura aboutJohnWilson has a spectacular aura about
Default

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
__________________
Microsoft PowerPoint MVP 2007-2023
Free Advanced PowerPoint Tips and Tutorials
Reply With Quote
  #7  
Old 02-04-2013, 10:47 AM
kisa500 kisa500 is offline Macro to update ole links not working Windows 7 64bit Macro to update ole links not working Office 2010 64bit
Novice
Macro to update ole links not working
 
Join Date: Feb 2013
Posts: 4
kisa500 is on a distinguished road
Default

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).
Reply With Quote
  #8  
Old 02-05-2013, 03:52 AM
JohnWilson JohnWilson is offline Macro to update ole links not working Windows 7 64bit Macro to update ole links not working Office 2010 32bit
Programmer
 
Join Date: Nov 2008
Location: UK
Posts: 1,912
JohnWilson has a spectacular aura aboutJohnWilson has a spectacular aura about
Default

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.
__________________
Microsoft PowerPoint MVP 2007-2023
Free Advanced PowerPoint Tips and Tutorials
Reply With Quote
Reply

Tags
ole links

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to update ole links not working PPT 2010 Update Links Error MichaelinSJ PowerPoint 2 10-03-2012 10:44 AM
Macro to update ole links not working 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
Macro to update ole links not working Automatic Update of links has been disabled: Kal Excel 1 03-21-2010 09:37 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:48 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft