break excel links

[striker72s]

Hello,

IV been trying to break links in excel workbooks by VBA
But I wanted to know how to break all the links in the workbooks, no
just one single link

ActiveWorkbook.BreakLink Name:= _
"\\excel\Portfolios\sample.xls" _
, Type:=xlExcelLinks

This code breaks single link, but is there a code to break them all.

Thanks in advanc

 

[Ron de Bruin]

Hi striker72s

You can run this macro also to make text of all hyperlinks
in your worksheet

Sub test()
ActiveSheet.Hyperlinks.Delete
End Sub

Alt-F11
Insert>Module from the menubar
paste the sub in there
Alt-Q to go back to Excel

If you do Alt-F8 you get a list of your macro's
Select "test" and press Run

 

[striker72s]

iv tryed it , the datalinks didnt break ??

ActiveSheet.Hyperlinks.Delete

 

[Dave Peterson]

I stole the code from VBA's help and modified it to go through all the links:

Option Explicit
Sub UseBreakLink2()

Dim astrLinks As Variant
Dim iCtr As Long

astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

If IsArray(astrLinks) Then
For iCtr = LBound(astrLinks) To UBound(astrLinks)
ActiveWorkbook.BreakLink Name:=astrLinks(iCtr), _
Type:=xlLinkTypeExcelLinks
Next iCtr
End If

End Sub

 

[Dave Peterson]

Ron guessed that you were writing about hyperlinks--not links to other
workbooks.

 

[Dave Peterson]

ps. If you want to see the stuff with the links and decide later to break them
(or not)...

Use Bill Manville's FindLink program:
http://www.oaltd.co.uk/MVP/Default.htm

 

[Ron de Bruin]

Thanks Dave

 

[striker72s]

Dave, iv tried the code and it worked,
Thanks a lot guys for the help. Now, ill try to transform it to C#