Code to 'break links'

[Mel Smith]

When I copy various worksheets, I need to break the data links on the new worksheet that the original worksheet contained. As I have to do this quite frequently, I'd like to have a macro stored in my Personal Macros Workbook so that I can virtually automate this process. Sometimes there is only one data link whereas at other times there might be three or four to be removed/broken.

Is this possible to do with a macro? Could someone help me with the coding, please?

Mel

 

[Mel Smith]

I've managed to get most of my code to work but I'm struggling to overcome the issue of when there is only one link rather than the three I've coded in so far. These, of course have specific names. Is there a way of introducing an IF function to deal with this?

My code looks like this:

Sub Break_Links()
' Break_Links Macro
ActiveWorkbook.BreakLink Name:="https://d.docs########", Type:=xlExcelLinks
ActiveWorkbook.BreakLink Name:="https://d.doc########", Type:=xlExcelLinks
ActiveWorkbook.BreakLink Name:="https://d.docs########", Type:=xlExcelLinks
End Sub

Mel

 

[Akbarov]

Use this Add-in, that will be more useful than macro.

FindLink

 

[Mel Smith]

Following my previous post, I omitted to use the correct method of posting - my apologies.

My code looks like this:

Sub Break_Links()
' Break_Links Macro
ActiveWorkbook.BreakLink Name:="[URL='https://d.docs/########']https://d.docs########[/URL]", Type:=xlExcelLinks
ActiveWorkbook.BreakLink Name:="[URL='https://d.doc/########']https://d.doc########[/URL]", Type:=xlExcelLinks
ActiveWorkbook.BreakLink Name:="[URL='https://d.docs/########']https://d.docs########[/URL]", Type:=xlExcelLinks
End Sub

 

[Mel Smith]

Hello Akbarov, I don't need to find the data links, I want to 'break' them all in one go via VBA.

Mel

 

[Akbarov]

It can find and delete all of links.

 

[Mel Smith]

That will teach me to read the full document before replying - many thanks.
Mel

 

[Akbarov]

You are welcome