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..

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



wrote:

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

Dave Peterson wrote:
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.

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.

wrote:

Dave Peterson wrote:
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

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.

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

wrote:

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