Using Edit->Links->startup prompt

[Hall]

My workbook has a vlookup to an external file. I want to skip the user
prompt when opening the file so that the update happens automatically.

I expected this to be controlled by the setting in Edit->Links->Startup
Prompt. But this setting is being ignored.

The only setting that is working is in Tools->Options->Edit->Ask to update
automatic links. But this setting would affect all files, which I don't
want.

So how do I utilize the feature Edit->Links->Startup Prompt??

 

[Dave Peterson]

This is one of those settings that the end user has more control over than the
developer.

If they want to be prompted (tools|options|Edit tab|ask to update automatic
links), then what you do in Edit|links|startup will be ignored.

And not only does "ask to update automatic links" get set for all the workbooks,
it's a user setting. So you as a developer can't force the user to change it
(by changing a setting in a workbook that you share with others).

One common workaround is to give the user two workbooks.

This first dummy workbook opens the second workbook the way you want--then
closes to get out of the way.

The dummy workbook would contain a macro like this:

Kind of like:

Option Explicit
Sub auto_open()
Workbooks.Open Filename:="c:\my documents\excel\book2.xls", UpdateLinks:=1
ThisWorkbook.Close savechanges:=False
End Sub

Then you open the dummy workbook and the links will be refreshed.
(read about that UpdateLinks argument in VBA's help.)