Do not prompt user on broken link

**Whizbang** · 08-07-2009, 08:16 AM

I have an excel file that is intended to generate a graph for management's viewing pleasure.

This file pulls totals from external links. The way I have it set up is that every month our log is saved to a naming scheme involving the date, then a fresh log is created for the current month. So, for each month I have "log mm-yyyy".

Now, the chart file is a thirteen month rolling chart (meaning every month it must show the thirteen most recent month's totals). I have created a macro to automatically update the links, based on an input in cell A:1 (meaning you can enter a date in the past and view the thirteen months previous). The problem is that we have only been doing the log for 10 months. So, when the macro runs to update the links, and it gets to months 11 through 13, it pops up a window for the user to manually browse for the missing(non-existent) files. There are quite a few cells that contain links to various information on these logs. I get a prompt for each cell and have to click "cancel". Once I have clicked cancel through all of the broken links, then everything works great.

My question (after all that) is, is there a way to suppress the prompt for the user to search for the missing file? Like I said, this chart is for management, and they shouldn't have to click cancel a dozen times just o look at a chart. If the file doesn't exist, then just break the link.

Obviously once the next three months are over, this problem will disappear, but in the mean time I need a solution.

**MartinjLane** · 08-07-2009, 08:48 AM

Hi Sounds simple but have you tried creating 3 dummy files with zero values?

**Whizbang** · 08-07-2009, 08:51 AM

Wow... The thought never even occurred to me.

That's what I'll do.

But it'd still be a nice bit-o-knowledge to have for any future use. If anyone does know of a way to suppress the prompt, I'd love to know how. In the mean time, I'll use MartinjLane's method.

**Cheeky Charlie** · 08-07-2009, 09:34 AM

The solutions isn't to "pre-click" the prompt, but instead to "preempt" the the prompt. i.e. the macro checks if a file exists before it writes links to it.

In other situations the option "update remote refences" in Tools-> options -> calculation would be appropriate but you don't want to turn this off if other formulae rely on remote references (obviously they do).

CC

**Whizbang** · 08-07-2009, 10:14 AM

This is my macro:

Please Login or Register  to view this content.

It takes the existing/pre-filled link, and replaces the previous date in the filename with the new date.

How could I test to see if the new file exists before the replace?

**Whizbang** · 10-13-2009, 01:16 PM

Solution:

Please Login or Register  to view this content.

Then just turn the alerts back to "true" when done with code.

I can't believe how simple this was, or how long it took me to find it.

**Cheeky Charlie** · 10-13-2009, 01:28 PM

lol - at least you'll never forget this one

Sorry, it looks like this thread slipped through the "subscribed threads net" - I didn't mean to abandon you!

CC