how to skip Update Values dialog when links change tonon-existent spreadsheets?
 

I have a VBA subroutine within an excel spreadsheet which goes through
several cells containing links to external spreadsheets and changes the
formulas containing those links to point to a different spreadsheet based on
the date.

It works, kinda, except for the annoying popups which occur from trying to
link to non-existing spreadsheets. The title shows "Update Values:" with a
spreadsheet link.

I was wondering if there's a way to prevent these dialogs from showing up
when a link was changed in a formula to now point to a non-existent
spreadsheet.

The formulas contain something like "if(iserror(vlookup( referencing an
external spreadsheet)), "ND", vlookup(..))".

Come to think of it, is there a way to check if the spreadsheet exists
before even attempting a link to it in a formula and yet still keep the
formula?

--
Ken Shaffer

how to skip Update Values dialog when links change tonon-existent spreadsheets?
 

Ken,

Try

Application.DisplayAlerts = False

at the start of your code, and reset to True after.

For the fileexits, look at the Dur function in Help.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ken Shaffer" wrote in message
...
I have a VBA subroutine within an excel spreadsheet which goes through
several cells containing links to external spreadsheets and changes the
formulas containing those links to point to a different spreadsheet based
on
the date.

It works, kinda, except for the annoying popups which occur from trying to
link to non-existing spreadsheets. The title shows "Update Values:" with a
spreadsheet link.

I was wondering if there's a way to prevent these dialogs from showing up
when a link was changed in a formula to now point to a non-existent
spreadsheet.

The formulas contain something like "if(iserror(vlookup( referencing an
external spreadsheet)), "ND", vlookup(..))".

Come to think of it, is there a way to check if the spreadsheet exists
before even attempting a link to it in a formula and yet still keep the
formula?

--
Ken Shaffer

how to skip Update Values dialog when links change tonon-existent spreadsheets?
 

That worked! I knew about that, but didn't think it applied in this situation

Thanks

-
Ken Shaffe

----- Bob Phillips wrote: ----

Ken

Tr

Application.DisplayAlerts = Fals

at the start of your code, and reset to True after

For the fileexits, look at the Dur function in Help

--

HT

Bob Phillip
... looking out across Poole Harbour to the Purbeck
(remove nothere from the email address if mailing direct

"Ken Shaffer" wrote in messag
..
I have a VBA subroutine within an excel spreadsheet which goes throug
several cells containing links to external spreadsheets and changes th
formulas containing those links to point to a different spreadsheet base
o
the date
It works, kinda, except for the annoying popups which occur from trying t
link to non-existing spreadsheets. The title shows "Update Values:" with
spreadsheet link

how to skip Update Values dialog when links change tonon-existent spreadsheets?
 

Probably obvious, but in case not:
Dur should be Dir

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
Ken,

Try

Application.DisplayAlerts = False

at the start of your code, and reset to True after.

For the fileexits, look at the Dur function in Help.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ken Shaffer" wrote in message
...
I have a VBA subroutine within an excel spreadsheet which goes through
several cells containing links to external spreadsheets and changes the
formulas containing those links to point to a different spreadsheet
based
on
the date.

It works, kinda, except for the annoying popups which occur from trying
to
link to non-existing spreadsheets. The title shows "Update Values:" with
a
spreadsheet link.

I was wondering if there's a way to prevent these dialogs from showing
up
when a link was changed in a formula to now point to a non-existent
spreadsheet.

The formulas contain something like "if(iserror(vlookup( referencing an
external spreadsheet)), "ND", vlookup(..))".

Come to think of it, is there a way to check if the spreadsheet exists
before even attempting a link to it in a formula and yet still keep the
formula?

--
Ken Shaffer