Forum Discussion

Kacper Modzelewksi's avatar
Kacper Modzelewksi
Copper Contributor
Aug 10, 2017

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...
excel
office 365
k l's avatar
k l
Copper Contributor
Apr 28, 2018

Yes, I tried FindLink. but, it does not see the link.  Though I can see the link destination listed in the "Edit link" .  I tried the "break link" function, but it has no effect.

JKPieterse's avatar
JKPieterse
Silver Contributor
Apr 30, 2018
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 :-)
  • DigitizedSoul's avatar
    DigitizedSoul
    Copper Contributor
    Feb 08, 2021

    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!

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    May 02, 2018
    No, not that I know of. Perhaps you can contact the maker (Bill Manville) about this (via his website as shared earlier)
  • k l's avatar
    k l
    Copper Contributor
    May 02, 2018

    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

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    May 02, 2018
    Findlink did find the link in the data validation settings of a cell. It was also capable of removing that link.
  • k l's avatar
    k l
    Copper Contributor
    May 01, 2018

    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
  • k l's avatar
    k l
    Copper Contributor
    May 01, 2018
    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

Resources