Excel 2010 - Can't Remove Links

[3gswish]

Hi everyone,

I've gone through all of the previous posts regarding removing bad links, but the previous posts are pretty stale, and most of the links in them are no longer working, or refer to code/add-ins that no longer work in Excel 2010, so I hope you don't mind my restarting this topic in a new thread

I have tried all of the suggestions in them anyway, and have only partially resolved my issue.

I am working in a workbook that contains 10 worksheets with varying in row count from 100 to 1000.

A number of them were copied from a couple of other workbooks. At the time I copied the data (either by doing the move/copy worksheet or cut/paste) I was not aware of links and how hard they would be to deal with after the fact!

The edit links dialog was referencing 2 different files.

I was able to delete all of the named ranges which cleared up one of the links in the edit links dialog.

There remains a link to the second file, with Status = Unknown, which I cannot get rid of !!!!

When I click Break Link, I get a response that this convert to values, and when I click Break Links in that popup, the popup goes away without complaint, yet the link remains.

I have searched for "[" and "]" in the workbook, with no results found.
I have searched for part of the linked filename in the workbook with no results found.
I have tried the microsoft DeleteLink add-in - it doesn't seem to work in 2010.
I have tried finding objects via a macro that was posted years ago, it errors and must not be good for Excel 2010.

I have spent more time trying to figure this out than it would have taken to simply cut/paste values into a new spreadsheet.
(Which I can do, as this workbook only contains values and minimal formatting)

My problem is that I am unable to let go of a problem until I find a proper solution!

Thank you in advance for any input you would like to provide

 

[3gswish]

Figures - right after I post, I figure out what was going on.

One of the worksheets was either cut/pasted or moved/copied and it contained column filters.

I was unable to clear the filters (they were greyed out), so I cut/pasted values only to another worksheet, and deleted the sheet with the bad filters.

Viola. Data-Connections-Edit Links is now greyed out (no links exist) and the workbook opens without error.

I consider my solution a workaround, not a true fix. If I had lots of other formulas, references, and major customizations to the worksheet, my method would not be ideal !!

So, if you want to respond for the edification of myself and future problem seekers, please feel free! I will continue working with a bad copy of my file to test any of your suggestions...

Thanks!

 

[CliffyB]

I'm currently using Excel 2010 on Windows 7. I have a file that has 3 external links to it. I have tried all the steps in the first post of this thread to no avail. Two of those files reside on my computer, which must have been created when I copied/pasted some information over. The third link is to a file on a website (my source file was emailed and a 3rd party must have saved the file to this website). For the life of me I cannot delete any of these links! Even the 2 that reside on my computer, I've tried to "Change Source..." to no avail. I'm at a loss, and extremely consternated with MS. I could provide screen shots, as jpg's, upon request. Any help would be most appreciated.

Thank you,
Cliff

 

[TerjeL]

Have you tried Name Manager (under Formulas)? Also this link has got some nice tips : Finding and Deleting Hidden Links in Excel

 

[KBBRISBANE]

Hello,
I am having great difficulty deleting 3 files that are linked to me spreadsheet (excel 2010). I have tried everything from deleting named ranges etc. I have used add-ins for previous version sof Excel. Could I save as a lower version and use the add in to remove the links? or is there another way! I have spent around 6 hours already trying to get this to work! Thanks in advance.

 

[KBBRISBANE]

I am hving great difficulty removing three linked files in Excel 2010. I have removed names ranges etc, looked for [ and .xls etc. There is a add-in that worked ion lower levels of the excel so could I save my file as 97 and then use the add-in? or is there another way?

 

[RossFon]

I had the same problem, turned out the links were in formulas within conditional formatting, I rewrote the formula and problem solved.

 

[WAQQAR]

I had the same problem, turned out the links were in formulas within conditional formatting, I rewrote the formula and problem solved.

This is a issue with Excel, unfortunately sometimes even after removing the links from Excel file it seems to be sting in. The only solution what works is that you have to copy each formula of the excel into notepad and then again past back by copying from notepad into excel. This will remove the hidden links within excel. It was annoying for me too as well but this is the solution that perfectly worked at my side.

 

[ginipig]

Thanks all for your responses. Here is what I did

Systematic elimination is the only solution! Follow below mentioned steps if you could not get rid of External Links!

In my case it was a problem of copying a sheet from other workbook and the remains of ref were found in conditional formatting! Excel should do better in copy/move sheet operation, similar to "Paste Special" but currently it does not.

Ok, so here is what I did and what you can do

0. I assume you have all tried with 'Find all with "[" and "!"' and eliminated all the cells in workbook for these contents and still you are facing this issue. Also I assume you are not having any other external links in the file before. You can still use these instructions but it may be more tedious to work on.

1. Identify when the problem started, in most cases the last copying sheet operation would have brought in this insect. If you do not know or do not have a clue about this, and if you have many sheets, it would be harder but patience is the key!

2. Make a copy of the excel file you are having and now on use this copy itself for experiment

3. Delete the sheets from the workbook that you feel are creating issue and save the file.

4. Re-open the file, goto DATA Ribbon and see if the "EDIT Link" option is enabled. If not, you have found the sheets that have links. If its still enabled, you deleted the wrong sheets.

5. You get a fresh copy and try again with differernt sheets till you zero in for the problematic sheet.

6. Now, you should have a copy of file with the problematic sheet removed and with no links dialog. Add a blank sheet at the place where the sheet was deleted and give the same name for the sheet

7. Open the original workbook and select all from the sheet, come to the blank sheet and Paste Special with option "Column Width". This will set the column widths according to original sheet.

8. After every step, check if Edit Link option is being enabled under DATA Ribbon, if it is not, Save the experimental sheet

9. Now use "Paste Special" to copy formula

10. Finally use "Paste Special" to copy Formatting

11. From steps 7 till 10, at some point, your "Edit Link" option will become enabled, when it does, Close file without saving and reopen to get the file back in without "Edit Link" enabled.

12. You have just narrowed your problem to formula or formatting.

13. Now use divide and conquer rule by copying parts of cells from the original sheet to know which cells are causing the issue and you will soon understand the problem cell and eliminate the same.

14. Once you know the root cause, go back to the original sheet and remove the problem causing elements such as formula, conditional formatting or any other issue and you will become Link FREE!!

Although the steps are many, actual time may not take more than 15-20 minutes to exeperiment. Hope this helps!

 

[konew1]

3. Delete the sheets from the workbook that you feel are creating issue and save the file.

4. Re-open the file, goto DATA Ribbon and see if the "EDIT Link" option is enabled. If not, you have found the sheets that have links. If its still enabled, you deleted the wrong sheets.

5. You get a fresh copy and try again with differernt sheets till you zero in for the problematic sheet.

This assumes that the problem is on only one sheet. I have found a similar process where (on a copy of the file) I have moved one sheet at a time to a new workbook. Each time I check the new workbook and expect to see links to the problem file, and when links to an unwanted file occur you have found one sheet with ghost links. Then check the original file for further unwanted links, and carry on to a different new file until all problem sheets are identified.