How to avoid "update links" startup prompt?

[joeu2004]

How can I avoid the "update links" startup prompt __and__ avoid
attempting to update (from?) links?

I am receive a workbook created on another system. The other system
has all the other workbooks referred to by links. But my system has
only one workbook, with all values updated before the workbook was
saved and sent to me.

According to the help text, there should be an Edit > Links > Startup
Prompt option menu. That should permit to avoid the prompt __ and__
allow me to control whether or not to do the update. But the Links is
grayed out. In other words, I cannot click on the Edit > Link item.

Is that an option that the owner of the workbook with links must set
before sending the workbook to me?

Deselecting the "ask to update links" under Tools > Options > Edit is
not what I want because according to help text, if the prompt is
deselected there, Excel will automatically try to update links,
presumably when a spreadsheet is changed..

 

[Dave Peterson]

I'd use another workbook with a macro that opened your real workbook the way you
want.

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

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[joeu2004]

I'd use another workbook with a macro that opened your real workbook the way you
want.
Option Explicit
Sub auto_open()
Workbooks.Open Filename:="c:\my documents\excel\book2.xls", UpdateLinks:=0
ThisWorkbook.Close savechanges:=False
End Sub

Thanks. That would work for me. But I must set this up for fairly
novice users who are accessing the workbook via an attachment to email.
I would have to change their procedures so radically that I know they
could not handle it :-(.

(They would have to save the workbook to a predetermined file name --
overwriting any previous instance -- then open a workbook that I would
provide, saved locally, that executes the magic macro when my workbook
is opened.)

Am I to infer from your "unusual" solution that there is no easy
remedy?

All I want is to know how to use the Edit > Links > Startup Prompt
option.

Why is it greyed out when I first enter Excel?

Is this an option that must be set before the workbook is saved, and
then only if there are indeed links to other workbooks in the current
workbook?

Or is this an option that can be applied to all workbooks when I open
them, like macro security?

The latter is what I hoping for. But the former (set by the creator of
the workbook) would be acceptable.

 

[Dave Peterson]

I thought that "unusual" solution was pretty easy.

I don't know why your "startup prompt" option is greyed out.

But 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 that user option is a user by user setting...You can't do anything in your
workbook to modify that setting for your workbook.

 

[joeu2004]

Thanks so much for your responses, Dave. I know you are trying to be
helpful, and I appreciate the effort. But I have the feeling we are
not communicating very well, and the fault is probably mine.

So please allow me to start all over with hopefully a better
explanation. I will refer to the actions of Person A and Person B, and
I will refer to Workbooks 1, 2, etc. It would be helpful if
respondents use the same nomenature and dispense with the use of
pronouns as much as reasonable.

By the way, I am neither Person A nor B; I am merely a "referee".
Also, I do not have intimidate knowledge of the form and content of
Worksbook 1, 2 etc. So my ability to provide details is very limited.
Sorry. Please try to read between the lines.

The situation is this....

Person A maintains a set of Workbooks 1, 2 etc; that is, Person A
modifies the set of workbooks. And yes, these are individual
workbooks, each with many worksheets, not merely one workbook with many
(more) worksheets. Apparently, Workbook 1 has links to Workbooks 2, 3
etc. Periodically, Person A sends (just) Workbook 1 to Person B.
Workbook 1 is an attachment to the email; Person B uses a different
computer than Person A. Person B merely opens the email and saves and
prints Workbook 1; that is, Person B merely reads the data in Workbook
1. In fact, it is important that Person B not modify the data in
Workbook 1.

Obviously, Person A needs the "update link" feature in order to
maintain the set of workbooks properly. But as the feature is used
currently, this causes Person B to see the following prompt when
Workbook 1 is opened: "This workbook contains links to other data
sources. ... Update [button] ... Don't Update [button]".

Of course, Person B selects "don't update", since Person B does not
even have the linked data (other workbooks) at his disposal. But
Person B is annoyed by needing to do this. Person B wonders if the
prompt can be avoided when he opens Workbook 1.

Questions....

Is there a way to avoid the "update links?" prompt that Person B sees
in such a way that Excel will not attempt to the "update the links"
(i.e. retrieve the latest data)?

Ideally, is there something that Person B can do to avoid the prompt,
without impacting what Person A does?

Alternatively, is there something that Person A can do, persumably when
Workbook 1 is saved, to ensure that Person B will not see the prompt,
without impacting what Person B does?

Alternatively, are there things that Persons A and B must do separately
so that Person B will not see the prompt and Excel will not attempt to
"update the links"?

IMHO, it would be acceptable if Person A must save a copy of Workbook 1
-- call it Workbook 1 Copy -- in order to select the appropriate
option(s) so that Person B will not see an "update links?" prompt and
Excel will not attempt to "update the links".

But I want to reiterate that both Persons A and B are fairly
inexperienced Excel users, and both are set in their own ways. So any
solution must be "as simple as possible, but no simpler", as Einstein
once said, requiring the smallest possible changes to each person's
procedures.

Thanks again for any solution or pointers.

 

[Dave Peterson]

I think that person B will have to answer the prompt.

Thanks so much for your responses, Dave. I know you are trying to be
helpful, and I appreciate the effort. But I have the feeling we are
not communicating very well, and the fault is probably mine.

So please allow me to start all over with hopefully a better
explanation. I will refer to the actions of Person A and Person B, and
I will refer to Workbooks 1, 2, etc. It would be helpful if
respondents use the same nomenature and dispense with the use of
pronouns as much as reasonable.

By the way, I am neither Person A nor B; I am merely a "referee".
Also, I do not have intimidate knowledge of the form and content of
Worksbook 1, 2 etc. So my ability to provide details is very limited.
Sorry. Please try to read between the lines.

The situation is this....

Person A maintains a set of Workbooks 1, 2 etc; that is, Person A
modifies the set of workbooks. And yes, these are individual
workbooks, each with many worksheets, not merely one workbook with many
(more) worksheets. Apparently, Workbook 1 has links to Workbooks 2, 3
etc. Periodically, Person A sends (just) Workbook 1 to Person B.
Workbook 1 is an attachment to the email; Person B uses a different
computer than Person A. Person B merely opens the email and saves and
prints Workbook 1; that is, Person B merely reads the data in Workbook
1. In fact, it is important that Person B not modify the data in
Workbook 1.

Obviously, Person A needs the "update link" feature in order to
maintain the set of workbooks properly. But as the feature is used
currently, this causes Person B to see the following prompt when
Workbook 1 is opened: "This workbook contains links to other data
sources. ... Update [button] ... Don't Update [button]".

Of course, Person B selects "don't update", since Person B does not
even have the linked data (other workbooks) at his disposal. But
Person B is annoyed by needing to do this. Person B wonders if the
prompt can be avoided when he opens Workbook 1.

Questions....

Is there a way to avoid the "update links?" prompt that Person B sees
in such a way that Excel will not attempt to the "update the links"
(i.e. retrieve the latest data)?

Ideally, is there something that Person B can do to avoid the prompt,
without impacting what Person A does?

Alternatively, is there something that Person A can do, persumably when
Workbook 1 is saved, to ensure that Person B will not see the prompt,
without impacting what Person B does?

Alternatively, are there things that Persons A and B must do separately
so that Person B will not see the prompt and Excel will not attempt to
"update the links"?

IMHO, it would be acceptable if Person A must save a copy of Workbook 1
-- call it Workbook 1 Copy -- in order to select the appropriate
option(s) so that Person B will not see an "update links?" prompt and
Excel will not attempt to "update the links".

But I want to reiterate that both Persons A and B are fairly
inexperienced Excel users, and both are set in their own ways. So any
solution must be "as simple as possible, but no simpler", as Einstein
once said, requiring the smallest possible changes to each person's
procedures.

Thanks again for any solution or pointers.

 

[joeu2004]

I think that person B will have to answer the prompt.

Why do you think that?

I finally overcame my relunctance to experiment with the link feature
myself, and in fact, I discovered that it works exactly the way I
expected. My conclusion: There is no reason why Person A cannot email
a workbook that does not prompt for "update links?" and does not
attempt to update linked data.

It really is very simple. The creator of the workbook, Person A,
probably wants the following option set: Edit > Links > Startup Prompt

Let Users Choose to Display the Alert or Not. Then whether or not Person A sees a prompt depends on the setting of the option Tools > Options > Edit > Ask to Update Automatic Links.

When Person A prepares Workbook 1 to be emailed to Person B, Person A
can set the option Edit > Links > Startup Prompt > Don't Display the
Alert and Don't Update Automatic Links, the Save As another file name.
It is the saved file that Person A emails to Person B. Note that this
does not alter the options in Workbook 1. This ensures that the
Workbook 1 update options remain consistent with Person A's wishes, yet
the emailed workbook meets with Person B's needs (viz. no prompt and no
update).

