Breaking Excel Links

Topic

New Reply

Many thanks to Macropod and the forum herein for advise already given. I feel guilty even asking for help on another issue, but here goes. Can anyone help me with a macro in Word 2003 that will unlink all Excel fields (links) thereby changing them to regular text, but keeping intact the TOC fields, etc. which are internal to Word? I’m trying to use an ‘If F.Type = wdFieldLink Then’ statement but keep getting errors.

Thanks very much.

Reply | Quote

Replies

Hi Brent,

If you post the full code and tell us which line generates the error message, plus the message’s contents, that would eliminate what would only be guessing at this stage.

FWIW, if you want to break all external links you should be able to use code like:

Code:

Sub UnlinkLinks()
Dim oRng As Range, oFld As Field
With ActiveDocument
  For Each oRng In .StoryRanges
    For Each oFld In oRng.Fields
      With oFld
        If Not .LinkFormat Is Nothing Then .Unlink
      End With
    Next
  Next
End With
End Sub

I say should be able, because in my experience testing the .LinkFormat isn’t 100% reliable. If it doesn’t work for you, or you only want to unlink certain types of fields with external links, you could use code like:

Code:

Sub UnlinkLinks()
Dim oRng As Range, oFld As Field
With ActiveDocument
  For Each oRng In .StoryRanges
    For Each oFld In oRng.Fields
      With oFld
        If .Type = wdFieldHyperlink Then .Unlink
        If .Type = wdFieldImport Then .Unlink
        If .Type = wdFieldInclude Then .Unlink
        If .Type = wdFieldIncludePicture Then .Unlink
        If .Type = wdFieldIncludeText Then .Unlink
        If .Type = wdFieldLink Then .Unlink
        If .Type = wdFieldRefDoc Then .Unlink
      End With
    Next
  Next
End With
End Sub

and comment out or delete whichever ‘If’ tests you don’t want to apply.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Many thanks Macropod:

The above macro works great for the body of the document but refuses to unlink fields in my headers. However, this one did work:
—

Code:

Sub UnlinkAllFields()
Dim rngStory As Word.Range
Dim lngJunk As Long
Dim oShp As Shape
lngJunk = ActiveDocument.Sections(1).Headers(1).Range.StoryType
For Each rngStory In ActiveDocument.StoryRanges
  'Iterate through all linked stories
  Do
    On Error Resume Next
    'rngStory.Fields.Update
    rngStory.Fields.Unlink
    Select Case rngStory.StoryType
      Case 6, 7, 8, 9, 10, 11
        If rngStory.ShapeRange.Count > 0 Then
          For Each oShp In rngStory.ShapeRange
            If oShp.TextFrame.HasText Then
               'oShp.TextFrame.TextRange.Fields.Update
               oShp.TextFrame.TextRange.Fields.Unlink
            End If
          Next
        End If
      Case Else
        'Do Nothing
    End Select
    On Error GoTo 0
    'Get next linked story (if any)
    Set rngStory = rngStory.NextStoryRange
  Loop Until rngStory Is Nothing
Next
End Sub

—

If I knew why, I might be able to modify the one that redirects Excel links to the current folder (we discussed previously). Any suggestions?

Thanks very much!

Reply | Quote

Hi Brent,

The fact you’re iterating through the shapes collection tells me you’re not using the default in-line layout for the links in the header. I suspect that may be at the root of the problem. You could modify the macro I’ve provided to do a similar loop through the shapes and change the link paths.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Macropod and all:

I hope this will be of help to others. I have been from one side of the web to the other and consulted every book I have at my disposal! Here is what I have finally written and it is working for me. The code breaks all links in the document in all the stories but leaves the TOC and page number references intact. With some simple adjustments, it would allow the user to specify which field types to break and in which stories. I hope I’m not jumping the gun; but I have tested it on my documents with very good results.

Here goes:

Code:

Sub FieldsToText()
Dim srng As Range
Dim done As Boolean
Dim fld As Field
Dim i As Integer
For Each srng In Activedocument.StoryRanges
     'If srng.StoryType = wdFirstPageHeaderStory Then
     'Comment back in and modify above if specific to story
     'Otherwise applies to all stories
    done = False
    While Not done
        ct = srng.Fields.Count
        For i = 1 To ct
            Set f = srng.Fields
            For Each fld In srng.Fields
                With fld
                    If .Type = wdFieldLink Then
                        .Unlink
                    End If
                End With
            Next fld
             'Or specify field type(s) to unlink above
        Next i
        Set srng = srng.NextStoryRange
        If srng Is Nothing Then done = True
    Wend
     'End If
Next srng
End Sub

Reply | Quote

Hi Brent,

For a comprehensive approach to breaking Excel links, try:

Code:

Sub FieldsToText()
Dim Rng As Range, Fld As Field, Shp As Shape
For Each Rng In ActiveDocument.StoryRanges
    For Each Fld In Rng.Fields
        With Fld
            If .Type = wdFieldLink Then .Unlink
        End With
    Next Fld
    For Each Shp In Rng.ShapeRange
        With Shp
            If .LinkFormat.Type = wdLinkTypeOLE Then .LinkFormat.BreakLink
        End With
    Next Shp
Next Rng
End Sub

Ironically, the For each Shp/Next Shp loop code is required just to deal with linked shapes (not even linked inlineshapes) in headers and footers. Strangely, shapes in the body of the document are captured by the For each Fld/Next Fld loop, but those in headers and footers aren’t. I suspect that’s a bug.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Macropod,

I will certainly give that a try too- certainly much more eloquent than my rather tedious version. Just learning as I go here! Thanks so much for your help. I hope I can reciprocate in some way- please don’t hesitate to tell me how I can.

Thanks again.

Reply | Quote

I know this is a very old thread, but wanted to thank Paul for the above.

David

Reply | Quote