Getting Rid of Annoying Prompt

[Guest]

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.

 

[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

 

[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

 

[Guest]

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)

 

[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)

 

[Guest]

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.

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

 

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

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.