Of course, it might be ideal if someone (moi!) provided an add-in macro
for Person A to use to facilitate this procedure. But Person A is a
more-sophisticated Excel user than Person B. I can expect him to
follow the multistep procedure.

Dave Peterson wrote earlier:

I don't know why your "startup prompt" option is greyed out.

Presumably because the spreadsheet (or workbook) that I tried this in
initially did not have any linked data. (Klunk!) Apparently, Excel is
clever enough to make this Edit menu item available (not grayed out)
only when there are linked data.

(FYI, I wrote "the Links is grayed out[; i]n other words, I cannot
click on the Edit > Link item". I did not say that the Startup Prompt
option was grayed out.)

 

[Dave Peterson]

This sounds like it was the startup prompt to me:

All I want is to know how to use the Edit > Links > Startup Prompt
option.

Why is it greyed out when I first enter Excel?

I think that person B will have to answer the prompt.

Why do you think that?

I finally overcame my relunctance to experiment with the link feature
myself, and in fact, I discovered that it works exactly the way I
expected. My conclusion: There is no reason why Person A cannot email
a workbook that does not prompt for "update links?" and does not
attempt to update linked data.

It really is very simple. The creator of the workbook, Person A,
probably wants the following option set: Edit > Links > Startup Prompt

Let Users Choose to Display the Alert or Not. Then whether or not Person A sees a prompt depends on the setting of the option Tools > Options > Edit > Ask to Update Automatic Links.

