Bad External Link

[TH]

I am getting this message ONLY when I QUIT Excel (2000) with this 1 workbook
open: "Your formula contains an invalid external reference to a worksheet."
If I simply close the workbook (without quitting Excel) the error does not
appear.

I have Googled the Excel newsgroups and tried these solutions to no avail.

1. FINDLNK. This shows no external links.
2. Unmerge every cell in the workbook and rerun FINDLNK. Nothing.
3. Set Application.DisplayAlerts = False in the workbook_beforeclose event
hoping to just by pass the message. No good.
4. Searched for renamed sheets. I renamed a sheet from '7' to '8' about the
time this started happening. No links found for '7'

Now for the weird one:
5. I tried setting breakpoints in the workbook_beforeclose to find the line
of code that was failing. It DOES NOT FAIL with a breakpoint set!

This behavior appeared in this workbook about 5 revisions ago (out of 100+).

Last note: this does NOT happen on a machine running WINXP/EXCELXP. I am
running Win2K SP-4 and Excel2000 SP-3.

I really need this message to go away in order to automate this program
while I'm gone. It's my stock trading program. Any ideas will be greatly
appreciated.

Terry

 

[TH]

So, no one knows the answer to my question???
This is not the typical Bad Link problem. Please read and HELP!

 

[Robert McCurdy]

Don't bother to find out why this works.
Just open a clean template and copy over your data.
Close the other workbook and Save as the new one deleting the old one.

Otherwise if you must know, check the Names you may have a broken link.
Check your Links under the View menu item and click open.
If that don't work cause XL can't find the other file, pick Change Source and point to this open file.

Here is a Macro for you to see all your names as some may be hidden, or you may have several with the same name on different sheets.

Sub ListAllNames3()
Dim n As Name, x As Long
With ActiveCell
For Each n In ActiveWorkbook.Names
n.Visible = True
.Offset(x, 0).Value = n.Name
.Offset(x, 1).Value = " " & n.RefersTo
x = x + 1
Next n
End With
End Sub


You can also check on all your formulas by using the special cells feature F5 > Special > Formulas > Errors > OK.
Now all formula with errors are selected for the sheet you are on.


Regards Robert

 

[TH]

Robert,

Thanks a bundle. I will need your detailed suggestions as this workbook has
300 names, 20 sheets, 5000 rows per sheet and several thousand lines of VBA.

I have already searched the names with Name Manager to no avail, but I will
try all your suggestions.

ONE QUESTION: Did you mean check my links under the EDIT / LINKS... Menu? I
don't see LINKS in the VIEW menu. By the way, my LINKS... Is greyed out
because there aren't any.

Thanks again.

TH

 

[TH]

FIXED...with one more QUESTION.

Just so others can see the solution.

Using Robert's code below (which I believe is also an option on FindLnk) I
discovered 7 Named cells that somehow had duplicate, invalid pointers to a
wrong sheet. All variables existed twice, once pointing to the correct sheet
and again pointing to the incorrect sheet. If you did a GOTO on the name
pointing to the incorrect sheet, it would actually take you to the correct
worksheet. So, deleting these AND deleting two PrintArea names fixed the
error message.

There are still 2 very strange names I did not put there. They refer to the
PrintArea names (in duplicate to the real PrintNames I deleted) with very
long HEX names. Looks a bit like a registry code?? Anyone know what these
are?
(Note: I tried deleted just these names and that did NOT fix the problem.)

'About Rivelle'!Z_8818E7CE_E982_4E9C_992C_F93C4FDBEBE_.wvu.PrintArea
'EMU-PUMA Chart'!Z)8818E7CE_E982_4E9C_992C_F93C4FD4BEBE_.wvu.Rows

TH

 

[Dave Peterson]

Any chance you used View|Custom Views?

They make funny names with WVU in them.

And my two favorite utilities for working with names and links are:

Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager.
http://www.jkp-ads.com/Download.htm

And
Bill Manville's FindLink program to help find those pesky links:
http://www.bmsltd.ie/MVP/Default.htm

Together, it makes things lots, lots, (and one more!) lots, simpler.

 

[TH]

Dave,

1. Thanks. I have both of those utilities and they are excellent.

2. I don't recall using View|Custom, but I may have at one point. In any
case I deleted those 2 names.

3. The problem is back after one day of successful use and no changes other
than saving it. I went through everything again and cannot find any
suspicious links. I used Name Manager and checked all it's various (very
excellent) sort options to no avail. I can only assume it's something in my
code now causing this problem, but that makes no sense either.

4. Still, it's very weird that:

a) it only fails if you quit Excel entirely. Closing the workbook causes no
problems, and,

B) it does not fail using XP.

TH

 

[Dave Peterson]

I don't think I've ever seen that error.

Any chance it's not your workbook that's causing the trouble?

Are there any workbooks that are open at the same time.

I'd check the project explorer (ctrl-R) inside the VBE to see if there's
something else open that may cause the trouble.

Maybe you could close all those other workbooks/addins and see what happens
then.

 

[TH]

Dave,

Checked all that. No other workbooks open. Tried removing my personal macro
workbook. No good. Tried it on another Win2K Excel2K system. Same results.
Ok on XP system. Isn't a problem on different workbooks.

So, MAYBE this is the problem. I noticed that a shape was selected on nearly
every worksheet that had a shape. Additionally, a macro assigned to a shape
was somehow unassigned. I went through each sheet and made sure no shapes or
charts were selected and Quit. The "Bad External Link" error DID NOT APPEAR


I did not write any code to select all these shapes, but will look anyway. I
don't understand why there were selected. If anyone knows how to select the
first shape on all sheets, let me know.

We'll see tomorrow what happens when this workbook runs (it's set to open
via Windows Scheduler so I can get email reports of my trading systems while
out).

Thanks for the ideas.

TH

 

[Dave Peterson]

I still don't have a guess, but you can select the first shape with something
like:

Option Explicit
Sub testme01()
Dim wks As Worksheet
For Each wks In Worksheets
If wks.Shapes.Count > 0 Then
wks.Shapes(1).Select
End If
Next wks
End Sub

(No hidden sheets, right?)