XL03: Avoid "workbook contains links" msg when opening

[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

 

[ryguy7272]

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

....from a Ron de Bruin post...

 

[Ron de Bruin]

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.

 

[Barb Reinhardt]

Ron,

I've used UpdateLinks:=False.

I'm guessing False equates to 0. Is there some reason I should use 0
instead of False?

Barb Reinhardt

 

[ker_01]

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

 

[Ron de Bruin]

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.

 

[ker_01]

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