Excel VBA code breaking of links

Is there a way in VBA to break the link in a spread sheet instead of breaking the link manually. See the provided screen shot for clarification.
Thanks in advance.
EE-VBA---break-the-link-in-a-spr.docx
tesla764Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

unknown_routineCommented:
For Example:


 Range("D4").Select

Selection.Hyperlinks.Delete

Removes link in D4
0
mvidasCommented:
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

Open in new window


Matt
0
tesla764Author Commented:
Hi Matt,

When the statement is executed...

For i = 1 UBound(vLinks)

The following error occurs...
Run-time error '13':
Type mismatch
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

tesla764Author Commented:
may be I need to place the code ina more strategic place. Will this error occur if the workbook is not active?
0
mvidasCommented:
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

Open in new window

0
tesla764Author Commented:
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.
0
tesla764Author Commented:
Is it true that the work book MUST be in an active state in order to break the links?
0
mvidasCommented:
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

You're getting the type mismatch because it is trying to find the upper bound of a string (or an empty variable). It is looking for an array.
Is it true that the work book MUST be in an active state in order to break the links?
It does not have to be. Here is a modified code to remove them from "DRA Summary.xlsx" only (the workbook does have to be open)
Sub sBreakLinks()
 Dim vLinks As Variant, i As Long, vWB As Workbook
 Set vWB = Workbooks("DRA Summary.xlsx")
 
 vLinks = vWB.LinkSources(xlLinkTypeExcelLinks)
 If IsEmpty(vLinks) Then
  MsgBox "No links in '" & vWB.Name & "' to remove."
 Else
  For i = 1 To UBound(vLinks)
   vWB.BreakLink Name:=vLinks(i), Type:=xlLinkTypeExcelLinks
  Next
 End If
End Sub

Open in new window

That will solve your issue of removing from the newly created file.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tesla764Author Commented:
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.
0
tesla764Author Commented:
When I check vWB the value is Nothing.
When I check vLinks the value is Empty.
0
tesla764Author Commented:
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?
0
tesla764Author Commented:
Perhaps this would be a solution...

Open the workbook
Apply the break link code
and then save the workbook without the links.
0
mvidasCommented:
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
0
tesla764Author Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.