Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Nov 2010
    Location
    Gainesville, Georgia, USA
    Posts
    11
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    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
    [MS MVP - Word]

  3. #3
    New Lounger
    Join Date
    Nov 2010
    Location
    Gainesville, Georgia, USA
    Posts
    11
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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!

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    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
    [MS MVP - Word]

  5. #5
    New Lounger
    Join Date
    Nov 2010
    Location
    Gainesville, Georgia, USA
    Posts
    11
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    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
    [MS MVP - Word]

  7. #7
    New Lounger
    Join Date
    Nov 2010
    Location
    Gainesville, Georgia, USA
    Posts
    11
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •