Updating links to files with vba in Publisher

Question

How can I automate the process of updating links to OLEObjects in Publisher with a macro? I have found some discussion of how to do this (mostly in PowerPoint), and I am experiencing a couple of issues when trying to apply this to a 2010 Publisher document.

First, when I display the .LinkFormat.SourceFullName in a message box, none of the references to the Excel worksheet or object appear as they do in the dialogue box when I edit links to files through the menu options.

Second, when I attempt to simply change the file name that appears as the .SourceFullName, I receive a compile error message that says I can't assign to a read-only property.

Any help with this would be greatly appreciated.

When I apply the following code, I get the compile error when trying to assign the new link name. If I comment out the .SourceFullName = newlinkname line, I get message boxes that tell me I have Edit and Open ObjectVerbs available, and the linkname that is displayed only includes "C:\Desktop\Projects\old.xlsx." It does not show the worksheet and chart references that I see linked to the object when I use the Edit Links to Files command through the ribbon. There, the dialogue box actually shows:

old.xlsx!H_A_CurrStat_byYrDxBar![old.xlsx]H_A_CurrStat_byYrDxBar Chart 1

I want to use a macro to change the source name from old.xlsx to new.xlsx and then update the linked objects.

Option Explicit

Sub links()

Dim linkname As String
Dim newlinkname As String
Dim shpShape As Shape
Dim intCount As Integer

newlinkname = "C:\Desktop\Projects\new.xlsx"

    For Each shpShape In ActiveDocument.Pages(1).Shapes
       shpShape.OLEFormat.Activate

               With shpShape.OLEFormat
                    For intCount = 1 To .ObjectVerbs.count
                     MsgBox .ObjectVerbs(intCount)
                    Next
                End With

            With shpShape.LinkFormat
                linkname = .SourceFullName
                '.SourceFullName = newlinkname
                MsgBox linkname
            End With

       shpShape.LinkFormat.Update
    Next

End Sub

score 0 · Answer 1 · answered May 15 '16 at 18:50

https://msdn.microsoft.com/en-us/library/office/ff940537.aspx?f=255&MSPPError=-2147217396

This code for activating all objects might help:

    Sub ActivateOLEObjects() 
            Dim shpShape As Shape 

            For Each shpShape In ActiveDocument.Pages(1).Shapes 
                    If shpShape.Type = pbLinkedOLEObject Then 
                            shpShape.OLEFormat.Activate 
                    End If 
            Next 
    End Sub

Or perhaps even better, this example code for updating links: https://msdn.microsoft.com/en-us/library/office/ff939544.aspx

    Sub FindOLEObjects() 
            Dim shpShape As Shape 

            For Each shpShape In ActiveDocument.Pages(1).Shapes 
                    If shpShape.Type = pbLinkedOLEObject Then 
                            shpShape.LinkFormat.Update 
                    End If 
            Next shpShape 
    End Sub

If neither helps, please provide some more info, like an example file or your current code

Updating links to files with vba in Publisher

1 Answers1