Excel VBA code breaking of links

For Example:


 Range("D4").Select

Selection.Hyperlinks.Delete

Removes link in D4

You should be able to loop through the links in the workbook and break them that way:

Sub sBreakLinks()
 Dim vLinks As Variant, i As Long
 vLinks = ActiveWorkbook.LinkSources(xlLinkTypeExcelLinks)
 For i = 1 To UBound(vLinks)
  ActiveWorkbook.BreakLink Name:=vLinks(i), Type:=xlLinkTypeExcelLinks
 Next
End Sub

Select allOpen in new window

Matt

Hi Matt,

When the statement is executed...

For i = 1 UBound(vLinks)

The following error occurs...
Run-time error '13':
Type mismatch

may be I need to place the code ina more strategic place. Will this error occur if the workbook is not active?

It is coded to work on the active workbook, but could be changed if you wanted to run it in the background or something.

I didn't write anything to check if there were no links; here is an updated version:

Sub sBreakLinks()
 Dim vLinks As Variant, i As Long
 vLinks = ActiveWorkbook.LinkSources(xlLinkTypeExcelLinks)
 If IsEmpty(vLinks) Then
  MsgBox "No links to remove."
 Else
  For i = 1 To UBound(vLinks)
   ActiveWorkbook.BreakLink Name:=vLinks(i), Type:=xlLinkTypeExcelLinks
  Next
 End If
End Sub

Select allOpen in new window

When the code hits the line...
vLinks = ActiveWorkbook.LinkSources(xlLinkTypeExcelLinks)

vLinks is empty

and then that triggers the line...
MsgBox "Nolinks to remove."

When actually the workbook "DRA Summary.xlsx" does have links.

Just as a test I commented out the line...
'vLiknks = ActiveWorkbook.LinkSources(xlLinkTypeExcelLinks)
 
and put in the line...
vLinks = "DRA Summary.xlsx"

I still get...
The following error occurs...
Run-time error '13':
Type mismatch

As another test I took your code out of the main Workbokk "DRA Tool.xlsm" ran the program through which creates the "DRA Summary.xlsx" workbook and then applied your code in "DRA Summary.xlsx" and that worked successfully.

I am hoping there is a way to break the link in the "DRA Summary.xlsx" that is being created while running the program "DRA Tool.xlsm", so that when "DRA Summary.xlsx" is created the links will not be there.

Is it true that the work book MUST be in an active state in order to break the links?

When the code tries to execute the line...
Set vWB = Workbooks("DRA Summary.xlsx")

This error occurs...
Run-time error '9':
Subscript out of range.

When I check vWB the value is Nothing.
When I check vLinks the value is Empty.

If the workbook DOES have to be open to break the links, then that changes the logic. After "DRA Summary.xlsx" is created I guess I would have to open it back up to break the links with the code.
Does that sound right?

Perhaps this would be a solution...

Open the workbook
Apply the break link code
and then save the workbook without the links.

My apologies, I thought you were asking if the workbook has to be active, not open. Yes, absolutely the workbook has to be open for this to work. The code can open the file if you need it to though
Set vWB = Workbooks.Open("C:\DRA Summary.xlsx")

Then at the bottom to close it again you can paste in
vWB.Close True

I believe I will have to experiment with this more and perhaps look at a new stategy to make this work properly.
I really appreciate all your help Matt. I have to leave here in 45 minutes and I need to rap up and I will pick this back up on Monday.
Thanks again and have a great weekend.