Using VBA to break specific links

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nick Turner - 11 Apr 2006 01:30 GMT

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

Reply to this Message

Greg Maxey - 11 Apr 2006 01:49 GMT

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

Signature

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

> Hi
>
[quoted text clipped - 7 lines]
>
> Nick

Reply to this Message

Nick Turner - 11 Apr 2006 02:03 GMT

It works beautifully

Thankyou

> Nick,
>
[quoted text clipped - 19 lines]
> >
> > Nick

Reply to this Message

Nick Turner - 11 Apr 2006 02:27 GMT

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

> Nick,
>
[quoted text clipped - 19 lines]
> >
> > Nick

Reply to this Message

Greg Maxey - 11 Apr 2006 02:45 GMT

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

Signature

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

> 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
[quoted text clipped - 32 lines]
>>>
>>> Nick

Reply to this Message

Nick Turner - 11 Apr 2006 02:58 GMT

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

> Nick,
>
[quoted text clipped - 51 lines]
> >>>
> >>> Nick

Reply to this Message

Greg Maxey - 11 Apr 2006 03:20 GMT

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)

Signature

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

> Hi Greg
>
[quoted text clipped - 68 lines]
>>>>>
>>>>> Nick

Reply to this Message