Forum Discussion
how to remove external links from excel
hello Please help me with following task - I try to remove external links from excel: Data==>Edit Links==>break link It does not work - still I have that external links in the list. Even I c...
Can you perhaps send the file to me?
i n f o AT j k p - a d s . c o m
Please include a link to this page with your message so I know what the email is about :-)
i n f o AT j k p - a d s . c o m
Please include a link to this page with your message so I know what the email is about :-)
I sent a private email to Jan Karel, but thought others might benefit to see what I discovered.
-------
Before sending the excel to you, I decided to remove a lot of stuff that I was sure would not be the issue, since they were recently added. But, as I did so, I realized that I could do so incrementally, and perhaps narrow the problem down. I continued until I only had one cell that caused the problem. It was a Data Validation cell. The FindLink.xla program could not find it.
Perhaps the creator of FindLink.xla could modify it to see these types of links. But if not, I wonder if a program could be created that would systematically delete portions of a spreadsheet.
1) Save as Test.xls
2) Delete half the tabs , save as Test1
3) Open Test1, and see if the error occurs.
a) if no error, close Test1, then re-open Test and delete the other half of the tabs.
b) If error, delete half of the remaining tabs.
4) Repeat the above cycle, until only 1 tab remains.
5) Do a similar process for the rows of the spreadsheet, then the columns, until only one cell remains, which is the problem cell.
There would need to be some method to keep track of the cells, so that at the end, it could say what tab and cell reference was a problem. I also imagine there may be multiple cell reference problems. So, I guess my steps a and b, both Test.xls and Test1.xls files should be checked for the external reference error.
Attached is the problem spreadsheet. It is empty, with just cell A1 containing the external data validation link. Unfortunately, I didn't work on a way to keep track of which cell it was. At least I know which tab.
-Joe Dunfee
k l Just a quick THANKS!
Been driving me crazy, but was datavalidation in my case too. I just selected all, cleared validation and boom, problem gone!
- No, not that I know of. Perhaps you can contact the maker (Bill Manville) about this (via his website as shared earlier)
Then perhaps the FindLink program is not working properly on my system. I have it installed as an add-in, and ran it on this file. I also have some Visual Basic that I use regularly, so I know macros are fully enabled.
Is there anything you know of that will prevent FindLink from working properly?
-Joe
- Findlink did find the link in the data validation settings of a cell. It was also capable of removing that link.
I just realized that I had failed to save the version, where I had isolated the data validation cell that had the external link. One of the challenges with saving so many versions, as I gradually deleted more and more.
So, I am not sure which of the 4 remaining tabs it was, nor the cell itself.
-Joe
