• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Undeletable Connections!

S

saeed

New Member
Good Day,

After getting great replies on my last question, I’m back again with a new one about the Connections.
There are 3 Connections in many of our files, Excel 2003, which are not linked to any external files but can NOT be deleted over the menu (Edit -> Connections -> Delete Source").

The following code deleted all links BUT these 3:
Sub RemoveLinks()

Dim Link As Variant
For Each Link In ActiveWorkbook.LinkSources
ActiveWorkbook.BreakLink Name:=Link, Type:=xlLinkTypeExcelLinks
Next
End Sub

These links can be listed, using Sam Mathai Chacko’s code, but cannot be removed.
Copying the sheets to a new workbook does not resolve the issue.
Linking them to a new source does not help either.

To my understanding "LinkSources" is a function returning the Link Sources.
Where are these "Sources" stored? How could they be manipulated?
I’ve so far seen: BreakLink and ChangeLink.
Which function or method would remove the entries in the list?
Many thanks in advance.

Saeed
　
 
@Saeed,

Would it be possible to post your workbook with these links remaining? What you have described seems very odd, I'm not sure how I could duplicate it to test.
 
Hi Luke,

These are confidential documents, so I unfortunately cannot post it as is.
I will however, delete the contents and then upload it, if the errors are still there!
 
Not sure if this is relevant to your situation... I once had some links that could not be removed with the
breaklink, etc. options. In the end, I found out that some of the data validations I had been using had some external references (that got there because I copied the worksheet from one workbook to another).
 
Hi Sajan,

Thanks for the tip. How do I check this though?
There are some 50 sheets in the workbook.
How can I systematically sift thru them and look for Data Validations in all the cells of a sheet?
I have "inherited" the file, so I don't know what might existed previously.
Do you know of VBA code which would list the Data Validations?
 
Luke,

here's the file. I deleted all the contents, but these 3 links remain.
I tried changing the source, in one case it worked (link1.xls).
It didn't however work with the other 2.
I also called Data Validation and pressed Clear All button, just in case.
As you can see, it didn't work either.
I look forward to your help.
Cheers
 

Attachments

Hi Saeed,
I ended up using Bill Manville's "Find Link" add-in to identify those connections. I only had a few such connections, so I was able to remove them manually, once they were identified.

Perhaps someone with VBA experience can offer some insights about how to remove bad references in "data validations".

-Sajan.
 
Hi Saeed ,

I am not sure I have understood your problem ; when I go into the Name Manager , and filter on Names with Errors , there are several named ranges which refer to these three undeletable links ; if you either delete all these named ranges , or you relink them by editing them , then these links disappear.

Narayan
 
Bull's Eye Narayan!

Many many thanks. This is exactly the reason the links are still there.
I removed them and thus got rid of the them annoying links.

Much obliged.
Saeed
 
You must log in or register to reply here.
Back
Top