Using VBA to break specific links

[Nick Turner]

Hi

I need to write a macro to break all Excel links in a Word document, while
leaving the other links in tact. I have both charts and text linked from
Excel which I need to break. The other fields that I have in the document
are Word captions and cross references, which I need to remain in tact.

TIA

Nick

 

[Greg Maxey]

Nick,

You might need to beef this up a bit, as it simply looks for the string
"Excel" in your field code. Still it might work:

Sub Test()
Dim oFld As Field
For Each oFld In ActiveDocument.Fields
If InStr(oFld.Code.Text, "Excel") > 1 Then oFld.Unlink
Next
End Sub

 

[Nick Turner]

It works beautifully

Thankyou

Nick,

You might need to beef this up a bit, as it simply looks for the string
"Excel" in your field code. Still it might work:

Sub Test()
Dim oFld As Field
For Each oFld In ActiveDocument.Fields
If InStr(oFld.Code.Text, "Excel") > 1 Then oFld.Unlink
Next
End Sub

--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

 

[Nick Turner]

Thanks Greg, but I jumped the gun a bit. This Macro works fine for the body
of the word document, but it doesn't not work for the headers, footers and
footnotes. Any ideas on how to get it to work in the entire document?

Thanks Nick

 

[Greg Maxey]

Nick,

Sorry. Try:

Sub UnlinkExcel()
Dim oStoryRng As Word.Range
Dim oFld As Field
Dim lngJunk As Long
lngJunk = ActiveDocument.Sections(1).Headers(1).Range.StoryType
For Each oStoryRng In ActiveDocument.StoryRanges
Do
For Each oFld In oStoryRng.Fields
If InStr(oFld.Code.Text, "Excel") > 0 Then oFld.Unlink
Next oFld
Set oStoryRng = oStoryRng.NextStoryRange
Loop Until oStoryRng Is Nothing
Next
End Sub

 

[Nick Turner]

Hi Greg

Thanks. That does the trick.

I know I'm pushing my luck, but do you know if there is a way for the macro
to pickup links that are in textboxs in the word document.

Cheers

Nick

 

[Greg Maxey]

Nick,

The macro I sent you last unlinks in text boxes that I inserted in my
document.

Send me the document via email (or a sample)