Getting Rid of Annoying Prompt

[Chaplain Doug]

Excel 2003. I am programmatically creating a single workbook from multiple
workbooks. I selectively do a worksheet.copy from the various source
workbooks into the single destination workbook. When the process is complete
I always get a prompt about "You workbook contains links to other data
sources . . . do you want to update the links?" The first question is why do
I have links when the source workbooks had no links and when I copies sheets
from the source workbooks to the destination workbook? The second question
is, if having these "links" is inevitable, then how do I stop the prompt from
coming up every time? I have already tried:

WbNew.UpdateLinks = xlUpdateLinksAlways

I check this in the destination workbook under Edit-->Links and it sure
enough is set to always update links without prompt,. Nevertheless, I get
the prompt every time anyways. Thanks for the help.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

[Paul Martin]

Doug

Try Application.EnablePrompts = False then when finished switch to
True. This won't fix your underlying issue of links, but should stop
the prompts.

Regards

Paul Martin
Melbourne, Australia

[Tom Ogilvy]

I can't seem to find an enableprompts property or method. Did you mean

Application.DisplayAlerts = False

--
Regards,
Tom Ogilvy


"Paul Martin" <pmartin1960@hotmail.com> wrote in message
news:1116305866.590170.216860@z14g2000cwz.googlegroups.com...
> Doug
>
> Try Application.EnablePrompts = False then when finished switch to
> True. This won't fix your underlying issue of links, but should stop
> the prompts.
>
> Regards
>
> Paul Martin
> Melbourne, Australia
>

[Chaplain Doug]

Application.EnablePrompts = False does not supress these prompts. However, I
did find that the following produced workbooks that did not have the links in
them:

Set WbRD = Workbooks.Open(RDFileName, xlUpdateLinksAlways)


"Paul Martin" wrote:

> Doug
>
> Try Application.EnablePrompts = False then when finished switch to
> True. This won't fix your underlying issue of links, but should stop
> the prompts.
>
> Regards
>
> Paul Martin
> Melbourne, Australia
>
>

[Tom Ogilvy]

If you mean it didn't give you the prompt, then that is because you stumbled
into something.

the second argument of workbooks.open is UpdateLinks

it can take any of 4 values: 0, 1, 2, 3

Value Meaning
0 Doesn't update any references
1 Updates external references but not remote references
2 Updates remote references but not external references
3 Updates both remote and external references

xlUpdateLinksAlways happens to have a value of 3 which tells excel to update
all links. (therefore it doesn't need to ask since you have told it what to
do). That doesn't indicate the workbook doesn't have any links. (if it
does, if you open it manually, it may still prompt)

--
Regards,
Tom Ogilvy


"Chaplain Doug" <ChaplainDoug@discussions.microsoft.com> wrote in message
news:5AEBB9E8-2476-42EA-B2B1-42B24B76C553@microsoft.com...
> Application.EnablePrompts = False does not supress these prompts.
However, I
> did find that the following produced workbooks that did not have the links
in
> them:
>
> Set WbRD = Workbooks.Open(RDFileName, xlUpdateLinksAlways)
>
>
> "Paul Martin" wrote:
>
> > Doug
> >
> > Try Application.EnablePrompts = False then when finished switch to
> > True. This won't fix your underlying issue of links, but should stop
> > the prompts.
> >
> > Regards
> >
> > Paul Martin
> > Melbourne, Australia
> >
> >

[Tom Ogilvy]

Do you have worksheets that have formulas that refer to other sheets in the
same workbooks? If so, when you copy these sheets, they still point back to
the original sheet in the original workbook and create a link.

Perhaps you want

application.AsktoUpdateLinks = False



--
Regards,
Tom Ogilvy

"Chaplain Doug" <ChaplainDoug@discussions.microsoft.com> wrote in message
news:66A2F989-DDDB-4FB9-8A60-91D4CC0E981A@microsoft.com...
> Excel 2003. I am programmatically creating a single workbook from
multiple
> workbooks. I selectively do a worksheet.copy from the various source
> workbooks into the single destination workbook. When the process is
complete
> I always get a prompt about "You workbook contains links to other data
> sources . . . do you want to update the links?" The first question is why
do
> I have links when the source workbooks had no links and when I copies
sheets
> from the source workbooks to the destination workbook? The second
question
> is, if having these "links" is inevitable, then how do I stop the prompt
from
> coming up every time? I have already tried:
>
> WbNew.UpdateLinks = xlUpdateLinksAlways
>
> I check this in the destination workbook under Edit-->Links and it sure
> enough is set to always update links without prompt,. Nevertheless, I get
> the prompt every time anyways. Thanks for the help.
> --
> Dr. Doug Pruiett
> Good News Jail & Prison Ministry
> www.goodnewsjail.org

