External References

  • Thread starter Thread starter Pinda
  • Start date Start date
P

Pinda

When one has external references in a workbook, a dialogue
box pops up on opening the workbook saying...


"The workbook you opened contains automatic links to
information to another workbook. Do you want to update
this workbook with changes made to the other
workbook.......

Yes or No."

Is it possible to use VBA to always choose yes on opening
the spreadsheet so that the dialogue box doesn't appear?

Thanks in advance.

Pinda
 
When one has external references in a workbook, a dialogue
box pops up on opening the workbook saying... ...
Is it possible to use VBA to always choose yes on opening
the spreadsheet so that the dialogue box doesn't appear?
Click to expand...

Easier than that. *IF* the links are automatic (most external reference links in
formulas are), you can turn off an option that causes this particular dialog to
appear. Tools > Options, Edit tab, uncheck 'Ask to update automatic links',
click OK.

Warning: changing this option bypasses this dialog ALL THE TIME, so if some of
your users have other, large workbooks with lots of external reference links,
they may not like this.

In Excel 97 at least, there doesn't seem to be any alternative to using this
particular option. Excel displays this dialog *before* it runs any AutoOpen or
Workbook_Open macros/event handlers. It's possible to use a macro to open all
your files, but this can get real old real fast. Still, an example.


Sub foo()
Dim fn As Variant, wb As Workbook

fn = Application.GetOpenFilename

If TypeName(fn) = "String" Then _
Set wb = Workbooks.Open(FileName:=fn, UpdateLinks:=1)

End Sub
 
Back
Top