When Person A prepares Workbook 1 to be emailed to Person B, Person A
can set the option Edit > Links > Startup Prompt > Don't Display the
Alert and Don't Update Automatic Links, the Save As another file name.
It is the saved file that Person A emails to Person B. Note that this
does not alter the options in Workbook 1. This ensures that the
Workbook 1 update options remain consistent with Person A's wishes, yet
the emailed workbook meets with Person B's needs (viz. no prompt and no
update).

Of course, it might be ideal if someone (moi!) provided an add-in macro
for Person A to use to facilitate this procedure. But Person A is a
more-sophisticated Excel user than Person B. I can expect him to
follow the multistep procedure.

Dave Peterson wrote earlier:

I don't know why your "startup prompt" option is greyed out.

Presumably because the spreadsheet (or workbook) that I tried this in
initially did not have any linked data. (Klunk!) Apparently, Excel is
clever enough to make this Edit menu item available (not grayed out)
only when there are linked data.

(FYI, I wrote "the Links is grayed out[; i]n other words, I cannot
click on the Edit > Link item". I did not say that the Startup Prompt
option was grayed out.)

 

[Dave Peterson]

and you are right about not updating links.

I think that person B will have to answer the prompt.

Why do you think that?

I finally overcame my relunctance to experiment with the link feature
myself, and in fact, I discovered that it works exactly the way I
expected. My conclusion: There is no reason why Person A cannot email
a workbook that does not prompt for "update links?" and does not
attempt to update linked data.

It really is very simple. The creator of the workbook, Person A,
probably wants the following option set: Edit > Links > Startup Prompt

Let Users Choose to Display the Alert or Not. Then whether or not Person A sees a prompt depends on the setting of the option Tools > Options > Edit > Ask to Update Automatic Links.

When Person A prepares Workbook 1 to be emailed to Person B, Person A
can set the option Edit > Links > Startup Prompt > Don't Display the
Alert and Don't Update Automatic Links, the Save As another file name.
It is the saved file that Person A emails to Person B. Note that this
does not alter the options in Workbook 1. This ensures that the
Workbook 1 update options remain consistent with Person A's wishes, yet
the emailed workbook meets with Person B's needs (viz. no prompt and no
update).

Of course, it might be ideal if someone (moi!) provided an add-in macro
for Person A to use to facilitate this procedure. But Person A is a
more-sophisticated Excel user than Person B. I can expect him to
follow the multistep procedure.

Dave Peterson wrote earlier:

I don't know why your "startup prompt" option is greyed out.

Presumably because the spreadsheet (or workbook) that I tried this in
initially did not have any linked data. (Klunk!) Apparently, Excel is
clever enough to make this Edit menu item available (not grayed out)
only when there are linked data.

(FYI, I wrote "the Links is grayed out[; i]n other words, I cannot
click on the Edit > Link item". I did not say that the Startup Prompt
option was grayed out.)