Phantom External File Links, how can I break them?

[randyharris]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have a few files that when opened in Excel 2007 Windows show that no files are linked, when I open the file in Excel 2008 Mac and look in the EDIT | LINKS two external file links show up. But when I try to break them I cannot. I click Break Link and nothing happens.

I have searched the entire worksheet for the reference to that external file and cannot find anything, my only guess is maybe a chart has a reference (I've noticed that searches for formulas don't give results in Charts.

Thanks

Randy

 

[Bob Greenblatt]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
I have a few files that when opened in Excel 2007 Windows show that no files
are linked, when I open the file in Excel 2008 Mac and look in the EDIT |
LINKS two external file links show up. But when I try to break them I cannot.
I click Break Link and nothing happens.

I have searched the entire worksheet for the reference to that external file
and cannot find anything, my only guess is maybe a chart has a reference (I've
noticed that searches for formulas don't give results in Charts.

Thanks

Randy

You must certainly recognize the name of the sheets. Can¹t you determine if
a chart refers to them? What about internal names? Did you check there? Are
there any active-x controls on the sheet? Do you get any other errors?

 

[randyharris]

This is a rather large spreadsheet with hundreds of charts. One of the linked files I absolutely recognize the file name as one could have linked to, but there are no formulas referencing it, and I can't break the links. The other file that is linked that I can't break I have never heard or seen before.

 

[Bob Greenblatt]

This is a rather large spreadsheet with hundreds of charts. One of the linked
files I absolutely recognize the file name as one could have linked to, but
there are no formulas referencing it, and I can't break the links. The other
file that is linked that I can't break I have never heard or seen before.
Are the links producing errors? Yes, sometimes the references are hard to
find. You need to check the internal names, references on buttons or other
controls.

 

[Jim Gordon Mac MVP]

On 12/17/09 3:50 PM, in article (e-mail address removed)2ac0,

FWIW I experienced the same problem yesterday when I moved a worksheet
from one workbook to another. The cure was to move it back into the
source workbook and copy, instead of move, the worksheet. Then I deleted
the worksheet.

-Jim

 

[jeffr]

.

 

[jeffr]

I have Excel workbooks that have no problems when opened with Excel version
V.x under OS X 10.5. However, I recently upgraded to Snow Leopard and Office
for Macintosh 2008 and have experienced Link errors, one of which was the one
described in this thread.

I did a lot of debugging on the problem and eventually solved it.

Like Randy, I have a workbook that has multiple sheets. When opened with
links updated it wants access to an old spreadsheet that it should not be
concerned about. The Edit->Links->Break Link seems to have no effect.

Like Randy, I searched the formulas across the workbook for the name of the
offending spreadsheet but found no such link.

What I found, after a lot of work, was that the offending link, was not
directly coded but came from a hidden named range.

If I choose Insert->Name->Define it shows me all my named ranges and none of
them use the offending link. However, persevering, I deleted the names one by
one and I found a discrepancy.

It seems that while the list of names given with Insert->Name->Define (I'll
call it the "Name View") does not have any duplicate entries, duplicate
entries are in existence hidden inside the workbook mechanics. I don't know
where, just that they are held somewhere internally. The evidence is that
when I delete a name from the Name View, if it has no duplicate then it
disappears from the list. However, if it has a duplicate, after the name is
deleted it stays in the list but now showing the duplicate and pointing to a
different range or a different link.

I deleted all my names from the Name View, making a list of any names that
were invalid references or which had duplicate entries. When finished, I
closed the workbook without saving it and re-opend it. This time I went to
the Name View and deleted only those names that I had accumulated on my list
as being in error.

When I had finished, I saved the workbook, closed it and reopened it. The
phantom link had gone. I had to reestablish the correct named range for the
duplicate links found and deleted since both the correct and duplicate
entries were deleted.

If you have not too many names in your workbook it might be quicker to just
delete all the names, save the workbook, close it, open it and redefine the
named ranges. I had a lot and decided not to go this route.

I'm not sure if all this saving, closing and reopening is necessary. I did
it to be sure.

So that's what worked for me. It seems that an Excel workbook can contain
multiple occurrences of a named range. The Name View shows only one
occurrence but will display the hidden occurrence if the first one is
deleted. In this way, the hidden links can be found and deleted.

Hope it works for you.

.....Jeff

 

[jeffr]

I forgot to say that I think these duplicate named ranges enter the workbook
when the "Edit->Move or Copy Sheet" menu command is used to copy or move a
sheet to the workbook. It seems that Excel does not (or did not) correctly
resolve the duplicate names at the time the sheet was added.

......Jeff

 

[CyberTaz]

Hi Jeff;

A good portion of you've "discovered" ‹ and gone to a great deal of trouble
to confirm ‹ is a 'feature' of Excel which has caused problems for eons
... It is entirely possible to have the same range name used multiple
times in the same workbook. IOW, range names can be either Global or
Sheet-specific. (I can't comment on the impact this may have on 'Move or
Copy' but your finding certainly sounds possible.)

This can be a beneficial feature if used intentionally, knowingly &
implemented properly. My contention, though, is that it's too easy to do so
without realizing it ‹ especially if more than one user works in the file
because there are no 'checks & balances' that apprise the user that a range
name already exists on other sheets. There is no *thorough* & explicit
documentation on on the repercussions it can cause (at least, I can't find
it if it exists). Most resources only describe how to create local range
names correctly, such as the info on Chip Pearson's site under the topic:
Global-Scope And Sheet-Scope Names;

http://www.cpearson.com/EXCEL/DefinedNames.aspx

What often happens, however, is that a workbook will have several similar
sheets, each for different Division, Location, etc. Those sheets will have
quarterly figures for various categories of values [such as Hardware,
Software, Accessories] specific to that group of records. A user will select
the range of categories & use Insert> Name> Create command on Sheet1 to
generate named ranges based on those categories. Or they may simply define
the names themselves based on labels common to both sheets. At that point
they are Global range names.

Then that user or a different user will use the Create command to create
range names on Sheet2. Absolutely no notification or prompt is generated to
indicate that those same names have already been used in the book. The
ranges on Sheet1 simply are "converted" from Global to Local on Sheet1 & the
new [duplicated] range names become Local to Sheet2.

Thanks for the detailed report. I'm certain that it will be helpful to
others & I intend to use it to help bolster my efforts to get the behavior
modified in time for the next release of Office.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Bob Greenblatt]

I'm glad you solved your problem. What you were seeing was the distinction
between a local sheet name and a global name with the same name. You can
find these more easily than the route you took by using:
Insert-Name-Paste-Paste List. This will paste the entire name table into a
work sheet starting at the active cell. You should then be able to scan (or
search) this list for the offending link.

 

[CyberTaz]

Hi Robert;

Assuming you're still out there perhaps you've seen my reply to Jeff by now.

Unfortunately, your suggestion doesn't really help in this scenario. The
list that gets pasted only includes the ranges from the *first sheet* where
the duplicated names were created. It's only if the names were actually
created using "Sheet1!Hardware", "Sheet2!Hardware", etc. that they show up
as separate items in the list. The list doesn't even suggest the presence of
the second & successive ranges if they have simply been named "Hardware" on
each sheet, which is what happens when you use Insert> Name> Create... The
sheet names do not get appended to the labels being used.

Further, the existence of the additional ranges is only disclosed when you
are on those respective sheets. IOW, the list only includes any Global range
names plus the Local range names specific to that sheet... And if you select
multiple sheets the Paste command isn't available. The only way I've found
(other than programmatically, perhaps) is to go from sheet to sheet & either
paste the list for that sheet or look in the Define dialog (which I believe
is what the list is generated from). The Paste List dialog doesn't offer any
option or distinction pertaining to Local ranges on other sheets.

For example, if the name "Hardware" was created on Sheet1 first & Sheet2
second:

=SUM(Hardware) on Sheet1 sums those values in the range on Sheet1,

=SUM(Hardware) on Sheet2 sums the values in that range on Sheet2, but

=SUM(Hardware) on any other sheet in the book where a local range by that
name doesn't exist sums the values in the range named "Hardware" on Sheet1,
& if the range name was first defined on Sheet2 those are the values summed.

Call it a "bug", "by design", whatever. The fact is that it can have some
quite unexpected, undesirable & problematic results. IMHO, any time an
attempt is made to create/use an existing range name there should be a
notification, option and/or automatic updating of the pre-existing range
names to append the appropriate sheet name prefixes & convert them to local.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[jeffr]

Thanks, Bob. That would have saved me a lot of time. Once I paste the list
and find the offending link, what do I do to remove it?

.....Jeff

 

[jeffr]

Hi, Bob.

Thanks for your reply. I followed the link you gave and it, together with
this thread, was a useful addition to my understanding of Excel links.

.....Jeff

 

[jeffr]

Hi, Bob. I answered my own question by doing some trials. I saw (and Bob
Jones also said) that you have to do the paste list on each sheet and compare
the lists. Then the offending name can be deleted by going to the sheet that
defines the offending name and delete it there.

......Jeff

 

[jeffr]

When I did the "Edit->Move or Copy Sheet" I was moving in a sheet that had
identical names and name ranges to the ones in the destination workbook.
Therefore, when I was asked whether I wanted to use the names on the source
or those on the destination workbook, it didn't seem important since they
were the same. I might have clicked on either choice.

Now, I see that choosing the names on the source workbook created new
sheet-specific names that were links back to the external source workbook
rather than locally to sheets in the destination workbook. Hence my phantom
link.

Now I know and I won't make that mistake again.

Thanks for helping me to get it straight.

......Jeff

 

[Bob Greenblatt]

When I did the "Edit->Move or Copy Sheet" I was moving in a sheet that had
identical names and name ranges to the ones in the destination workbook.
Therefore, when I was asked whether I wanted to use the names on the source
or those on the destination workbook, it didn't seem important since they
were the same. I might have clicked on either choice.

Now, I see that choosing the names on the source workbook created new
sheet-specific names that were links back to the external source workbook
rather than locally to sheets in the destination workbook. Hence my phantom
link.

Now I know and I won't make that mistake again.

Thanks for helping me to get it straight.

.....Jeff

The easiest way to find the global names is to insert a new sheet and then
paste the names into that sheet.

 

[Donkey Hotté]

jeffr,

after hours of searching your recommendation worked I deleted all the defined names and the links disappeared.

They had come from a histogram xlsx i had used as Mac excel lacks Analysis Tool Pack -which is another complaint.

Thanks again.

 

[1abney]

Thank you! Thank you! Thank you! Thank you!

This helped me much!!

 

[ga.greenarrow]

The easiest way to find the global names is to insert a new sheet and then
paste the names into that sheet.

Hi Bob

I have attempted everything I could find about removing the phantom link; from removing all defined names, removing all objects etc and have taken me days to redo. I have no charts in my file as its a game console. After months of learning VBA to develop this console, it is completed but tainted with this life drawing phantom link. Where else could it be? Findlinks.xla delinks.xla has also been used. Short of redoing the entire program, there is no other options I can think of. If I have to redo the program, I rather give it up all together. Any last options I can explore? Wise I will just deliver the apps with the phantom and be done with it. Players will just have to click ignore links each time they open it. I dont care anymore.

 

[ga.greenarrow]

Hi Bob



I have attempted everything I could find about removing the phantom link;from removing all defined names, removing all objects etc and have taken me days to redo. I have no charts in my file as its a game console. After months of learning VBA to develop this console, it is completed but tainted with this life drawing phantom link. Where else could it be? Findlinks.xla delinks.xla has also been used. Short of redoing the entire program, there is no other options I can think of. If I have to redo the program, I rather give it up all together. Any last options I can explore? Wise I will just deliver the apps with the phantom and be done with it. Players will just have to click ignore links each time they open it. I cant find anymore energy to care anymore.

For Reference in my Case
The Source file no longer exist as I believe the links was created in a temp file when excel did one of those crash events. I realize this when I deleted the temp file created when excel crashes.
What I have tried?
1.I re-created using a backup file and changed its name to the source file name that was deleted but it did not work.
2.I tried changing source file but it wont make the change.
3.I could not set the updatelink of that link to MANUAL as the option is greyed out and remains "Automatic".
4.I have swept the file using findlinks.xla and Delinks.xla several times and removed objects it suggested but the link remains.
5. I then decided to remove all objects (as the xla seem to point to those source) but its still there.
6.updatelinknever and asktoupdatelinks are functions that makes no changes to anything.
7. The find links vba code recommended by microsoft does not even run properly as it did identify that link but after clicking remove, it wont remove the link. I was so happy when it found the link and clicked remove but unfortunately, phantom remains a close friend that never goes away.
8. I re-copied the entire sheet again from what originally caused this linkto occur but the the end it made no difference as phantom was still my best friend and wont go away.

How do I divorce this phantom?

Many thanks for any other options short of redoing the whole system can besuggested.