Links to other files in error!

steve case

steve case

Well-known Member
Joined
Apr 10, 2002
Messages
823
[I posted this in the Lounge gripe thread]
[Which is this thread right here~admin]

I take most everyone has seen this dialog box:

This workbook contains links to other data sources.

*If you update the links, Excel will attempt to retrieve the latest data.
*If you don't update the links, Excel will use the previous information.

Note that data links can be used to access and share confidential information without permission and possibly perform other harmful actions. Do not update the links if you do not trust the source of this workbook.
Click to expand...

I've stopped trying to rectify the situation and just click on cancel. So does everyone else. Big mistake. If there is a file that changes from time to time, and you're relying on a link to it to make your data correct, and some one moves or removes it! you get the above message. But you have no idea it's your very important file that has been deleted or moved. And Excel as the message says, uses the last data it had access to.

Is there a way to force Excel to display the #N/A error for the broken link instead of telling the lie? Well really, if the file’s been deleted or moved, Excel has no idea what the true value of the "Link" is and is lying when it puts up that last known value and really doesn't tell you except for that opening message.

Very frustrating, the Bill Of Material was moved and then changed, and my Excel file reported the old value just like that.

I've got a huge file with lots of pages and all sorts of people do lots of things including making links to files that after a while get moved or deleted. I can't chase them all down.

So, is there a way to force Excel to "Crash" and put up an error message in the cell where the link is supposed to be?
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Excuse me, but what do you mean by "rectify the situation"? Excel gives you the opportunity to choose whether to update links or not.

You seem to be saying that giving you a choice is a problem, and that "just click on cancel" is the easiest thing to do, and that Excel is at fault for allowing you to do that.

I am misunderstanding something here???????????????
 
Upvote 0
Or is that when a supporting link is no longer valid that you want an error to appear in cells, after pressing "Cancel" to the "File Not Found" dialog?
 
Upvote 0
As a matter of course the users click on cancel or the [x] or in some way make the annoying dialog box "Go Away"

Why Excel chooses to remember the deleted or moved files and treat that nonexistent data as truth is causing me fits.

The data file that had been there is gone and there's not much to tell the user that anything is amiss.

I'd like a command in VBA that tells Excel that if the file isn't there, put up a #N/A

Maybe I haven't explained this very well. But I've finally come the realization that Excel has been pulling data from a file that's been deleted for months and I didn't know it.

So, does anyone know of a VBA solution that will make Excel crash and display the #N/A no matter what the user selects?.
.
.
.
.
 
Upvote 0
Is there a way to force Excel to display the #N/A error for the broken link instead of telling the lie?
Click to expand...

I'm not so sure "lie" is really the right word. Excel displays the 'last known' value when it could successfully link to the now-delete or -moved file. I can easily see the logic of why it 'doesn't' put N/A. I think most users would 'want' the last known value, rather than having critical data no longer be there (i.e. display "N/A") be missing (i.e. their spreadsheet would be rendered useless).

If the message bothers you (and I wholeheartedly admit, it's irritating), why don't you just break the link (which will convert the non-linkable cell(s) into values). If you do, the irritating message, will, of course, go away. This of course presumes you do not know or are unable to locate where the file has been moved. If you can locate the file, of course, just edit the links and tell Excel it's new file location.
 
Upvote 0
If the message bothers you (and I wholeheartedly admit, it's irritating)
Click to expand...
I don't care so much that the message shows up, I care that the results after the message is dispatched by the users that Excel is wrong!

, why don't you just break the link (which will convert the non-linkable cell(s) into values).
Click to expand...
Huh? I didn't follow that at all.

If you do, the irritating message, will, of course, go away.
Click to expand...
Again, the message isn't the point. The errors from reporting nonexistent data is.

This of course presumes you do not know or are unable to locate where the file has been moved.
Click to expand...
I'll deal with the missing file, but first I need to know it's missing. When people send the message away and act upon the erroneous data, my phone doesn't ring. But my phone will ring if the users get a string of #N/A error messages. That's why I'm seeking some code that will flag the linked cells with #N/A when the link isn't there.

If you can locate the file, of course, just edit the links
Click to expand...
Again I'll deal with the file when I know there's a problem

and tell Excel its new file location.
Click to expand...
I'll tell Excel what to do when I know I need to.

Maybe I'm not explaining this very well, but I've got files "out there" that depend on links to data. I need to know when those links don't work. Using yesterday's data for today’s problems is a recipe for disaster. I'm talking about a communication problem. I need to know when Excel encounters a broken link. I need my users to know when "It aint' working" so they call me up to fix it.

So, is there a command in VBA that will put an error message in cells with broken links?
.
.
.
.
 
Upvote 0
Maybe we're thinking about this in two different ways.

I need to know when those links don't work.
Click to expand...

You know the when, when you click UPDATE, and Excel tells you "This workbook contains one or more links that cannot be updated.

Now, I would imagine you want to know the where.

After clicking update, the error message box appears and you are given two choices; Continue or Edit Links.

Choose Edit Links.

A dialog box (1) will appear, which contains a STATUS column. Look for the file(s) that say Error: Source not found. Now you know the name of the filename(s).

Now search (edit>find): [*filename*, followed by clicking the Find All button.

You'll receive a list of exactly which cells were broken.
(You may have to repeat this step if two or more files are involved)

Probably not the elegant solution you were looking for.

---------------------

, why don't you just break the link (which will convert the non-linkable cell(s) into values).
Click to expand...
Huh? I didn't follow that at all.
Click to expand...
(1): This is where the Break Link button appears.
 
Upvote 0
First of all, thanks for your replies, I do appreciate it that some one has taken a look at this issue.
Maybe we're thinking about this in two different ways.
Click to expand...
Yes we are.
Quote:
I need to know when those links don't work.
You know the when, when you click UPDATE, and Excel tells you "This workbook contains one or more links that cannot be updated.
Click to expand...
And this is precisely where we're thinking about this in two different ways.

I'm the creator of the workbook, not the user. Users get rid of that opening dialog box and never give it a second thought. They certainly don't ring me up to tell me my spreadsheet isn't working again. But if it were to "crash" with #N/A where they wanted answers they would.
 
Upvote 0
You could have a VBA macro that tries to update the links on opening, and tests to see if the update worked, and if not then give a message telling the user to contact support and report the error, and then have the macro close the workbook so that it can't be viewed until all links are resolved.
 
Upvote 0
Similar Problem

I am having a problem where I receive the "workbook contains one or more links that cannot be updated" in a spreadsheet that I use every day with multiple links to other spreadsheets. Next to the files it says "ERROR SOURCE NOT FOUND". It doesn't happen each time I open the file, it seems to be once a week or so... I lose the link to the other spreadsheets and have to go thru each one and UPDATE the file. My files are located on our server, so I'm thinking it might be some issue with a "mapped network drive"?



Thanks for your help
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Changing row number dynamically in link to another file
Replies
1
Views
241
AlphaFrog
AlphaFrog
M
  • Question Question
Dynamic link from Word to Excel...
Replies
8
Views
325
Matteo85
M
W
  • Solved
Formula with Link not working in VBA
Replies
2
Views
283
woodsy74
W
willow1985
  • Solved
Linking 2 identical tables in the same workbook so when one updates the other updates
2
Replies
18
Views
515
mumps
mumps
S
  • Question Question
Linked file doesn't update
Replies
9
Views
289
Alex Blakenburg
A

Forum statistics

Threads
1,225,346
Messages
6,184,404
Members
453,230
Latest member
ProdInventory

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top