Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    woutervdh
    Guest

    Remove "bad" name (Excel 97 (SR2) Windows)

    Hi,

    I use Microsoft Excel 97 (SR-2) for Windows.

    I have recently found a "bad" name in one of my workbooks. Let
    me explain. When I click on Insert, Name, Define I see
    |||Recor (the 3 lines are actually 3 squares) and in the
    bottom box it says that it refers to "=Instructions!#REF!". Obviously
    this is not a valid reference. I have no idea how it got there in
    the first place.

    I cannot delete this name. I have tried to change it to refererence to a
    valid address so that I can delete it. This did not work either.

    The real problem started when I copied the worksheet, on which this
    problem occurs, to another file and now I have a file name link (in the new file),
    that references to the original file, which I cannot delete.

    Please help. Thanks.

    W.

  2. #2
    woutervdh
    Guest

    Re: Remove "bad" name (Excel 97 (SR2) Windows)

    Hi there,

    Thanks for your response.

    When I run this program (FINDLINK.XLA) and request to delete the link, I get the following error message (maybe the author of this program can comment) :
    "Sorry, an unexpected error has occured in stage Names of FindLInks
    Error 1004: That name is not valid."

    With all due respect, If it was this easy, I probably would not have posted this question here.

    Let me know and I can e-mail you an example of this problem.

    Wouter.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Remove "bad" name (Excel 97 (SR2) Windows)

    Could you possibly upload a copy of the worksheet with th e problem name. You do not have to include any data.

    Andrew C

  4. #4
    woutervdh
    Guest

    Re: Remove "bad" name (Excel 97 (SR2) Windows)

    <P ID="nt"><font size=-1>(No Text)</font>

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove "bad" name (Excel 97 (SR2) Windows)

    I suggest you send an e-mail to Bill asking if he'd like to see your workbook. Just write him I sent you. You'll find his e-mail address on the website I mentioned.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Remove "bad" name (Excel 97 (SR2) Windows)

    Wouter,

    It seems to me th eworkbook is in some way corrupt, and the name cannot be accessed even by way of VBA.

    I was able to copy both sheets to a new workbook, and the reference cleared away. However you need to copy and paste the sheets, as moving them, even when you select create a copy, brings the problem to the new book.

    For the moment that is the best suggestion that I can come up with. I will try digging a bit deeper, just out of obstinacy if nothing else, but would not be hopeful.

    Andrew C

  7. #7
    woutervdh
    Guest

    Re: Remove "bad" name (Excel 97 (SR2) Windows)

    Hi,

    Thank you for your trouble. This problem has been giving me headaches since I can't remember when. Do you think it is worth it running one of those Excel "Corruption" utilities on this file to determine if it is actually corrupt ?

    Have a good week-end.

    W.

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Remove "bad" name (Excel 97 (SR2) Windows)

    What to do depends on your level of curiosity I suppose.

    The actual link seems ok, it's just the name seems to have become corrupt. Have you actually tried the sheet on the PCNetwork with the linked fil ?

    Andrew

  9. #9
    Eugene
    Guest

    Re: Remove "bad" name (Excel 97 (SR2) Windows)

    Hi,

    I've spent some time working .xls files during Y2K, so, out of curiosity I looked at your file using MSFT binary dump utility, and there are at least 3 defined names that appear corrupted:

    021B3: [NAME] (18h 24dec)
    00000 18 00 1e 00 00 00 00 08 07 00 00 00 02 00 00 00 ................
    00010 00 00 00 00 15 00 52 65 63 6f 72 3c 08 00 5c 5c ......Recor<..
    00020 5c 5c -- -- -- -- -- -- -- -- -- -- -- -- -- --
    021D5: [NAME] (18h 24dec)
    00000 18 00 1e 00 00 00 00 08 07 00 00 00 01 00 00 00 ................
    00010 00 00 00 00 15 00 52 65 63 6f 72 3c 03 00 84 84 ......Recor<....
    00020 84 84 -- -- -- -- -- -- -- -- -- -- -- -- -- -- ..
    021F7: [NAME] (18h 24dec)
    00000 18 00 1e 00 00 00 00 08 07 00 00 00 00 00 00 00 ................
    00010 00 00 00 00 15 00 52 65 63 6f 72 3c 02 00 5c 5c ......Recor<..
    00020 5c 5c -- -- -- -- -- -- -- -- -- -- -- -- -- --

    I think it is possible to remove them by direct manipulation on file, not through VBA though.

    Is this file that important to you?

    Regards,

    Eugene

    PS. There also appears to be a link to another spreadsheet -

    K:ReportsRegional_Reports2001 Year-end ReportsOther plc 2001 year end Reports.xls

  10. #10
    woutervdh
    Guest

    Re: Remove "bad" name (Excel 97 (SR2) Windows)

    This file works fine if you open it. I just don't like to have links to external files (as well as invalid references) in my files. I guess it's just a case of trying to keep my files "clean".

    Wouter.

  11. #11
    woutervdh
    Guest

    Re: Remove "bad" name (Excel 97 (SR2) Windows)

    Hi,

    I have tried to remove them by direct manipulation without success.

    This file is not critical to me - I would just like to try and find a solution to a problem that I have had for years. I have received a workaround for this - copy and paste the contents to a new worksheet. However, then you have to set up things like the the print ranges again ?

    Correct, the one sheet demonstrates an invalid name reference to itself and the other demonstrates an invalid name link to another file. I just wanted to give both examples of the problem I encounter.

    Wouter.

  12. #12
    woutervdh
    Guest

    Re: Remove "bad" name (Excel 97 (SR2) Windows)

    Does Microsoft know about this bug ? have they been told ?

    W.

  13. #13
    woutervdh
    Guest

    Re: Remove "bad" name (Excel 97 (SR2) Windows)

    Hi,

    If I do decide to go your suggested route (copy & paste everything, including formatting, to new sheet) what other things must I redo on the new worksheet ? For example, will the print range be trasferred ? How about print margins ? How about header & footers ?

    Please let me know.

    Thanks,

    Wouter.

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

    Re: Remove "bad" name (Excel 97 (SR2) Windows)

    I did find a link to a Microsft KB article- KB 124223- "Cannot Use Visual Basic to Delete Names with Spaces "- but that link is broken.

    In <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q213/4/54.ASP> Q213454</A> There's a statement
    <hr>NOTE: If your sheet names contain spaces, you may receive an error when you attempt to delete the defined name. <hr>
    which implies that perhaps they do know about it.

    I don't know if <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=8792&page=&vi ew=&sb=&o=&vc=1>this post</A> can help you out.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

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

    Re: Remove &quot;bad&quot; name (Excel 97 (SR2) Windows)

    Check <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=30606&page=&v iew=&sb=&o=&vc=1#Post30606>this post</A>. Note that there is a bug in Excel VBA- not in Bill's utility- which gives an error when a reference to a worksheet name with spaces is removed in code.

    And there may be something in <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=8676&page=&view=& sb=&o=&vc=1>this thread</A> which might help.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

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
  •