[Chaplain Doug]

Dear Tom:

I guess what I really want is to eliminate the "links" that are somehow
being created when I copy sheets from one workbook to another. The sheets
being copied DO NOT refer to any other sheets or workbooks. They only use
the data on the sheet. Al formulas on the sheet refer to data on the same
sheet. SO HOW AM I GETTING THESE "LINKS" AND HOW MAY I MAKE THIS NOT HAPPEN
WHEN I COPY THE SHEETS FROM ONE BOOK TO ANOTHER? THANKS.

"Tom Ogilvy" wrote:

> Do you have worksheets that have formulas that refer to other sheets in the
> same workbooks? If so, when you copy these sheets, they still point back to
> the original sheet in the original workbook and create a link.
>
> Perhaps you want
>
> application.AsktoUpdateLinks = False
>
>
>
> --
> Regards,
> Tom Ogilvy
>
> "Chaplain Doug" <ChaplainDoug@discussions.microsoft.com> wrote in message
> news:66A2F989-DDDB-4FB9-8A60-91D4CC0E981A@microsoft.com...
> > Excel 2003. I am programmatically creating a single workbook from
> multiple
> > workbooks. I selectively do a worksheet.copy from the various source
> > workbooks into the single destination workbook. When the process is
> complete
> > I always get a prompt about "You workbook contains links to other data
> > sources . . . do you want to update the links?" The first question is why
> do
> > I have links when the source workbooks had no links and when I copies
> sheets
> > from the source workbooks to the destination workbook? The second
> question
> > is, if having these "links" is inevitable, then how do I stop the prompt
> from
> > coming up every time? I have already tried:
> >
> > WbNew.UpdateLinks = xlUpdateLinksAlways
> >
> > I check this in the destination workbook under Edit-->Links and it sure
> > enough is set to always update links without prompt,. Nevertheless, I get
> > the prompt every time anyways. Thanks for the help.
> > --
> > Dr. Doug Pruiett
> > Good News Jail & Prison Ministry
> > www.goodnewsjail.org
>
>
>

[Steve]

You may have some named cells/ranges lurking. Go to
Insert|Name|Define... and see if anything appears there that refers to
another workbook. Also, there is a great add-in out there,
FindLink.xla, that you can install and use for exactly this situation.
Do a google search on it.


Chaplain Doug wrote:
> Dear Tom:
>
> I guess what I really want is to eliminate the "links" that are
somehow
> being created when I copy sheets from one workbook to another. The
sheets
> being copied DO NOT refer to any other sheets or workbooks. They
only use
> the data on the sheet. Al formulas on the sheet refer to data on the
same
> sheet. SO HOW AM I GETTING THESE "LINKS" AND HOW MAY I MAKE THIS NOT
HAPPEN
> WHEN I COPY THE SHEETS FROM ONE BOOK TO ANOTHER? THANKS.
>
> "Tom Ogilvy" wrote:
>
> > Do you have worksheets that have formulas that refer to other
sheets in the
> > same workbooks? If so, when you copy these sheets, they still
point back to
> > the original sheet in the original workbook and create a link.
> >
> > Perhaps you want
> >
> > application.AsktoUpdateLinks = False
> >
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Chaplain Doug" <ChaplainDoug@discussions.microsoft.com> wrote in
message
> > news:66A2F989-DDDB-4FB9-8A60-91D4CC0E981A@microsoft.com...
> > > Excel 2003. I am programmatically creating a single workbook
from
> > multiple
> > > workbooks. I selectively do a worksheet.copy from the various
source
> > > workbooks into the single destination workbook. When the process
is
> > complete
> > > I always get a prompt about "You workbook contains links to other
data
> > > sources . . . do you want to update the links?" The first
question is why
> > do
> > > I have links when the source workbooks had no links and when I
copies
> > sheets
> > > from the source workbooks to the destination workbook? The
second
> > question
> > > is, if having these "links" is inevitable, then how do I stop the
prompt
> > from
> > > coming up every time? I have already tried:
> > >
> > > WbNew.UpdateLinks = xlUpdateLinksAlways
> > >
> > > I check this in the destination workbook under Edit-->Links and
it sure
> > > enough is set to always update links without prompt,.
Nevertheless, I get
> > > the prompt every time anyways. Thanks for the help.
> > > --
> > > Dr. Doug Pruiett
> > > Good News Jail & Prison Ministry
> > > www.goodnewsjail.org
> >
> >
> >