break excel links

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, not 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 advance

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"striker72s" <[email protected]> wrote in message
news:[email protected]...
>
> 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, not
> 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 advance
>
>
> --
> striker72s
> ------------------------------------------------------------------------
> striker72s's Profile: http://www.excelforum.com/member.php...o&userid=24862
> View this thread: http://www.excelforum.com/showthread...hreadid=384061
>

iv tryed it , the datalinks didnt break ??

ActiveSheet.Hyperlinks.Delete

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



striker72s wrote:
>
> 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, not
> 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 advance
>
> --
> striker72s
> ------------------------------------------------------------------------
> striker72s's Profile: http://www.excelforum.com/member.php...o&userid=24862
> View this thread: http://www.excelforum.com/showthread...hreadid=384061

--

Dave Peterson

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


striker72s wrote:
>
> iv tryed it , the datalinks didnt break ??
>
> ActiveSheet.Hyperlinks.Delete
>
> --
> striker72s
> ------------------------------------------------------------------------
> striker72s's Profile: http://www.excelforum.com/member.php...o&userid=24862
> View this thread: http://www.excelforum.com/showthread...hreadid=384061

--

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

striker72s wrote:
>
> 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, not
> 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 advance
>
> --
> striker72s
> ------------------------------------------------------------------------
> striker72s's Profile: http://www.excelforum.com/member.php...o&userid=24862
> View this thread: http://www.excelforum.com/showthread...hreadid=384061

--

Dave Peterson

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

Thanks Dave

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Dave Peterson" <[email protected]> wrote in message news:[email protected]...
> Ron guessed that you were writing about hyperlinks--not links to other
> workbooks.
>
>
> striker72s wrote:
>>
>> iv tryed it , the datalinks didnt break ??
>>
>> ActiveSheet.Hyperlinks.Delete
>>
>> --
>> striker72s
>> ------------------------------------------------------------------------
>> striker72s's Profile: http://www.excelforum.com/member.php...o&userid=24862
>> View this thread: http://www.excelforum.com/showthread...hreadid=384061
>
> --
>
> Dave Peterson