Getting Rid of Annoying Prompt
 

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

Getting Rid of Annoying Prompt
 

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

Getting Rid of Annoying Prompt
 

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

Application.DisplayAlerts = False

--
Regards,
Tom Ogilvy


"Paul Martin" wrote in message
oups.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

Getting Rid of Annoying Prompt
 

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" wrote in message
...
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

Getting Rid of Annoying Prompt
 

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

Getting Rid of Annoying Prompt
 

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" wrote in message
...
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

Getting Rid of Annoying Prompt
 

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" wrote in message
...
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

Getting Rid of Annoying Prompt
 

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" wrote in
message
...
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