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

Ken Shaffer wrote:
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.

Application.DisplayAlerts = False
ActiveCell.Formula = ....
Application.DisplayAlerts = True

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

in article , Bill Manville at
wrote on 6/2/04 4:43 AM:

Application.DisplayAlerts = False
ActiveCell.Formula = ....
Application.DisplayAlerts = True


Works great but takes very long. Appears as though links are actually being
updated. Any way to change links in formulas without an update occurring?

Ken Shaffer

Ken Shaffer wrote:
Works great but takes very long. Appears as though links are actually being
updated. Any way to change links in formulas without an update occurring?

No.
Ideas for improving speed:
- manual calculation
- if there are a number of cells with the same formula (apart from relative
adjustments) set them all at once e.g.
Selection.FormulaR1C1 = ...
- if there are lots of different formulas you could try
ActiveCell.Formula = "!$" & "the formula you would have used without the ="
followed by a global replacement of !$ with =

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup