Get rid of unknown links

[barnett2000]

I have some large files that I inherited that are causing a lot of problems, so for 2017, I built a new file. Somehow, when I copied something from the old file to the new one, a link was created to a file that doesn't exist. I've tried inserting a new worksheet then deleted all the other worksheets, and the link still exists. I've tried breaking the link, but it still shows up as a linked file.

No formulas, no named ranges, no conditional formatting, no hidden worksheets. I've tried everything I could find online.

How can I get rid of this???

Added a sample file. You may note that the file size is way bigger than it should be for that small amount of data, so there is something in the background that I can't find.

Thanks,
David

[Aardigspook]

Where is it appearing as a linked file? I assume it's not in a formula, as that would be easy to find and remove. Are any Names are defined which reference that file?

[Logit]

I tried saving the file as .xlsb and that actually made it bigger.

Is there anyway you can start over with a new workbook ? I realize your attachment was a sample ... but can you begin anew or is the original file way too big ?

[barnett2000]

I actually did that originally. There are a number of tabs in this file, but I'm also trying to figure this out so I can fix existing files that have the issue that can't be easily redone, or I don't have the time. This problem exists for a lot of people in my company, so if I can solve this, it would be a big win for a lot of people.

[Logit]

so if I can solve this, it would be a big win for a lot of people.

I agree !

I looked for formulas, hidden sheets, cells in obscure areas with data, etc. First thing I did was try to delete all the rows below your data that were not being used.
Not certain that really worked.

Finally, I utilized the freebie here --> https://excelfilecleaner.codeplex.com/ ... and that cleaned up what ever was going on.

The freebie cleaner reported 537 invalid named ranges were eliminated.

I would make a copy of the "real workbook" and try this utility on the copy first. Don't want to jump off the cliff without a parachute !

[barnett2000]

I can't download that at work, so I'll have to try it from home this weekend.

I'd like to know what it actually does, though. I can't email all the files with this issue to myself at home. I already had deleted all the named ranges, so there must be some behind-the-scenes code somewhere that is holding the named ranges, even though they don't show up in Name Manager.

Thanks for the tip. I'll check it out.

[Aardigspook]

This has come up before on other threads. On this one, in post 16, there's code which searches for any possible causes of hidden external links:
http://www.excelforum.com/showthread.php?t=825383
I ran the code on your file and it listed 95 Named Ranges which refer to external workbooks.
So, run this macro and it'll make your hidden named ranges visible:

Please Login or Register  to view this content.

You can then delete them - make a copy of the file first, for safety's sake!

Hope that helps.

[barnett2000]

I think I found a solution. Apparently there are "hidden" names in the file. I found some VBA code that I tested on the test file, and it solved the problem. I ran this on my larger file and it also solved the issue. Once I ran this, I could see the hidden names in the Name Manager, then delete them.

Sub Unhide_Hidden_Names
Dim n As Name
For Each n In ActiveWorkbook.Names
n.Visible = True
Next n
End Sub

[Logit]

Curious how you ran that sub.

I put it in a routine module and tried running it from the VBE and also from a commandbutton. Nothing happened.

[barnett2000]

<<<I put it in a routine module and tried running it from the VBE and also from a commandbutton. Nothing happened.>>>

When you run it, it doesn't look like anything happens, but if there were hidden names in the sheet, they will now show up in the Name Manager, where you can then delete them.

Note to Aardigspook, it looks like I found the solution elsewhere and posted it about the same time as you. Thanks for your efforts!

[Logit]

Thanks..........................

[Aardigspook]

You're welcome - glad you got it sorted.
If that's your problem fixed, please take a moment to mark the thread as Solved so others know there's an answer here (instructions in my sig). Thanks.