XL03: Avoid "workbook contains links" msg when opening

  • Thread starter Thread starter ker_01
  • Start date Start date
K

ker_01

'Open workbook
Application.DisplayAlerts = False
Set twb = Workbooks.Open(FileName:=MyFullFilePath, ReadOnly:=True)
Application.DisplayAlerts = True

When my target workbook contains links, I still get a warning pop-up asking
if links should be updated, even with displayalerts = False.

So I changed the open statement to:
Application.DisplayAlerts = False
Set twb = Workbooks.Open(FileName:=MyFullFilePath, UpdateLinks:=False,
ReadOnly:=True)
Application.DisplayAlerts = True

It bypasses that initial warning, but then pops up the filesearch
wizard/userform with the title bar "Update Values: HsTbar.xla" which I'm
guessing is an add-in used by the person providing me the data sheet. This is
not what I'd expect, since I set UpdateLinks = False

I'm just trying to copy the data out of one sheet (format and values, not
formulas or links) but I need to do so in a way that is fully automated (no
user prompts) so that my code can run overnight.

How can I completely bypass the links alerts and just open the file via VBA?

Thank you,
Keith
 
If you have links in your workbook this (UpdateLinks:=0) will avoid the message

....from a Ron de Bruin post...
 
Correct, it is wrong in the help of a lot of Exel versions, i believe it is correct
in the good old Excel 2000

If you have links in your workbook this (UpdateLinks:=0) will avoid the message
do you want to update the links or not "0 Doesn't update any references"
Use 3 instead of 0 if you want to update the links.
 
And if they are equivalent, is there another reason why the second sample
code in my initial post would not eliminate the secondary Excel message?

If Excel understood that I didn't want to update links, I would expect it to
just open the worksheet without any additional prompts, but it is acting like
I said that I did want to update links.

Thanks to all,
Keith
 
Hi Barb

I always use the numbers (never test it with False)

Excel 2003 and 2007 Help
1 User specifies how links will be updated
2 Never update links for this workbook on opening
3 Always update links for this workbook on opening

If you use 2 or 3 both will update the links so the help is not correct

Excel 2000 Help
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

I think 3 and 0 are the best options if you want to update or not and is
working in all Excel versions as far as I know.
 
Thank you all for your continued assistance.

I replaced the False with 0, and still have the same symptom, which makes me
think that this is not actually a links issue that I'm seeing.

The formulas in the source workbook seem to refer to an XLA which I don't
have (and am unlikely to get), based on the pop-up:
An example of the formula is
='C:\Hyperion\SmartView\Bin\HsTbar.xla'!HsGetValue("CorpHFM","Scenario#"&$C$1&";Year#"&E$12&";Period#"&E$13&";View#"&$C$2&";Entity#"&$A15&";Value#"&$C$4&";Account#"&$B15&";ICP#"&$C$3&";Custom1#"&$C$5&";Custom2#"&$C$6&";Custom3#"&$C$7&";Custom4#"&$C$8&"")*2204.6

I think what confused me was that when I open the file manually (without
VBA) and decline to update links, I do not get this secondary pop-up asking
for the file location of HsTbar.xla.

So, my best deduction is that there is different behavior between
UpdateLinks:=0 vs what happens when the file is manually opened and the user
declines to update links.

So I guess my question should be rephrased; what additional parameters or
settings do I need to use to open a file with VBA without triggering any
sheet updates at all, so that it won't ask for the xla location?

Many thanks,
Keith
 
Back
Top