Page 1 of 2 12 LastLast
Results 1 to 15 of 24

Thread: Hiddne Links

  1. #1
    Clone_of_Struve
    Guest

    Hiddne Links

    I have a spreadsheet in Excel 2000 that was developed from earlier spreadsheets going all the way back to Lotus 123. (Wow, is THIS an old sheet or what?) When I open it, I get a message about links to external sheets. I click NO and the sheet opens with no harm or damage.

    I downloaded the findlinks.xla utility and it found 15 hidden name references to a sheet that no longer exists. When I told the utlility to delete the links, it could not and I got a run time error.

    Anybody have any ideas how to find these rascally hidden links and zap them for good?

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiddne Links

    You can delete names manually via Insert / Names / Define.
    Step through the defined names list box and watch their references for #REFs or external references. Delete those that aren't valid.
    Since you could have duplicate names(exist at both the workbook and worksheet level), you may have to repeat this process on each worksheet just in case. If you only have a couple of dozen names to worry about, it's faster this way then writing a VBA sub to accomplish it.

  3. #3
    Clone_of_Struve
    Guest

    Re: Hiddne Links

    Thanks! I'll try that and see if it works.

  4. #4
    Clone_of_Struve
    Guest

    Re: Hidden Links

    Right! I downloaded the utilities and they found the links, but could not remove them.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hidden Links

    Richard,

    Sorry, I didn't read the first post carefully enought- you already had the first of those utilities.

    Then the Microsoft one also fell over? What sort of error?

    Could you attach the spreadsheet- and tell us where the external links were found?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    Clone_of_Struve
    Guest

    Re: Hidden Links

    I have not been able to access MSN today--- Maybe they were out to lunch. When you open it, it tries to link to a spreadsheet I have called "Financial Statements" and the link XLA found 15 hidden names that referred to it. Odd....

    I was going to attach the file, but the limit is 100 K and it is 172 K in size. Would you like me to send it to your personal e-mail instead?

  7. #7
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hidden Links

    Richard,

    You could send it- but can you cut it down to just a portion of the spreadsheet which has the problem? It's much better to have a lot of minds working on it rather than just one.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  8. #8
    Clone_of_Struve
    Guest

    Re: Hidden Links

    OK, let's try it this way. I have attached two files in a ZIP file that fits the loung attachment limits. Unzip them and rename them from "Financial Statements 2.XLT" to "Financial Statements.XLT" and "Planning Lite2.XLS" to "Planning Lite.XLS". Don't worry about the link for the Financial Statements.XLT, it is fine and works as designed. When you get the Link message for Planning Lite.XLS, click NO, then try the link finder and eraser.

    You'll get a lot of "#REF" cells as the internal links are now gone since I had to strip out several sheets to get it down to this size. But I ** think ** this will help you see what I am running into.

    Thanks for the help!

  9. #9
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hidden Links

    Richard,

    I haven't worked it out yet. However, there do appear to be hidden names which I can't see in the application and I can see in code. I don't know how to look at those names in the application- and I don't know enough about what it's doing to be confident enough to delete them.

    The code I use for viewing them is:

    {pre]
    Dim xlnname As Excel.Name
    For Each xlnname In Excel.Names
    Debug.Print xlnname.Name; "@@"; xlnname.RefersTo
    Next
    [/pre]

    But when I try to delete them in the code I get:
    <pre>Dim i As Integer
    Dim xlnName As Excel.Name
    For i = Excel.Names.Count To 1 Step -1
    Set xlnName = Excel.Names(i)
    Debug.Print xlnName.Name; " "; xlnName.RefersTo
    If InStr(xlnName.RefersTo, "[") > 0 Then
    xlnName.Delete
    Debug.Print "deleted"
    End If
    Next
    </pre>



    I get an error in the delete.

    The Microsoft cleaner has found som other sorts of external links as well- that one's just a start.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hidden Links

    And again!

    Something I tried was to select the 2 sheets which did not have charts, and copy them into a new workbook. I figure the charts would be easy enough to rebuild from scratch. I saved the new workbook, and reopened it- there's no more external links.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  11. #11
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hidden Links

    And yet again- sorry.

    I think I've worked out what's happening.

    There were 5 hidden external names. I thought there were some other external links, but I was just reading the output from the utility incorrectly.

    There's a Knowledge Base Article which describes hidden names and the problems they cause. It gives a method to remove them, which involves deleting the reference as I tried to do in code- but there's a statement at the end which explains your problem (I think):

    "If your sheet names (GW: referring to the reference I'm trying to delete) contain spaces, you may receive an error message when you attempt to delete the defined name."

    Your hidden reference was to "Financial Statements.xlt".

    The article does not give any workaround for that.

    However, what you can do is make the names visible, then delete them manually via the "Insert, Names, Define"

    Dim i As Integer
    Dim xlnName As Excel.Name
    For i = Excel.Names.Count To 1 Step -1
    Set xlnName = Excel.Names(i)
    Debug.Print xlnName.Name; " "; xlnName.RefersTo
    If xlnName.Visible = False Then
    xlnName.Visible = True
    End If
    Next


    I hope that works. I don't know whether those names do anything- that's something you will have to work out for yourself. The names in your workbook all start with "___123" so I assume it's something from Lotus.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Hidden Links

    Hi Geoff,
    This is just a thought - haven't had time to actually try it yet - but could you change the RefersTo of the hidden name in code and then delete it? There was a recent post in the VBA forum re stripping all the spaces out of a string which could do it.
    If I get a chance, I'll try and write and then post some code to do this.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hidden Links

    Rory,

    It's possible, I didn't try it. I was just happy to get a solution!

    If it works you might like to try contacting the author of the non-MS external references cleaner.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  14. #14
    Clone_of_Struve
    Guest

    Re: Hidden Links

    Thanks, Geoff. Did you notice that the names look like 123 fossils? __123Graph{name}? I wonder if somehow old 123 macro code got embedded in the worksheets and never got eradicated as newer versions were developed in 123 and Excel?

  15. #15
    Clone_of_Struve
    Guest

    Re: Hidden Links

    Yes, I was thinking of doing that. (Grrrr) It may come down to that. I was wondering if it would work, and apparently, from your test, it would. Thanks for the confirmation!

Page 1 of 2 12 LastLast

Posting Permissions

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