Breaking Links in Word using VBA

[Thom]

I am trying to find away to automatically break linked
data from excel with out having to use the "edit" menu,
and after much searching have drawn a blank.

If anyone has any ideas if it's possible, i'd be very
appreciative

 

[Jean-Guy Marcil]

Hi Thom,

Look up the <Unlink> method
(
if your Excel object is inline:
Selection.Fields(1).Unlink
)
or
the <BreakLink> method
(
if your Excel object is wrapped:
ActiveDocument.Shapes(1).LinkFormat.BreakLink
)
..

There are good examples in the VBE help.

--
Cheers!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Guest]

This is absolutely brilliant, and will save an enormous
amount of time.

Just one question.

I can break all the links in the document and the frames,
but can't seem to be able to break the links in the Header
and any text boxes. Text boxes aren't such a problem as I
can, if necessary change them to frames, but the Headers
would be useful.

Any ideas?

Many thanks

Thom

 

[Jean-Guy Marcil]

Hi Thom,

You have to cycle through all the StoryRanges.
Something like:

'_______________________________________
Sub test()
Dim myStoryRge As Range
Dim myCount As Long

For Each myStoryRge In ActiveDocument.StoryRanges
myCount = myCount + 1
While Not (myStoryRge.NextStoryRange Is Nothing)
Set myStoryRge = myStoryRge.NextStoryRange
myCount = myCount + 1
Wend
Next myStoryRge

MsgBox "There are " & myCount & " Story Ranges in this document."

End Sub
'_______________________________________

See the VBE Help, there are some more complete examples.
Look up also StoryType.

--
Cheers!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org


<[email protected]> a écrit dans le message de [email protected]...
This is absolutely brilliant, and will save an enormous
amount of time.

Just one question.

I can break all the links in the document and the frames,
but can't seem to be able to break the links in the Header
and any text boxes. Text boxes aren't such a problem as I
can, if necessary change them to frames, but the Headers
would be useful.

Any ideas?

Many thanks

Thom

 

[macropod]

Hi Thom,

Try:

Sub UnlinkFields()
Dim oSection As Section
Dim shp as Shape
Dim oHeadFoot As HeaderFooter
ActiveDocument.Fields.Unlink
For Each oSection In ActiveDocument.Sections
For Each oHeadFoot In oSection.Footers
If Not oHeadFoot.LinkToPrevious Then oHeadFoot.Range.Fields.Unlink
Next
For Each oHeadFoot In oSection.Headers
If Not oHeadFoot.LinkToPrevious Then oHeadFoot.Range.Fields.Unlink
Next
Next oSection
For Each shp In doc.Shapes
With shp.TextFrame
If .HasText Then
.TextRange.Fields.Unlink
End If
End With
Next
End Sub

This will go through the body of the document, headers, footers and any
shapes (eg text boxes) that contain text, in that order.

Cheers