Macropod,
I also tried option 3, the MS Macro. Same issue - it found the link but
failed to delete it. The error was:
"run-time error '1004'. Application defined or object defined error."
Seems like the same sort of issue all over.
--
regards
....barglen


"barglen" wrote:

Thanks again Macropod.
I tried the 1st 2 options. Neither worked.
The problem is not with the actual link itself or the target workbook.
The problem is the name of the "Defined Name".
It's name is showing as the characters between the dashes --G! Pš--
The Delete Links Wizard found the link OK but when asked to delete it it
gave this error: "Routine Location error Code:6 Some Defined Name links may
not have been deleted."
Likewise Findlink found it but couldn't delete it.
And Insert/Names can see it but not delete it.
Seems like somehow its a half created link and nothing can get at it.
Think I'll leave it there.
Sometime in the future I'll recreate the whole workbook.
At least, from your 1st option, I've been able to rename the target to a
name that reminds me not to delete it.
Thanks


--
regards
...barglen


"macropod" wrote:

Hi barglen,

Another approach might be to open both the problem workbook and your
'temporary' one, then save the 'temporary' one under a new name via 'Save As',
making sure not to include the exclamation mark in the filename. With any
luck, that should let you delete the offending name from your problem
workbook.

Another possibility is the Excel 'Delete Links Wizard'. See:
http://support.microsoft.com/kb/188449/en-us

Alternatively, you could try an MS Macro to Delete Defined Names with Links,
available at:
http://support.microsoft.com/kb/138619/en-us

Cheers

--
macropod
[MVP - Microsoft Word]


"barglen" wrote in message
...
| Thanks Macropod, you are being a truly great help here - appreciated. Its an
| area in which i am outside my knowledge base. My comments below assume that
| i have understood your reply, which may not be the case.
| However, still have some issues.
| I had already created a dummy workbook so that my spreadsheet would open
| without failing to find the linked workbook. It opens OK. Its just a pain
| that I have to have that dummy workbook continually available. So, if I've
| understood your 1st option, that won't work since when I tried the delete of
| the defined name earlier it gave the "name is invalid" message. That was
even
| though the linked workbook was available (ie the dummy).
| The 2nd option is problematic also -- the reference for the defined name is
| to #REf in the dummy workbook. And it won't let me update that to something
| else.
| Re SYLK - that did allow me to find the reference line in notepad and delete
| it, but it only saves one worksheet I have a multi sheet workbook. (Also
lost
| all the formatting but that could be recreated easily enough).
| I tried saving two different worksheets to sylk -- only the current
| worksheet data gets saved -- but the phantom link was listed on both of the
2
| sylk's that I saved.
| Seems like I have problems whichever way I go. Maybe best to just let it go
| and keep the dummy workbook always available. But that's irritating.
| Or I could revcreate the whole thing when I get time ho ho.
| By the way, the defined name that has the link starts with G but the rest of
| the 7 characters or so are not ones that are reproducible on a keyboard. I
| think that's why I'm getting the "name is invalid" message.
| Its getting late here now so I'm off for the night. Thanks again for the
| replies.
|
| --
| regards
| ...barglen
|
|
| "macropod" wrote:
|
| Hi barglen,
|
| You might try creating a temporary workbook with the link's name (not
| including any cell references), then either:
| .. delete the defined name and destroy the link;
| .. use Edit|Links to change the source workbook to any other workbook. It
| doesn't matter which, since the next step will be to delete the defined
name
| and destroy the link; or
| .. open the temporary workbook and cut & paste the referenced cell into
the
| 'problem' workbook (this kills the external link), then delete the defined
| name
| after which you can delete the temporary workbook.
|
| Hopefully, at least one of these will work.
|
| As a last resort, you could try saving the workbook in SYLK format, using
a
| text editor to find and delete the offending reference, then re-opening in
| Excel.
|
| Cheers
|
| --
| macropod
| [MVP - Microsoft Word]
|
|
| "barglen" wrote in message
| ...
| | Thanks Macropod,
| | I didn't know about that feature - yes I can see the weird name in
"defined
| | names". And it refrers to the spreadsheet thta is being asked for on
open.
| | But when I try to delete the defined name I get the message - "that name
is
| | not valid".
| | I tried modifying it by blanking out the reference but still get "name
not
| | valid" when I try to save it.
| | Any further advice? Appreciate the help so far.
| | regards
| | ...barglen
| |
| |
| | "macropod" wrote:
| |
| | Hi barglen,
| |
| | If the name isn't in any worksheet cells, it could nevertheless be in
a
| | defined named. Check in Insert|Name for the offending reference in the
| defined
| | names.
| |
| | Cheers
| |
| | --
| | macropod
| | [MVP - Microsoft Word]
| |
| |
| | "barglen" wrote in message
| | ...
| | | I have created a new spreadsheet in Excel 2000 which involved some
| copying
| | | and pasting from a previous spreadsheet. There are multiple
worksheets
| in
| | the
| | | xls. Its fairly complex so don't want to recreate the whole thing
again.
| | | At one point the copy of a worksheet failed (crash with error report
| | request).
| | | The new spreadsheet seems to be OK, except on opening it is asking
for
| the
| | | password for a link to another spreadsheet. There is no link to that
| | | spreadsheet anywhere in the new spreadsheet. I've searched the
entire
| | | spreadsheet and there is no genuine link.
| | | So I used the Findlink add-in and it found a link to the
spreadsheet.
| But it
| | | names the source of the link as a worksheet which doesn't exist -
the
| source
| | | worksheet has a name something like G!11Ps. There is no worksheet of
| that
| | | name in the new spreadsheet. When Findlink is asked to remove the
link
| it
| | | gives an error and doesn't remove it.
| | | It seems there is a phantom worksheet in the new spreadsheet -- and
the
| | | phantom has the link that is coming up on open.
| | | How can I find and get rid of the phantom worksheet ? Any ideas?
| | |
| | | --
| | | regards
| | | ...barglen
| |
| |
| |
|
|
|