+ Reply to Thread
Results 1 to 12 of 12

Get rid of unknown links

  1. #1
    Registered User
    Join Date
    08-08-2013
    Location
    Colorado, USA
    MS-Off Ver
    365
    Posts
    44

    Get rid of unknown links

    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
    Attached Files Attached Files
    Last edited by barnett2000; 01-20-2017 at 02:35 PM. Reason: Adding File

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,403

    Re: Get rid of unknown links

    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?
    Regards,
    Aardigspook

    Recently moved house, internationally, during COVID (!) so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  3. #3
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,963

    Re: Get rid of unknown links

    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 ?

  4. #4
    Registered User
    Join Date
    08-08-2013
    Location
    Colorado, USA
    MS-Off Ver
    365
    Posts
    44

    Re: Get rid of unknown links

    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.

  5. #5
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,963

    Re: Get rid of unknown links

    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 !

  6. #6
    Registered User
    Join Date
    08-08-2013
    Location
    Colorado, USA
    MS-Off Ver
    365
    Posts
    44

    Re: Get rid of unknown links

    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.

  7. #7
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,403

    Re: Get rid of unknown links

    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:
    Formula: copy to clipboard
    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.

  8. #8
    Registered User
    Join Date
    08-08-2013
    Location
    Colorado, USA
    MS-Off Ver
    365
    Posts
    44

    Re: Get rid of unknown links

    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

  9. #9
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,963

    Re: Get rid of unknown links

    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.

  10. #10
    Registered User
    Join Date
    08-08-2013
    Location
    Colorado, USA
    MS-Off Ver
    365
    Posts
    44

    Re: Get rid of unknown links

    <<<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!

  11. #11
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,963

    Re: Get rid of unknown links

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

  12. #12
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,403

    Re: Get rid of unknown links

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Store all external links in an array and then replace all links with an empty string
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-17-2016, 03:32 PM
  2. Replies: 0
    Last Post: 04-03-2014, 05:09 AM
  3. VBA: 2 open workbooks; 1 unknown; activating the unknown one
    By Janis Rainis in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-05-2014, 08:46 AM
  4. Replies: 32
    Last Post: 07-23-2013, 01:14 AM
  5. Summing across unknown range in unknown cell!
    By agentred in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 10-28-2009, 10:10 AM
  6. Edit Links - Status Unknown
    By Hillheader in forum Excel General
    Replies: 0
    Last Post: 01-23-2009, 09:27 AM
  7. unknown links
    By BorisS in forum Excel General
    Replies: 1
    Last Post: 11-12-2005, 11:15 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1